number input box

S

Stan Halls

i am trying to creat a input box that i can type a number in and when i close
the box that number will be transferd into a cell on the spreadsheet, also i
need to check that the number entered is between to 1 - 6 ,
Any ideas
 
B

Bob Phillips

Dim ans

Do
ans = Application.InputBox("input a number between 1 and 6)",
Type:=2)
If ans <> False Then
If ans < 1 Or ans > 6 Then
MsgBox "Invalid amount"
End If
End If
Loop Until ans = False Or (ans >= 1 And ans <= 6)
If ans <> False Then
Worksheets("Sheet1").Range("H10").Value = ans
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
T

Toppers

Stan,
Look at Data==>Validation==> select "Whole number" from Allow:
dropdown. You can set range to 1 to 6.


HTH
 
S

Stan Halls

Perfect, thank you very much for that

Bob Phillips said:
Dim ans

Do
ans = Application.InputBox("input a number between 1 and 6)",
Type:=2)
If ans <> False Then
If ans < 1 Or ans > 6 Then
MsgBox "Invalid amount"
End If
End If
Loop Until ans = False Or (ans >= 1 And ans <= 6)
If ans <> False Then
Worksheets("Sheet1").Range("H10").Value = ans
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
B

Bob Phillips

Toppers answer is better!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top