| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming the Command button
Hello,
Maybe someone can tell me what I am doing wrong. I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. The button clicks but doesnt take you to the assigned sheet. You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("NPDES Ind Order").Select("B1").Select ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WDR Ind Order").Select("B1").Select ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WAIVER IND Order").Select("B1").Select ElseIf ("B10" = "General") And ("B14" = "Active") Then Sheets("General Order").Select("B1").Select ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then Sheets("Enrollee Record ").Select("B1").Select ElseIf ("B14" = "Draft") Then Sheets("Draft Order-Enrollee Record").Select("B1").Select End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
If you did not name the CommandButton "InputButton" then it will not work.
You can use CommandButton1_Click if it is the only button. Or make sure the Name is changed in the properties window. The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. The button clicks but doesnt take you to the assigned sheet. You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("NPDES Ind Order").Select("B1").Select ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WDR Ind Order").Select("B1").Select ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WAIVER IND Order").Select("B1").Select ElseIf ("B10" = "General") And ("B14" = "Active") Then Sheets("General Order").Select("B1").Select ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then Sheets("Enrollee Record ").Select("B1").Select ElseIf ("B14" = "Draft") Then Sheets("Draft Order-Enrollee Record").Select("B1").Select End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
On Jan 30, 8:48*am, JLGWhiz wrote:
If you did not name the CommandButton "InputButton" then it will not work. * You can use CommandButton1_Click if it is the only button. *Or make sure the Name is changed in the properties window. *The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. *I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. *The button clicks but doesnt take you to the assigned sheet. *You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' * * If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("NPDES Ind Order").Select("B1").Select * * ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WDR Ind Order").Select("B1").Select * * ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WAIVER IND Order").Select("B1").Select * * ElseIf ("B10" = "General") And ("B14" = "Active") Then * * Sheets("General Order").Select("B1").Select * * ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then * * Sheets("Enrollee Record ").Select("B1").Select * * ElseIf ("B14" = "Draft") Then * * Sheets("Draft Order-Enrollee Record").Select("B1").Select * * End If End Sub- Hide quoted text - - Show quoted text - I edited the button and renamed it InputButton and assigned the Macro abovebut it doesn't take me anywhere. The cells I reference in the code have dropdown choices, would that make a difference? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
Check the properties to see if "enabled" = True. If the click is working,
you should get some indication, like the button disappears. If the button diappears on click then you have a code problem although I don't see one at a quick glance. "rink" wrote: On Jan 30, 8:48 am, JLGWhiz wrote: If you did not name the CommandButton "InputButton" then it will not work. You can use CommandButton1_Click if it is the only button. Or make sure the Name is changed in the properties window. The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. The button clicks but doesnt take you to the assigned sheet. You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("NPDES Ind Order").Select("B1").Select ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WDR Ind Order").Select("B1").Select ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WAIVER IND Order").Select("B1").Select ElseIf ("B10" = "General") And ("B14" = "Active") Then Sheets("General Order").Select("B1").Select ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then Sheets("Enrollee Record ").Select("B1").Select ElseIf ("B14" = "Draft") Then Sheets("Draft Order-Enrollee Record").Select("B1").Select End If End Sub- Hide quoted text - - Show quoted text - I edited the button and renamed it InputButton and assigned the Macro abovebut it doesn't take me anywhere. The cells I reference in the code have dropdown choices, would that make a difference? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
Did you get the button from the Forms toolbar or the Control Toolbox? If
from the Forms toolbar, the Macro has to be attached through the Assign Macro dialog box. If from the control toolbox then the button has Its own code module which is accessed by double clicking in design mode. You probably already knew this, but just to be sure all bases are covered. "rink" wrote: On Jan 30, 8:48 am, JLGWhiz wrote: If you did not name the CommandButton "InputButton" then it will not work. You can use CommandButton1_Click if it is the only button. Or make sure the Name is changed in the properties window. The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. The button clicks but doesnt take you to the assigned sheet. You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("NPDES Ind Order").Select("B1").Select ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WDR Ind Order").Select("B1").Select ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WAIVER IND Order").Select("B1").Select ElseIf ("B10" = "General") And ("B14" = "Active") Then Sheets("General Order").Select("B1").Select ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then Sheets("Enrollee Record ").Select("B1").Select ElseIf ("B14" = "Draft") Then Sheets("Draft Order-Enrollee Record").Select("B1").Select End If End Sub- Hide quoted text - - Show quoted text - I edited the button and renamed it InputButton and assigned the Macro abovebut it doesn't take me anywhere. The cells I reference in the code have dropdown choices, would that make a difference? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
On Jan 30, 9:42*am, JLGWhiz wrote:
Check the properties to see if "enabled" = True. *If the click is working, you should get some indication, like the button disappears. *If the button diappears on click then you have a code problem although I don't see one at a quick glance. "rink" wrote: On Jan 30, 8:48 am, JLGWhiz wrote: If you did not name the CommandButton "InputButton" then it will not work. * You can use CommandButton1_Click if it is the only button. *Or make sure the Name is changed in the properties window. *The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. *I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. *The button clicks but doesnt take you to the assigned sheet. *You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' * * If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("NPDES Ind Order").Select("B1").Select * * ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WDR Ind Order").Select("B1").Select * * ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WAIVER IND Order").Select("B1").Select * * ElseIf ("B10" = "General") And ("B14" = "Active") Then * * Sheets("General Order").Select("B1").Select * * ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then * * Sheets("Enrollee Record ").Select("B1").Select * * ElseIf ("B14" = "Draft") Then * * Sheets("Draft Order-Enrollee Record").Select("B1").Select * * End If End Sub- Hide quoted text - - Show quoted text - I edited the button and renamed it InputButton and assigned the Macro abovebut it doesn't take me anywhere. *The cells I reference in the code have dropdown choices, would that make a difference?- Hide quoted text - - Show quoted text - Yes the Enabled =True. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
On Jan 30, 9:48*am, JLGWhiz wrote:
Did you get the button from the Forms toolbar or the Control Toolbox? *If from the Forms toolbar, the Macro has to be attached through the Assign Macro dialog box. *If from the control toolbox then the button has Its own code module which is accessed by double clicking in design mode. *You probably already knew this, but just to be sure all bases are covered. "rink" wrote: On Jan 30, 8:48 am, JLGWhiz wrote: If you did not name the CommandButton "InputButton" then it will not work. * You can use CommandButton1_Click if it is the only button. *Or make sure the Name is changed in the properties window. *The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. *I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. *The button clicks but doesnt take you to the assigned sheet. *You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' * * If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("NPDES Ind Order").Select("B1").Select * * ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WDR Ind Order").Select("B1").Select * * ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WAIVER IND Order").Select("B1").Select * * ElseIf ("B10" = "General") And ("B14" = "Active") Then * * Sheets("General Order").Select("B1").Select * * ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then * * Sheets("Enrollee Record ").Select("B1").Select * * ElseIf ("B14" = "Draft") Then * * Sheets("Draft Order-Enrollee Record").Select("B1").Select * * End If End Sub- Hide quoted text - - Show quoted text - I edited the button and renamed it InputButton and assigned the Macro abovebut it doesn't take me anywhere. *The cells I reference in the code have dropdown choices, would that make a difference?- Hide quoted text - - Show quoted text - I've tried both Buttons. I copied the code over on the Command Button and just assigned theMacro on the Button. Neither take you anywhere. Just for the heck of it, I added another command at the end that said Range("B18").Select and the button changed to that cell on the worksheet with the button so I don't know what is going on.. I closed the worksheet without saving so the added code would not save. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
First, "B10" is just a string in your code--it's not tied back to the worksheet
that holds the code. You can use addresses in formulas in excel--but not in your macro code. Option Explicit Option Compare Text Sub InputButton_click() If Me.Range("B2").Value = "WDR" _ And Me.Range("B10").Value = "Individual" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("NPDES Ind Order").Range("B1") ElseIf Me.Range("B2").Value = "NPDES Permits" _ And Me.Range("B10").Value = "Individual" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("WDR Ind Order").Range("B1") ElseIf Me.Range("B2").Value = "WAIVER" _ And Me.Range("B10").Value = "Individual" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("WAIVER IND Order").Range("B1") ElseIf Me.Range("b2").Value = "General" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("General Order").Range("B1") ElseIf Me.Range("B2").Value = "ENROLLEE" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("Enrollee Record ").Range("B1") ElseIf Me.Range("B14").Value = "Draft" Then Application.Goto _ Worksheets("Draft Order-Enrollee Record").Range("B1") End If End Sub The "Option Compare Text" at the top tells VBA to not worry about case differences (Active = AcTiVe = ACTIVE = active = ...) And instead of using "application.goto", you could use two lines: Worksheets("WDR Ind Order").select Worksheets("WDR Ind Order").Range("B1").select wrote: Hello, Maybe someone can tell me what I am doing wrong. I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. The button clicks but doesnt take you to the assigned sheet. You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("NPDES Ind Order").Select("B1").Select ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WDR Ind Order").Select("B1").Select ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WAIVER IND Order").Select("B1").Select ElseIf ("B10" = "General") And ("B14" = "Active") Then Sheets("General Order").Select("B1").Select ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then Sheets("Enrollee Record ").Select("B1").Select ElseIf ("B14" = "Draft") Then Sheets("Draft Order-Enrollee Record").Select("B1").Select End If End Sub -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
"You can use addresses in formulas in excel--but not in your macro code."
Doesn't sound right. You can't refer to a cell on a worksheet by just using its address. You have to use something else, like: range("b10") Dave Peterson wrote: First, "B10" is just a string in your code--it's not tied back to the worksheet that holds the code. You can use addresses in formulas in excel--but not in your macro code. Option Explicit Option Compare Text Sub InputButton_click() If Me.Range("B2").Value = "WDR" _ And Me.Range("B10").Value = "Individual" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("NPDES Ind Order").Range("B1") ElseIf Me.Range("B2").Value = "NPDES Permits" _ And Me.Range("B10").Value = "Individual" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("WDR Ind Order").Range("B1") ElseIf Me.Range("B2").Value = "WAIVER" _ And Me.Range("B10").Value = "Individual" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("WAIVER IND Order").Range("B1") ElseIf Me.Range("b2").Value = "General" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("General Order").Range("B1") ElseIf Me.Range("B2").Value = "ENROLLEE" _ And Me.Range("B14").Value = "Active" Then Application.Goto Worksheets("Enrollee Record ").Range("B1") ElseIf Me.Range("B14").Value = "Draft" Then Application.Goto _ Worksheets("Draft Order-Enrollee Record").Range("B1") End If End Sub The "Option Compare Text" at the top tells VBA to not worry about case differences (Active = AcTiVe = ACTIVE = active = ...) And instead of using "application.goto", you could use two lines: Worksheets("WDR Ind Order").select Worksheets("WDR Ind Order").Range("B1").select wrote: Hello, Maybe someone can tell me what I am doing wrong. I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. The button clicks but doesnt take you to the assigned sheet. You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("NPDES Ind Order").Select("B1").Select ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WDR Ind Order").Select("B1").Select ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WAIVER IND Order").Select("B1").Select ElseIf ("B10" = "General") And ("B14" = "Active") Then Sheets("General Order").Select("B1").Select ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then Sheets("Enrollee Record ").Select("B1").Select ElseIf ("B14" = "Draft") Then Sheets("Draft Order-Enrollee Record").Select("B1").Select End If End Sub -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
I see dave has already fixed the code. I took a little more time to look at
it and realized the syntax was not correct. Here is what I was going to suggest. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If Range("0B2") = "WDR" And Range("B10") = "Individual" _ And Range("B14") = "Active" Then Sheets("NPDES Ind Order").Select Range("B1").Select ElseIf Range("B2") = "NPDES Permits" And Range("B10") _ = "Individual" And Range("B14") = "Active" Then Sheets("WDR Ind Order").Select Range("B1").Select ElseIf Range("B2") = "WAIVER" And Range("B10") = _ "Individual" And Range("B14") = "Active" Then Sheets("WAIVER IND Order").Select Range("B1").Select ElseIf Range("B10") = "General" And Range("B14") _ = "Active" Then Sheets("General Order").Select Range("B1").Select ElseIf Range("B2") = "ENROLLEE" And Range("B14") _ = "Active" Then Sheets("Enrollee Record ").Select Range("B1").Select ElseIf Range("B14") = "Draft" Then Sheets("Draft Order-Enrollee Record").Select Range("B1").Select End If End Sub "rink" wrote: On Jan 30, 9:48 am, JLGWhiz wrote: Did you get the button from the Forms toolbar or the Control Toolbox? If from the Forms toolbar, the Macro has to be attached through the Assign Macro dialog box. If from the control toolbox then the button has Its own code module which is accessed by double clicking in design mode. You probably already knew this, but just to be sure all bases are covered. "rink" wrote: On Jan 30, 8:48 am, JLGWhiz wrote: If you did not name the CommandButton "InputButton" then it will not work. You can use CommandButton1_Click if it is the only button. Or make sure the Name is changed in the properties window. The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. The button clicks but doesnt take you to the assigned sheet. You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("NPDES Ind Order").Select("B1").Select ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WDR Ind Order").Select("B1").Select ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WAIVER IND Order").Select("B1").Select ElseIf ("B10" = "General") And ("B14" = "Active") Then Sheets("General Order").Select("B1").Select ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then Sheets("Enrollee Record ").Select("B1").Select ElseIf ("B14" = "Draft") Then Sheets("Draft Order-Enrollee Record").Select("B1").Select End If End Sub- Hide quoted text - - Show quoted text - I edited the button and renamed it InputButton and assigned the Macro abovebut it doesn't take me anywhere. The cells I reference in the code have dropdown choices, would that make a difference?- Hide quoted text - - Show quoted text - I've tried both Buttons. I copied the code over on the Command Button and just assigned theMacro on the Button. Neither take you anywhere. Just for the heck of it, I added another command at the end that said Range("B18").Select and the button changed to that cell on the worksheet with the button so I don't know what is going on.. I closed the worksheet without saving so the added code would not save. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
On Jan 30, 10:24*am, Dave Peterson wrote:
"You can use addresses in formulas in excel--but not in your macro code." Doesn't sound right. You can't refer to a cell on a worksheet by just using its address. *You have to use something else, like: *range("b10") Dave Peterson wrote: First, "B10" is just a string in your code--it's not tied back to the worksheet that holds the code. You can use addresses in formulas in excel--but not in your macro code. Option Explicit Option Compare Text Sub InputButton_click() * * If Me.Range("B2").Value = "WDR" _ * * *And Me.Range("B10").Value = "Individual" _ * * *And Me.Range("B14").Value = "Active" Then * * * * * * Application.Goto Worksheets("NPDES Ind Order").Range("B1") * * ElseIf Me.Range("B2").Value = "NPDES Permits" _ * * * * *And Me.Range("B10").Value = "Individual" _ * * * * *And Me.Range("B14").Value = "Active" Then * * * * * * Application.Goto Worksheets("WDR Ind Order").Range("B1") * * ElseIf Me.Range("B2").Value = "WAIVER" _ * * * * *And Me.Range("B10").Value = "Individual" _ * * * * *And Me.Range("B14").Value = "Active" Then * * * * * * Application.Goto Worksheets("WAIVER IND Order").Range("B1") * * ElseIf Me.Range("b2").Value = "General" _ * * * * *And Me.Range("B14").Value = "Active" Then * * * * * * Application.Goto Worksheets("General Order").Range("B1") * * ElseIf Me.Range("B2").Value = "ENROLLEE" _ * * * * *And Me.Range("B14").Value = "Active" Then * * * * * * Application.Goto Worksheets("Enrollee Record ").Range("B1") * * ElseIf Me.Range("B14").Value = "Draft" Then * * * * * * Application.Goto _ * * * * * * * * Worksheets("Draft Order-Enrollee Record").Range("B1") * * End If End Sub The "Option Compare Text" at the top tells VBA to not worry about case differences (Active = AcTiVe = ACTIVE = active = ...) And instead of using "application.goto", you could use two lines: * * * Worksheets("WDR Ind Order").select * * * Worksheets("WDR Ind Order").Range("B1").select wrote: Hello, Maybe someone can tell me what I am doing wrong. *I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. *The button clicks but doesnt take you to the assigned sheet. *You help is much appreciated.. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' * * If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("NPDES Ind Order").Select("B1").Select * * ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WDR Ind Order").Select("B1").Select * * ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WAIVER IND Order").Select("B1").Select * * ElseIf ("B10" = "General") And ("B14" = "Active") Then * * Sheets("General Order").Select("B1").Select * * ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then * * Sheets("Enrollee Record ").Select("B1").Select * * ElseIf ("B14" = "Draft") Then * * Sheets("Draft Order-Enrollee Record").Select("B1").Select * * End If End Sub -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - I have tried what you suggest and if I just add Range or use before the statement I get either a global error message or if I use the . it wants a "Then or "Go To" |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
On Jan 30, 10:40*am, JLGWhiz
wrote: I see dave has already fixed the code. *I took a little more time to look at it and realized the syntax was not correct. *Here is what I was going to suggest. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' * * If Range("0B2") = "WDR" And Range("B10") = "Individual" _ And Range("B14") = "Active" Then * * Sheets("NPDES Ind Order").Select * * Range("B1").Select * * ElseIf Range("B2") = "NPDES Permits" And Range("B10") _ = "Individual" And Range("B14") = "Active" Then * * Sheets("WDR Ind Order").Select * * Range("B1").Select * * ElseIf Range("B2") = "WAIVER" And Range("B10") = _ "Individual" And Range("B14") = "Active" Then * * Sheets("WAIVER IND Order").Select * * Range("B1").Select * * ElseIf Range("B10") = "General" And Range("B14") _ = "Active" Then * * Sheets("General Order").Select * * Range("B1").Select * * ElseIf Range("B2") = "ENROLLEE" And Range("B14") _ = "Active" Then * * Sheets("Enrollee Record ").Select * * Range("B1").Select * * ElseIf Range("B14") = "Draft" Then * * Sheets("Draft Order-Enrollee Record").Select * * Range("B1").Select * * End If End Sub "rink" wrote: On Jan 30, 9:48 am, JLGWhiz wrote: Did you get the button from the Forms toolbar or the Control Toolbox? *If from the Forms toolbar, the Macro has to be attached through the Assign Macro dialog box. *If from the control toolbox then the button has Its own code module which is accessed by double clicking in design mode. *You probably already knew this, but just to be sure all bases are covered. "rink" wrote: On Jan 30, 8:48 am, JLGWhiz wrote: If you did not name the CommandButton "InputButton" then it will not work. * You can use CommandButton1_Click if it is the only button. *Or make sure the Name is changed in the properties window. *The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. *I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. *The button clicks but doesnt take you to the assigned sheet. *You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' * * If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("NPDES Ind Order").Select("B1").Select * * ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WDR Ind Order").Select("B1").Select * * ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then * * Sheets("WAIVER IND Order").Select("B1").Select * * ElseIf ("B10" = "General") And ("B14" = "Active") Then * * Sheets("General Order").Select("B1").Select * * ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then * * Sheets("Enrollee Record ").Select("B1").Select * * ElseIf ("B14" = "Draft") Then * * Sheets("Draft Order-Enrollee Record").Select("B1").Select * * End If End Sub- Hide quoted text - - Show quoted text - I edited the button and renamed it InputButton and assigned the Macro abovebut it doesn't take me anywhere. *The cells I reference in the code have dropdown choices, would that make a difference?- Hide quoted text - - Show quoted text - I've tried both Buttons. *I copied the code over on the Command Button and just assigned theMacro on the Button. *Neither take you anywhere. Just for the heck of it, I added another command at the end that said Range("B18").Select and the button changed to that cell on the worksheet with the button so I don't know what is going on.. I closed the worksheet without saving so the added code would not save.- Hide quoted text - - Show quoted text - Ok, I fixed the code as follows and it runs except for the last 2 commands. Thanks for all of your help.. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by DAS Staff ' ' If Range("B2") = "WDR" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WDR Ind Order").Select Range("B1").Select ElseIf Range("B2") = "NPDES Permits" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("NPDES Ind Order").Select Range("B1").Select ElseIf Range("B2") = "WAIVER" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WAIVER IND Order").Select Range("B1").Select ElseIf Range("B10") = "General" And Range("B14") = "Active" Then Sheets("General Order").Select Range("B1").Select ElseIf Range("B2") = "ENROLLEE" And Range("B14") = "Active" Then Sheets("Enrollee").Select Range("B1").Select ElseIf Range("B14") = "Draft" Then Sheets("Draft Order Enrollee Record").Select Range("B1").Select End If End Sub |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
I'm confused about your reply.
Did you try copy|pasting my first response into the worksheet module with the commandbutton on it? And what happened when you tried that? If you changed the suggested code, it's time to repost your new version. rink wrote: <<snipped I have tried what you suggest and if I just add Range or use before the statement I get either a global error message or if I use the . it wants a "Then or "Go To" -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
On Jan 30, 12:04*pm, Dave Peterson wrote:
I'm confused about your reply. Did you try copy|pasting my first response into the worksheet module with the commandbutton on it? And what happened when you tried that? If you changed the suggested code, it's time to repost your new version. rink wrote: <<snipped I have tried what you suggest and if I just add Range or use before the statement I get either a global error message or if I use the . it wants a "Then or "Go To" -- Dave Peterson Dave, I used the code suggested by JLGWhiz and that worked. |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
On Jan 30, 12:04*pm, Dave Peterson wrote:
I'm confused about your reply. Did you try copy|pasting my first response into the worksheet module with the commandbutton on it? And what happened when you tried that? If you changed the suggested code, it's time to repost your new version. rink wrote: <<snipped I have tried what you suggest and if I just add Range or use before the statement I get either a global error message or if I use the . it wants a "Then or "Go To" -- Dave Peterson Sorry, I am attaching the new code below: Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by DAS Staff ' ' If Range("B2") = "WDR" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WDR Ind Order").Select Range("B1").Select ElseIf Range("B2") = "NPDES Permits" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("NPDES Ind Order").Select Range("B1").Select ElseIf Range("B2") = "WAIVER" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WAIVER IND Order").Select Range("B1").Select ElseIf Range("B10") = "General" And Range("B14") = "Active" Then Sheets("General Order").Select Range("B1").Select ElseIf Range("B2") = "ENROLLEE" And Range("B14") = "Active" Then Sheets("Enrollee").Select Range("B1").Select ElseIf Range("B14") = "Draft" Then Sheets("Draft Order Enrollee Record").Select Range("B1").Select End If End Sub |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
This worked for you?
rink wrote: On Jan 30, 12:04 pm, Dave Peterson wrote: I'm confused about your reply. Did you try copy|pasting my first response into the worksheet module with the commandbutton on it? And what happened when you tried that? If you changed the suggested code, it's time to repost your new version. rink wrote: <<snipped I have tried what you suggest and if I just add Range or use before the statement I get either a global error message or if I use the . it wants a "Then or "Go To" -- Dave Peterson Sorry, I am attaching the new code below: Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by DAS Staff ' ' If Range("B2") = "WDR" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WDR Ind Order").Select Range("B1").Select ElseIf Range("B2") = "NPDES Permits" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("NPDES Ind Order").Select Range("B1").Select ElseIf Range("B2") = "WAIVER" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WAIVER IND Order").Select Range("B1").Select ElseIf Range("B10") = "General" And Range("B14") = "Active" Then Sheets("General Order").Select Range("B1").Select ElseIf Range("B2") = "ENROLLEE" And Range("B14") = "Active" Then Sheets("Enrollee").Select Range("B1").Select ElseIf Range("B14") = "Draft" Then Sheets("Draft Order Enrollee Record").Select Range("B1").Select End If End Sub -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
On Jan 30, 12:29*pm, Dave Peterson wrote:
This worked for you? rink wrote: On Jan 30, 12:04 pm, Dave Peterson wrote: I'm confused about your reply. Did you try copy|pasting my first response into the worksheet module with the commandbutton on it? And what happened when you tried that? If you changed the suggested code, it's time to repost your new version. |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
The code you posted is different from the code JLGWhiz posted. And it works
differently, too. But if you're happy... rink wrote: <<snipped Yes, all of the routine now runs and redirects me to the correct worksheets. Thanks for all of your help. Bob Rinker -- Dave Peterson |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
Sheets("Draft Order Enrollee Record").Select Range("B1").Select
When you are selecting a new sheet and a range on that sheet, use two lines: Sheets("Draft Order Enrollee Record").Select Range("B1").Select This applies to all of the lines in your code where you are using Range("B1").Select This will reduce the likelyhood of generating error messages and will ensure that Range B1 on the correct sheet is selected. Otherwise, it could simply select B1 on the last active sheet and not send an error message, so you would not know that an error had occurred. "rink" wrote: On Jan 30, 10:40 am, JLGWhiz wrote: I see dave has already fixed the code. I took a little more time to look at it and realized the syntax was not correct. Here is what I was going to suggest. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If Range("0B2") = "WDR" And Range("B10") = "Individual" _ And Range("B14") = "Active" Then Sheets("NPDES Ind Order").Select Range("B1").Select ElseIf Range("B2") = "NPDES Permits" And Range("B10") _ = "Individual" And Range("B14") = "Active" Then Sheets("WDR Ind Order").Select Range("B1").Select ElseIf Range("B2") = "WAIVER" And Range("B10") = _ "Individual" And Range("B14") = "Active" Then Sheets("WAIVER IND Order").Select Range("B1").Select ElseIf Range("B10") = "General" And Range("B14") _ = "Active" Then Sheets("General Order").Select Range("B1").Select ElseIf Range("B2") = "ENROLLEE" And Range("B14") _ = "Active" Then Sheets("Enrollee Record ").Select Range("B1").Select ElseIf Range("B14") = "Draft" Then Sheets("Draft Order-Enrollee Record").Select Range("B1").Select End If End Sub "rink" wrote: On Jan 30, 9:48 am, JLGWhiz wrote: Did you get the button from the Forms toolbar or the Control Toolbox? If from the Forms toolbar, the Macro has to be attached through the Assign Macro dialog box. If from the control toolbox then the button has Its own code module which is accessed by double clicking in design mode. You probably already knew this, but just to be sure all bases are covered. "rink" wrote: On Jan 30, 8:48 am, JLGWhiz wrote: If you did not name the CommandButton "InputButton" then it will not work. You can use CommandButton1_Click if it is the only button. Or make sure the Name is changed in the properties window. The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. The button clicks but doesnt take you to the assigned sheet. You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("NPDES Ind Order").Select("B1").Select ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WDR Ind Order").Select("B1").Select ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WAIVER IND Order").Select("B1").Select ElseIf ("B10" = "General") And ("B14" = "Active") Then Sheets("General Order").Select("B1").Select ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then Sheets("Enrollee Record ").Select("B1").Select ElseIf ("B14" = "Draft") Then Sheets("Draft Order-Enrollee Record").Select("B1").Select End If End Sub- Hide quoted text - - Show quoted text - I edited the button and renamed it InputButton and assigned the Macro abovebut it doesn't take me anywhere. The cells I reference in the code have dropdown choices, would that make a difference?- Hide quoted text - - Show quoted text - I've tried both Buttons. I copied the code over on the Command Button and just assigned theMacro on the Button. Neither take you anywhere. Just for the heck of it, I added another command at the end that said Range("B18").Select and the button changed to that cell on the worksheet with the button so I don't know what is going on.. I closed the worksheet without saving so the added code would not save.- Hide quoted text - - Show quoted text - Ok, I fixed the code as follows and it runs except for the last 2 commands. Thanks for all of your help.. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by DAS Staff ' ' If Range("B2") = "WDR" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WDR Ind Order").Select Range("B1").Select ElseIf Range("B2") = "NPDES Permits" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("NPDES Ind Order").Select Range("B1").Select ElseIf Range("B2") = "WAIVER" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WAIVER IND Order").Select Range("B1").Select ElseIf Range("B10") = "General" And Range("B14") = "Active" Then Sheets("General Order").Select Range("B1").Select ElseIf Range("B2") = "ENROLLEE" And Range("B14") = "Active" Then Sheets("Enrollee").Select Range("B1").Select ElseIf Range("B14") = "Draft" Then Sheets("Draft Order Enrollee Record").Select Range("B1").Select End If End Sub |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
If this code is still behind the worksheet that contains that commandbutton, the
code will have to look more like: Sheets("Draft Order Enrollee Record").Select Sheets("Draft Order Enrollee Record").Range("B1").Select That second line has an unqualified range and unqualified ranges behind worksheets refer to sheet that owns the code. And when the code tries to select a cell on a sheet that isn't active, it'll blow up. JLGWhiz wrote: Sheets("Draft Order Enrollee Record").Select Range("B1").Select When you are selecting a new sheet and a range on that sheet, use two lines: Sheets("Draft Order Enrollee Record").Select Range("B1").Select This applies to all of the lines in your code where you are using Range("B1").Select This will reduce the likelyhood of generating error messages and will ensure that Range B1 on the correct sheet is selected. Otherwise, it could simply select B1 on the last active sheet and not send an error message, so you would not know that an error had occurred. "rink" wrote: On Jan 30, 10:40 am, JLGWhiz wrote: I see dave has already fixed the code. I took a little more time to look at it and realized the syntax was not correct. Here is what I was going to suggest. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If Range("0B2") = "WDR" And Range("B10") = "Individual" _ And Range("B14") = "Active" Then Sheets("NPDES Ind Order").Select Range("B1").Select ElseIf Range("B2") = "NPDES Permits" And Range("B10") _ = "Individual" And Range("B14") = "Active" Then Sheets("WDR Ind Order").Select Range("B1").Select ElseIf Range("B2") = "WAIVER" And Range("B10") = _ "Individual" And Range("B14") = "Active" Then Sheets("WAIVER IND Order").Select Range("B1").Select ElseIf Range("B10") = "General" And Range("B14") _ = "Active" Then Sheets("General Order").Select Range("B1").Select ElseIf Range("B2") = "ENROLLEE" And Range("B14") _ = "Active" Then Sheets("Enrollee Record ").Select Range("B1").Select ElseIf Range("B14") = "Draft" Then Sheets("Draft Order-Enrollee Record").Select Range("B1").Select End If End Sub "rink" wrote: On Jan 30, 9:48 am, JLGWhiz wrote: Did you get the button from the Forms toolbar or the Control Toolbox? If from the Forms toolbar, the Macro has to be attached through the Assign Macro dialog box. If from the control toolbox then the button has Its own code module which is accessed by double clicking in design mode. You probably already knew this, but just to be sure all bases are covered. "rink" wrote: On Jan 30, 8:48 am, JLGWhiz wrote: If you did not name the CommandButton "InputButton" then it will not work. You can use CommandButton1_Click if it is the only button. Or make sure the Name is changed in the properties window. The caption is not the name. " wrote: Hello, Maybe someone can tell me what I am doing wrong. I wrote the following Macro and it appears to run correctly, however when I assign it to the command button, nothing executes. The button clicks but doesnt take you to the assigned sheet. You help is much appreciated. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by ' ' If ("B2" = "WDR") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("NPDES Ind Order").Select("B1").Select ElseIf ("B2" = "NPDES Permits") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WDR Ind Order").Select("B1").Select ElseIf ("B2" = "WAIVER") And ("B10" = "Individual") And ("B14" = "Active") Then Sheets("WAIVER IND Order").Select("B1").Select ElseIf ("B10" = "General") And ("B14" = "Active") Then Sheets("General Order").Select("B1").Select ElseIf ("B2" = "ENROLLEE") And ("B14" = "Active") Then Sheets("Enrollee Record ").Select("B1").Select ElseIf ("B14" = "Draft") Then Sheets("Draft Order-Enrollee Record").Select("B1").Select End If End Sub- Hide quoted text - - Show quoted text - I edited the button and renamed it InputButton and assigned the Macro abovebut it doesn't take me anywhere. The cells I reference in the code have dropdown choices, would that make a difference?- Hide quoted text - - Show quoted text - I've tried both Buttons. I copied the code over on the Command Button and just assigned theMacro on the Button. Neither take you anywhere. Just for the heck of it, I added another command at the end that said Range("B18").Select and the button changed to that cell on the worksheet with the button so I don't know what is going on.. I closed the worksheet without saving so the added code would not save.- Hide quoted text - - Show quoted text - Ok, I fixed the code as follows and it runs except for the last 2 commands. Thanks for all of your help.. Sub InputButton_click() ' ' WDRIndOrder Macro ' Macro recorded 1/24/2008 by DAS Staff ' ' If Range("B2") = "WDR" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WDR Ind Order").Select Range("B1").Select ElseIf Range("B2") = "NPDES Permits" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("NPDES Ind Order").Select Range("B1").Select ElseIf Range("B2") = "WAIVER" And Range("B10") = "Individual" And Range("B14") = "Active" Then Sheets("WAIVER IND Order").Select Range("B1").Select ElseIf Range("B10") = "General" And Range("B14") = "Active" Then Sheets("General Order").Select Range("B1").Select ElseIf Range("B2") = "ENROLLEE" And Range("B14") = "Active" Then Sheets("Enrollee").Select Range("B1").Select ElseIf Range("B14") = "Draft" Then Sheets("Draft Order Enrollee Record").Select Range("B1").Select End If End Sub -- Dave Peterson |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming the Command button
Another way to do it would have been to use the application.goto
Application.Goto Worksheets("NPDES Ind Order").Range("B1") or Application.Goto Worksheets("NPDES Ind Order").Range("B1"), Scroll:=true rink wrote: <<snipped Yes, you are correct. I was getting to the correct worksheet but not the correct cell. I did as you suggested and now it takes me to the correct sheet and cell. Thank you very much, Bob -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting, command button and programming in VB | Excel Discussion (Misc queries) | |||
| Programming a Command Button | Excel Programming | |||
| Programming a "Save as..." command button within Excel | Excel Discussion (Misc queries) | |||
| Multiple Command button programming | Excel Programming | |||
| Programming command button to execute on a different worksheet | Excel Programming | |||
Linear Mode
