Basic question
in [Programming]
|
From: Jim Thomlinson on 28 Dec 2005 14:46 This should be close to what you want... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Address = "$C$12" Then If Target.Value = "N" Then Worksheet("Sheet3").Cells(3, 2) = "Sold Out" ElseIf Target.Value = "Y" Then Worksheet("Sheet3").Visible = False End If End If ErrorHandler: Application.EnableEvents = True End Sub This code needs to be included in the sheet and not in a standard module. -- HTH... Jim Thomlinson "hshayh0rn" wrote: > From the last time I posted I found the on change event and thought that > might be the answer but the code Bob supplied does not work when I add it to > the on change event. > > "Jim Thomlinson" wrote: > > > That is an on demand macro that the user would have to run on their own. You > > could use the On Change event from Sheet 2 if you needed it to be automatic. > > -- > > HTH... > > > > Jim Thomlinson > > > > > > "hshayh0rn" wrote: > > > > > Thanks for the reply... So, would the user have to run the macro manually or > > > does excel know to run this on its own? > > > > > > "Bob Phillips" wrote: > > > > > > > Sub myMacro() > > > > If Worksheet("Sheet2").cells(12,3) = "N" Then > > > > Worksheet("Sheet3").cells(3,2) = "Sold Out" > > > > ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then > > > > Worksheet("Sheet3").Visible = False > > > > End If > > > > End Sub > > > > > > > > This would be an on-demand macro, stored in a standard code module. > > > > > > > > -- > > > > > > > > HTH > > > > > > > > RP > > > > (remove nothere from the email address if mailing direct) > > > > > > > > > > > > "hshayh0rn" <hshayh0rn(a)discussions.microsoft.com> wrote in message > > > > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228(a)microsoft.com... > > > > > I have a very basic question but I can't seem to find the answer on here > > > > > (probably to basic). I have a spreadsheet that I would like to perform all > > > > of > > > > > my calculations / If / Then statements using VBA but I'm not sure how to > > > > > actually get the code to run or where to put it to run... I can get all of > > > > > the code to run with an on click event or something like that but > > > > basically > > > > > I'd like cells to automatically fill in other cells based on values the > > > > users > > > > > put in them. For example: > > > > > > > > > > If sheet2!.cells(12,3) = "N" Then > > > > > > > > > > Sheet3!.cells(3,2) = "Sold Out" > > > > > > > > > > or > > > > > > > > > > If sheet2!.cells(12,3) = "Y" Then > > > > > > > > > > Sheet3!.Visible = False > > > > > > > > > > I'll keep searching for the answers to this question but if someone could > > > > > shoot me a quick answer I would appreciate it. > > > > > > > > > > > >
From: Tom Ogilvy on 28 Dec 2005 14:56 did you run a macro like Sub StartOnChange() Worksheets("Sheet2").OnChange = "MyMacro" End Sub -- Regards, Tom Ogilvy "hshayh0rn" <hshayh0rn(a)discussions.microsoft.com> wrote in message news:7811B8AC-0ADD-4F01-B061-E98747268C39(a)microsoft.com... > From the last time I posted I found the on change event and thought that > might be the answer but the code Bob supplied does not work when I add it to > the on change event. > > "Jim Thomlinson" wrote: > > > That is an on demand macro that the user would have to run on their own. You > > could use the On Change event from Sheet 2 if you needed it to be automatic. > > -- > > HTH... > > > > Jim Thomlinson > > > > > > "hshayh0rn" wrote: > > > > > Thanks for the reply... So, would the user have to run the macro manually or > > > does excel know to run this on its own? > > > > > > "Bob Phillips" wrote: > > > > > > > Sub myMacro() > > > > If Worksheet("Sheet2").cells(12,3) = "N" Then > > > > Worksheet("Sheet3").cells(3,2) = "Sold Out" > > > > ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then > > > > Worksheet("Sheet3").Visible = False > > > > End If > > > > End Sub > > > > > > > > This would be an on-demand macro, stored in a standard code module. > > > > > > > > -- > > > > > > > > HTH > > > > > > > > RP > > > > (remove nothere from the email address if mailing direct) > > > > > > > > > > > > "hshayh0rn" <hshayh0rn(a)discussions.microsoft.com> wrote in message > > > > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228(a)microsoft.com... > > > > > I have a very basic question but I can't seem to find the answer on here > > > > > (probably to basic). I have a spreadsheet that I would like to perform all > > > > of > > > > > my calculations / If / Then statements using VBA but I'm not sure how to > > > > > actually get the code to run or where to put it to run... I can get all of > > > > > the code to run with an on click event or something like that but > > > > basically > > > > > I'd like cells to automatically fill in other cells based on values the > > > > users > > > > > put in them. For example: > > > > > > > > > > If sheet2!.cells(12,3) = "N" Then > > > > > > > > > > Sheet3!.cells(3,2) = "Sold Out" > > > > > > > > > > or > > > > > > > > > > If sheet2!.cells(12,3) = "Y" Then > > > > > > > > > > Sheet3!.Visible = False > > > > > > > > > > I'll keep searching for the answers to this question but if someone could > > > > > shoot me a quick answer I would appreciate it. > > > > > > > > > > > >
From: Tom Ogilvy on 28 Dec 2005 14:59 Sorry, this should have been OnEntry rather than OnChange. Sub StartOnChange() Worksheets("Sheet2").OnEntry = "MyMacro" End Sub Or perhaps you were talking about the Change Event, a sheet level event. -- Regards, Tom Ogilvy "Tom Ogilvy" <twogilvy(a)msn.com> wrote in message news:%23WOZGj%23CGHA.1032(a)TK2MSFTNGP11.phx.gbl... > did you run a macro like > > Sub StartOnChange() > Worksheets("Sheet2").OnChange = "MyMacro" > End Sub > > -- > Regards, > Tom Ogilvy > > "hshayh0rn" <hshayh0rn(a)discussions.microsoft.com> wrote in message > news:7811B8AC-0ADD-4F01-B061-E98747268C39(a)microsoft.com... > > From the last time I posted I found the on change event and thought that > > might be the answer but the code Bob supplied does not work when I add it > to > > the on change event. > > > > "Jim Thomlinson" wrote: > > > > > That is an on demand macro that the user would have to run on their own. > You > > > could use the On Change event from Sheet 2 if you needed it to be > automatic. > > > -- > > > HTH... > > > > > > Jim Thomlinson > > > > > > > > > "hshayh0rn" wrote: > > > > > > > Thanks for the reply... So, would the user have to run the macro > manually or > > > > does excel know to run this on its own? > > > > > > > > "Bob Phillips" wrote: > > > > > > > > > Sub myMacro() > > > > > If Worksheet("Sheet2").cells(12,3) = "N" Then > > > > > Worksheet("Sheet3").cells(3,2) = "Sold Out" > > > > > ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then > > > > > Worksheet("Sheet3").Visible = False > > > > > End If > > > > > End Sub > > > > > > > > > > This would be an on-demand macro, stored in a standard code module. > > > > > > > > > > -- > > > > > > > > > > HTH > > > > > > > > > > RP > > > > > (remove nothere from the email address if mailing direct) > > > > > > > > > > > > > > > "hshayh0rn" <hshayh0rn(a)discussions.microsoft.com> wrote in message > > > > > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228(a)microsoft.com... > > > > > > I have a very basic question but I can't seem to find the answer > on here > > > > > > (probably to basic). I have a spreadsheet that I would like to > perform all > > > > > of > > > > > > my calculations / If / Then statements using VBA but I'm not sure > how to > > > > > > actually get the code to run or where to put it to run... I can > get all of > > > > > > the code to run with an on click event or something like that but > > > > > basically > > > > > > I'd like cells to automatically fill in other cells based on > values the > > > > > users > > > > > > put in them. For example: > > > > > > > > > > > > If sheet2!.cells(12,3) = "N" Then > > > > > > > > > > > > Sheet3!.cells(3,2) = "Sold Out" > > > > > > > > > > > > or > > > > > > > > > > > > If sheet2!.cells(12,3) = "Y" Then > > > > > > > > > > > > Sheet3!.Visible = False > > > > > > > > > > > > I'll keep searching for the answers to this question but if > someone could > > > > > > shoot me a quick answer I would appreciate it. > > > > > > > > > > > > > > > > >
From: hshayh0rn on 28 Dec 2005 15:10 I still seem to be having an issue with the code. I r-clicked on the worksheet I need to place the code in. I changed the drop down from "general" to "worksheet" and then selected "change". I then pasted the code into the window and went back and made the change that should have triggered the action on the spreadsheet. I then get a compile error that says: "sub or function not defined" I tried the code you gave me using Worksheet("sheet7") and Worksheet("name of worksheet") and neither seems to work. When I look at the code in error it seems to be having an issue with the word worksheet. FYI - I'm running Excel2003 "Jim Thomlinson" wrote: > This should be close to what you want... > > Private Sub Worksheet_Change(ByVal Target As Range) > On Error GoTo ErrorHandler > Application.EnableEvents = False > If Target.Address = "$C$12" Then > If Target.Value = "N" Then > Worksheet("Sheet3").Cells(3, 2) = "Sold Out" > ElseIf Target.Value = "Y" Then > Worksheet("Sheet3").Visible = False > End If > End If > > ErrorHandler: > Application.EnableEvents = True > End Sub > > This code needs to be included in the sheet and not in a standard module. > -- > HTH... > > Jim Thomlinson > > > "hshayh0rn" wrote: > > > From the last time I posted I found the on change event and thought that > > might be the answer but the code Bob supplied does not work when I add it to > > the on change event. > > > > "Jim Thomlinson" wrote: > > > > > That is an on demand macro that the user would have to run on their own. You > > > could use the On Change event from Sheet 2 if you needed it to be automatic. > > > -- > > > HTH... > > > > > > Jim Thomlinson > > > > > > > > > "hshayh0rn" wrote: > > > > > > > Thanks for the reply... So, would the user have to run the macro manually or > > > > does excel know to run this on its own? > > > > > > > > "Bob Phillips" wrote: > > > > > > > > > Sub myMacro() > > > > > If Worksheet("Sheet2").cells(12,3) = "N" Then > > > > > Worksheet("Sheet3").cells(3,2) = "Sold Out" > > > > > ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then > > > > > Worksheet("Sheet3").Visible = False > > > > > End If > > > > > End Sub > > > > > > > > > > This would be an on-demand macro, stored in a standard code module. > > > > > > > > > > -- > > > > > > > > > > HTH > > > > > > > > > > RP > > > > > (remove nothere from the email address if mailing direct) > > > > > > > > > > > > > > > "hshayh0rn" <hshayh0rn(a)discussions.microsoft.com> wrote in message > > > > > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228(a)microsoft.com... > > > > > > I have a very basic question but I can't seem to find the answer on here > > > > > > (probably to basic). I have a spreadsheet that I would like to perform all > > > > > of > > > > > > my calculations / If / Then statements using VBA but I'm not sure how to > > > > > > actually get the code to run or where to put it to run... I can get all of > > > > > > the code to run with an on click event or something like that but > > > > > basically > > > > > > I'd like cells to automatically fill in other cells based on values the > > > > > users > > > > > > put in them. For example: > > > > > > > > > > > > If sheet2!.cells(12,3) = "N" Then > > > > > > > > > > > > Sheet3!.cells(3,2) = "Sold Out" > > > > > > > > > > > > or > > > > > > > > > > > > If sheet2!.cells(12,3) = "Y" Then > > > > > > > > > > > > Sheet3!.Visible = False > > > > > > > > > > > > I'll keep searching for the answers to this question but if someone could > > > > > > shoot me a quick answer I would appreciate it. > > > > > > > > > > > > > > >
From: Jim Thomlinson on 28 Dec 2005 15:20 Sorry I copied some of Bob's code without looking to close. It shoud say worksheets not worksheet... We should mark this on the calendar because it is rare that Bob makes a mistake... :-) Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Address = "$C$12" Then If Target.Value = "N" Then Worksheets("Sheet3").Cells(3, 2) = "Sold Out" ElseIf Target.Value = "Y" Then Worksheets("Sheet3").Visible = False End If End If ErrorHandler: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "hshayh0rn" wrote: > I still seem to be having an issue with the code. I r-clicked on the > worksheet I need to place the code in. I changed the drop down from "general" > to "worksheet" and then selected "change". I then pasted the code into the > window and went back and made the change that should have triggered the > action on the spreadsheet. I then get a compile error that says: > > "sub or function not defined" > > I tried the code you gave me using Worksheet("sheet7") and Worksheet("name > of worksheet") and neither seems to work. When I look at the code in error it > seems to be having an issue with the word worksheet. FYI - I'm running > Excel2003 > > "Jim Thomlinson" wrote: > > > This should be close to what you want... > > > > Private Sub Worksheet_Change(ByVal Target As Range) > > On Error GoTo ErrorHandler > > Application.EnableEvents = False > > If Target.Address = "$C$12" Then > > If Target.Value = "N" Then > > Worksheet("Sheet3").Cells(3, 2) = "Sold Out" > > ElseIf Target.Value = "Y" Then > > Worksheet("Sheet3").Visible = False > > End If > > End If > > > > ErrorHandler: > > Application.EnableEvents = True > > End Sub > > > > This code needs to be included in the sheet and not in a standard module. > > -- > > HTH... > > > > Jim Thomlinson > > > > > > "hshayh0rn" wrote: > > > > > From the last time I posted I found the on change event and thought that > > > might be the answer but the code Bob supplied does not work when I add it to > > > the on change event. > > > > > > "Jim Thomlinson" wrote: > > > > > > > That is an on demand macro that the user would have to run on their own. You > > > > could use the On Change event from Sheet 2 if you needed it to be automatic. > > > > -- > > > > HTH... > > > > > > > > Jim Thomlinson > > > > > > > > > > > > "hshayh0rn" wrote: > > > > > > > > > Thanks for the reply... So, would the user have to run the macro manually or > > > > > does excel know to run this on its own? > > > > > > > > > > "Bob Phillips" wrote: > > > > > > > > > > > Sub myMacro() > > > > > > If Worksheet("Sheet2").cells(12,3) = "N" Then > > > > > > Worksheet("Sheet3").cells(3,2) = "Sold Out" > > > > > > ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then > > > > > > Worksheet("Sheet3").Visible = False > > > > > > End If > > > > > > End Sub > > > > > > > > > > > > This would be an on-demand macro, stored in a standard code module. > > > > > > > > > > > > -- > > > > > > > > > > > > HTH > > > > > > > > > > > > RP > > > > > > (remove nothere from the email address if mailing direct) > > > > > > > > > > > > > > > > > > "hshayh0rn" <hshayh0rn(a)discussions.microsoft.com> wrote in message > > > > > > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228(a)microsoft.com... > > > > > > > I have a very basic question but I can't seem to find the answer on here > > > > > > > (probably to basic). I have a spreadsheet that I would like to perform all > > > > > > of > > > > > > > my calculations / If / Then statements using VBA but I'm not sure how to > > > > > > > actually get the code to run or where to put it to run... I can get all of > > > > > > > the code to run with an on click event or something like that but > > > > > > basically > > > > > > > I'd like cells to automatically fill in other cells based on values the > > > > > > users > > > > > > > put in them. For example: > > > > > > > > > > > > > > If sheet2!.cells(12,3) = "N" Then > > > > > > > > > > > > > > Sheet3!.cells(3,2) = "Sold Out" > > > > > > > > > > > > > > or > > > > > > > > > > > > > > If sheet2!.cells(12,3) = "Y" Then > > > > > > > > > > > > > > Sheet3!.Visible = False > > > > > > > > > > > > > > I'll keep searching for the answers to this question but if someone could > > > > > > > shoot me a quick answer I would appreciate it. > > > > > > > > > > > > > > > > > >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: bond convexity Next: Setting an Essbase Option using code |