VBA Question; Message box style input?

[Mike54]

Hello, would someone be kind enough to point me in the right direction please. I'm writting a program in VBA and need to have a window pop up when a command-click button is clicked, somthing like the msg box.

However I would then like the operator to have to answer a question such as "Please Enter Player's Number" they would then type in a number between 1 and 24.

I then want to place that "input" into a cell on the sheet.

Sadly I don't know where to begin, any suggestions please.
Many thanks

 

[kevin.philips]

Hi,

Something like this should get you started:

Private Sub CommandButton1_Click()
Dim ReqdAnswer As String
ReqdAnswer = InputBox("Please enter players number", "Input Required")
Cells(1, 1) = ReqdAnswer
End Sub

The cells refer to where the answer should be placed (Row Number, Column Number)

Beware though this snippet does not have any error checking and assumes the active sheet is where the information is required.

Regards

Kevin

 

[DominicB]

Good afternoon Mike54

What about this :

Sub test()
a = InputBox("Enter a number (1-24)")
If IsNumeric(a) And a >= 1 And a <= 24 Then
Range("A1").Value = a
Exit Sub
End If
MsgBox "Problem!"
End Sub

HTH

DominicB

 

[SidBord]

Here's a possible refinement.
The INPUTBOX statement presents two buttons: "OK" and "CANCEL". You really ought to allow the user to cancel in case he doesn't want to proceed. I usually use a sequence like this to take care of CANCEL:

Sub GetAnswer()
Dim Ans As Variant, CancelFlg As Long
Ans = InputBox("Enter ...")
If Ans = vbNullString _
    Then
        CancelFlg = 1
        GoTo Finish
    End If
'. . .
Finish:
If CancelFlg Then MsgBox "User Cancel"
End Sub

Note that the variable "Ans" has to be a variant to take care of the possibility of two different answer types. You don't have to write a message when the user cancels, but some folks get confused when nothing happens after a cancel.

Just some additional thoughts for your dining and dancing pleasure.
Sid

 

[Mike54]

Gentlemen, many thanks for all your methods, thanks to you I now have it working. You have probably saved me hours of frustration and I've learnt another little bit of VBA.

This vba stuff is dead clever but difficult to start with does it get easier?

Thanks again

Mike

 

[erik.van.geit]

Hi,

since you want the user to type a NUMBER
you could add an argument in the INPUTBOX-line
Type:=1

response = Application.InputBox("Prompt text", "TITLE", "DEFAULT (can be reference to cell or ...)", Type:=1)

try typing other data, you will be prompted to fill in a number

kind regards,
Erik

EDIT: check the helpfiles

 

[SidBord]

Mike, from my point of view, it doesn't get too much easier. Just continue to use forums like this one and get a good reference book like the ones written by John Walkenbach (check out his site).
I didn't like beer at first, but I kept trying it.