Programming the Command button

  • Thread starter Thread starter rink2
  • Start date Start date
R

rink2

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
 
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
Click to expand...

How exactly have you assigned the code to the button? What is the
name of the button?

Kind regards,
Matt Richardson
http://teachr.blogspot.com
 
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.
 
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 surethe
Name is changed in the properties window.  The caption is not the name.








- Show quoted text -
Click to expand...

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?
 
How exactly have you assigned the code to the button?  What is the
name of the button?

Kind regards,
Matt Richardsonhttp://teachr.blogspot.com- Hide quoted text -

- Show quoted text -
Click to expand...

First, I right clicked the button and renamed it InputButton, then
clicked Assign Macro and linked the InputButton Macro, then I edited
the text on the button and named it Input Button. When I run the code
I don't get errors but I also don't go anywhere. Would it help if I
attached the spreadsheet?
 
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.
 
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.
 
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.






- Show quoted text -
Click to expand...

Yes the Enabled =True.
 
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.






- Show quoted text -
Click to expand...

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.
 
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
 
"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")
 
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
 
"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.  Youhave to
use something else, like:  range("b10")














--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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"
 
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






- Show quoted text -
Click to expand...

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
 
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.
 
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:
Click to expand...

Dave,

I used the code suggested by JLGWhiz and that worked.
 
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:
Click to expand...

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
 
This worked for you?


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
Click to expand...
 
This worked for you?








--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

Yes, all of the routine now runs and redirects me to the correct
worksheets. Thanks for all of your help.

Bob Rinker
 
The code you posted is different from the code JLGWhiz posted. And it works
differently, too.

But if you're happy...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Improving the code from a recorded macro 2
Excel VBA - How to transpose every fifth row to same column on another sheet 1
Hide/Unhide Multiple Sheets 3
Combine columns from seperate sheets into new sheet- macro improve 4
Auto fill Sheets 1
Worksheet_Change sub does not trigger a called macro 3
Advanced Filter works in recorder but not while running macro 5
Code 7

Back
Top