User Input inside a macro (2000 SR1)

Topic

New Reply

I’m writing a macro where I need the user to start in a certain cell in the spreadsheet in order for the macro to run properly. I would like a message box to come up asking them if they are in that cell and then if they say Yes, the macro will run, if they say No the macro will halt. I’ve tried writing it (see below) but no matter what button (Yes or No) I click, the macro will not run…I just get my msgbox that says “Put the cursor in this cell”. What am I missing?

Thanks in advance,

Christa
Sub Detail()
Dim Position As Integer
Dim Reponse As Integer
Dim MyString As String
Application.ScreenUpdating = False
Reponse = MsgBox(“Is the cursor in the first cell that contains a GL formula?”, _
vbYesNo + vbCritical + vbDefaultButton1, “GL Formula”)
If Response = vbYes Then
MyString = “Yes”
Values = ActiveCell.Value
Position = 1
Do Until Values = “”
If Values = 0 Then
ActiveCell.Offset(0, -3).Select
Selection.Resize(Selection.Columns.Count, 4).Select
With Selection.Font
.FontStyle = “Bold”
End With
ActiveCell.Offset(1, 3).Select
Values = ActiveCell.Value
Else
DrillResult
ExpandVertical
Values = ActiveCell.Value
End If
Loop
Application.CutCopyMode = False
Else
MyString = “No”
MsgBox (“Please move cursor to the first cell containing a GL Formula”)
End If
End Sub

Reply | Quote

Replies

You mispelled the variable name Response in the MsgBox line. This works:

Sub Detail()
Dim Position As Integer
Dim Reponse As Integer
Dim MyString As String
    Application.ScreenUpdating = False
    Response = MsgBox("Is the cursor in the first cell that contains a GL formula?", _
      vbYesNo + vbCritical + vbDefaultButton1, "GL Formula")
    If Response = vbYes Then
        MyString = "Yes"
        Values = ActiveCell.Value
        Position = 1
        Do Until Values = ""
            If Values = 0 Then
                ActiveCell.Offset(0, -3).Select
                Selection.Resize(Selection.Columns.Count, 4).Select
                With Selection.Font
                    .FontStyle = "Bold"
                End With
                ActiveCell.Offset(1, 3).Select
                Values = ActiveCell.Value
            Else
                DrillResult
                ExpandVertical
                Values = ActiveCell.Value
            End If
        Loop
        Application.CutCopyMode = False
    Else
        MyString = "No"
        MsgBox ("Please move cursor to the first cell containing a GL Formula")
    End If
End Sub

Reply | Quote

Ooops…Thanks for you help!

Reply | Quote

Actually, it’s also misspelt in the declaration (Dim Reponse as Integer).

I find it a good idea to use the “Option Explicit”, which can help to find that sort of error.
In VBE, set “Tools, Options, require variable declaration” checked. That will put the “Option Explicit” in all new modules- but not in existing ones.

Reply | Quote