VBA input Box- cancel and Variable Setting

Question

Below is my code. I am writing a Macro to go on a button on the first page of an excel report that will take users to different tabs in the report based on the number they use. The issue is if I use the integer variable, if a user cancels the box or hits the x a error occurs. The numbers work fine in regards to the sheets with this. However, if i dim it as Variant an error no longer occurs, but then the numbers input no longer work. For ex. 3 brings up the invalid sheet box even if there are 8 sheets in the workbook. I think the variant is causing the input numbers to be a different format from integer? I think I can use the on error resume, but I was just wondering what that would look like. Or if there is another way to do this without using that method

Public Sub Summary_select()

     Dim x As Integer


     x = InputBox("Please Input The Sheet Number")


     If Not (IsNumeric(x)) Then
     MsgBox ("User Cancelled")
     ElseIf x = 0 Then
     MsgBox ("User Entered an Invalid Number of sheets")
     ElseIf x > Sheets.Count Then
     MsgBox ("User Entered an Invalid Number of sheets")
     Else
     Sheets(x).Activate
     Range("A1").Select
     End If

     End Sub

See this: stackoverflow.com/questions/44992958/…

Kostas K.
– Kostas K.

2018-01-10 18:26:44 +00:00
Commented Jan 10, 2018 at 18:26 — Kostas K.
– Kostas K., Commented Jan 10, 2018 at 18:26

braX · Accepted Answer · 2018-01-10 18:57:57Z

2

InputBox returns a String, so Dim x as String

Then once you have validated that it is numeric and within the correct range, use it as a number like this:

Worksheets(Val(x)).Activate

And do the same for your checks:

ElseIf Val(x) = 0 Then

Also, to critique your code a bit, what happens if the user enters -1? Technically, that's numeric, but not valid.

instead of ElseIf Val(x) = 0 Then you should use

ElseIf Val(x) < 1 Then

What about if they enter 1.5? You may want to also include a check for it being an integer:

If IsNumeric(x) Then
  If Val(x) <> Int(x) Then
    MsgBox "Please enter an Integer"
  End If
End If

edited Jan 10, 2018 at 18:57

answered Jan 10, 2018 at 18:24

braX

11.9k5 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

UserX Over a year ago

Thanks this worked like a charm. I am fairly new at VBA, If you use an input box in this instance where you want a number returned to use later on in the code, do you always need to do something like this?

braX Over a year ago

If you are askinig if an InputBox always returns a String the answer is yes. It's up to you to validate it if you are expecting a numeric value.

Mathieu Guindon Over a year ago

Nice answer, upvoted. Also, the "User Cancelled" box is misleading. If StrPtr(x) = 0 Then would be the correct condition for displaying such a msgbox.

Mathieu Guindon Over a year ago

The more I look at all the validations and possible edge cases, the more this looks like a job for a dedicated, properly unit-tested function...

braX Over a year ago

@Mat'sMug I wouldnt use an InputBox at all for this. I would make a Userform with a combobox and populate it with all the valid sheet numbers, or better yet, sheet names.

|

K.Dᴀᴠɪs · Accepted Answer · 2018-01-10 18:24:29Z

0

The reason you are getting an error is because when a use clicks cancel on an inputbox, the input box outputs an empty string - but the problem is your Integer declaration cannot accept a string.

So you could do something along the lines of:

On Error Resume Next
x = InputBox("Please Input The Sheet Number")
On Error Goto 0

And if an error occurs when passing the inputbox to x, then x = 0.

answered Jan 10, 2018 at 18:24

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Comments

SIM · Accepted Answer · 2018-01-10 18:46:30Z

0

Although I do not have much knowledge on vba core functionality, I've tried to modify your existing code to work the way you wanted. Give this a go:

Public Sub Summary_select()
     Dim x As String

     x = InputBox("Please Input The Sheet Number")

     If Not IsNumeric(x) Then
        MsgBox ("User Cancelled")
     ElseIf x = 0 Then
        MsgBox ("User Entered an Invalid Number of sheets")
     ElseIf x > Sheets.Count Then
        MsgBox ("User Entered an Invalid Number of sheets")
     Else
        Sheets("Sheet" & x).Activate
        Range("A1").Select
     End If
End Sub

answered Jan 10, 2018 at 18:46

SIM

22.5k6 gold badges45 silver badges116 bronze badges

Collectives™ on Stack Overflow

VBA input Box- cancel and Variable Setting

3 Answers 3

11 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related