excel 2010 vba user menu to store selection as variable

[cmccabe]

In the below excel 2010 VBA I am trying to create a user menu that stores a selection (there are only 2 choices) in the variable strInput. That selection will be used later on. Currently the 1 or 2 from the selection being stored and not the unique 5 digit code? I added the Convert section in bold, but that is returning an overflow error. Is there a better way? Thank you very much.

VBA

Dim Msg, Title As String
Dim MyInput As Integer

' Define message."
Msg = "Which array was used ? " _
& vbNewLine & "Enter 1 for Design: 00000" & vbNewLine _
& "Enter 2 for Design: 11111"

Title = "Selection of Application" ' Define title.

While MyInput.Value <> 1 And MyInput.Value <> 2

MyInput = InputBox(Msg, Title)
Select Case MyInput
    Case 1
        MsgBox "User chose Design 1"
    Case 2
        MsgBox "User chose Design 2"
    Case Else
        MsgBox “Invalid Entry. Try again.”
End Select

Wend

'STORE SELECTION '
Dim strInput As Integer
strInput = MyInput.Value

[B]' CONVERT USER CHOICE '
If MyInput = 1 Then
strInput = "00000"
Else
strInput = "11111"
End If[/B]

desired:

user selects 1, so 00000 would be stored in strInput but if the user selects 2, so 11111 would be stored in strInput

 

[Norie]

What is MyInput?

I ask because in your code you have MyInput.Value which suggests that it's some sort of object, perhaps a textbox?

 

[Fluff]

You are declaring strInput as Integer

Dim strInput As Integer

But then trying to store a string

strInput = "00000"
Else
strInput = "11111"

 

[Norie]

Fluff

That shouldn't cause the problem(s) the OP is reporting, the "00000" and "11111" will get converted.

 

[Fluff]

@Norie

Never knew that.

 

[Norie]

Fluff

To be honest I wasn't sure myself and had to double check, been a long day.