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