How to add a cancel function to an inputbox

[vba317]

I have the following inputbox I need to add a function to the inputbox function so that when I click on cancel the subroutine ends. I would appreciate any help.

myNum2 = Application.InputBox(prompt:="Enter 1 for PBK and PBO type codes 2 for assembly 3 for PAQ3000 codes", Title:="Enter Code", Type:=1)

 

[MajP]

If you hit cancel the inputbox returns an empty string.

If myNum2 = "" then exit sub

 

[vba317]

I have tried this method. I am getting a runtime error 13 Type mismatch. Any sugesstions?

 

[Loomah]

how have you declared the variable myNum2?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Andrzejek]

Insetad of an input box, I would rather give user a drop down combo to select from:
[tt]
Choose Type: 1 - PBK and PBO
2 - assembly
3 - PAQ3000[/tt]

You can define the default value, no need to validate the entry since user can not select (type) "abcd"

Have fun.

---- Andy

 

[HarleyQuinn]

For the current situation, I'm with Loomah, if myNum2 is declared as a numeric datatype (long, int etc.) then when the box returns "" it will return the Type Mismatch error (at least that's what I think he was meaning ) as you can't set these variable types to "" (or evaluate them against ""). As the inputbox returns a string it would be solved if you changed your dimensioning statement.

In the grand scheme of things I'd be tempted to go with Andy's suggestion as not only is it easier for the user to make the choice it's also easier to maintain (IMO).

Regards

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[vba317]

MyNum2 is declared as a double which is what I want. I like Andy's suggestion but I don't know how to set up a drop down combo box in excel. I also know the type = 1 verifies that the entry must be a number. If I change my Dim statement to a string will that affect how the numbers get evaluated. I have 9 other Dim statements that I would have to change so I need to make sure that the numbers are still evaluated as numbers.

Tom

 

[combo]

If myNum2 is declared as numeric then:
- 'Cancel' key will set myNum2=0,
- 'OK' key will display input error and will wait for proper (numeric) type.
So you can test input for any proper value (1, 2 or 3), as the user can input any other value too. Andy's tip will make
life easier, both for you and for the user.

combo

 

[HarleyQuinn]

combo, Oops! you are correct

I had missed that the OP was using Application.InputBox rather than simply the InputBox call.

Apologies to all

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[Loomah]

application.inputbox indeed!
damn these friday afternoons..........and the inevitable onset of extreme laziness!

i'm now more intrigued with where the type mismatch is being generated?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

MyNum2 is declared as a double which is what I want
Why a double for values in (1,2,3) ?
Declare MyNum2 as an integer and then test it against False (0) to know if the Cancel button was clicked.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[strongm]

>i'm now more intrigued with where the type mismatch is being generated?

If myNum2 = "" then exit sub

 

[Loomah]

d'oh!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

I like Andy's suggestion but I don't know how to set up a drop down combo box in excel.

Add userform to your vba project, add combobox. The example of code, no OK/Cancel buttons interacting:

Private Sub UserForm_Initialize()
With ComboBox1
    .AddItem "<Choose type from the list>"
    .AddItem "1 - PBK and PBO>"
    .AddItem "2 - assembly"
    .AddItem "3 - PAQ3000"
    .ListIndex = 0
End With
End Sub

Private Sub UserForm_Terminate()
myNum2 = ComboBox1.ListIndex
End Sub

Public myNum2 As Integer

Sub test()
myNum2 = 0
UserForm1.Show
If myNum2 = 0 Then
    MsgBox "No valid selection"
Else
    MsgBox myNum2 & " selected"
End If
End Sub

combo