number input box

[Guest]

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

 

[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)

 

[Guest]

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


HTH

 

[Guest]

Perfect, thank you very much for that

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)

 

[Bob Phillips]

Toppers answer is better!

--
HTH

Bob Phillips

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