vba input box with restriction on what can be entered.

  • Thread starter Thread starter Tonso
  • Start date Start date
T

Tonso

In an XL2003 spreadsheet I have the following code which places a
number in cell...

Sub DecimalSelect()
Dim answer As Variant
Dim result As Long
answer = Application.InputBox("Enter 2 or 3 for the number of
Decimals." & vbCrLf & "Your MUST be2 or 3.", "Get Number", Type:=1)
If TypeName(answer) = "Boolean" Then Exit Sub
ActiveSheet.Unprotect Password:="time"
Range("R9") = answer
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True, Password:="time"
The code works, but what i wouild like is to restrict the the entry to
either '2' or '3', and not just prompt the user to enter 2 or 3. How
can I accomplish this in vba?

Thank you,
Tonso
 
Type:=1 in your code limits the response to a number.

But you'll have to check to see if it's valid after they enter the value.

Maybe you could change to a msgbox and ask if they want to use 2. If they
answer no, then you use 3.

Option Explicit
Sub testme()
Dim myNum As Long
Dim resp As Long

resp = MsgBox(Prompt:="Click Yes to use 2" & _
vbLf & "Click No to use 3" & _
vbLf & "Click Cancel to stop", _
Buttons:=vbYesNoCancel)

Select Case resp
Case Is = vbCancel
Exit Sub 'is this ok?
Case Is = vbYes
myNum = 2
Case Else
myNum = 3
End Select

MsgBox myNum

End Sub

If need more than 3 responses (2, 3, Cancel), you may want to consider creating
a userform to get the input from the user.

Debra Dalgleish has some notes:
http://contextures.com/xlUserForm01.html
(video: http://contextures.com/xlVideos05.html#UserForm01)
and
http://contextures.com/xlUserForm02.html
 
Type:=1 in your code limits the response to a number.

But you'll have to check to see if it's valid after they enter the value.

Maybe you could change to a msgbox and ask if they want to use 2.  If they
answer no, then you use 3.

Option Explicit
Sub testme()
     Dim myNum As Long
     Dim resp As Long

     resp = MsgBox(Prompt:="Click Yes to use 2" & _
                 vbLf & "Click No to use 3" & _
                 vbLf & "Click Cancel to stop", _
                 Buttons:=vbYesNoCancel)

     Select Case resp
         Case Is = vbCancel
             Exit Sub 'is this ok?
         Case Is = vbYes
             myNum = 2
         Case Else
             myNum = 3
     End Select

     MsgBox myNum

End Sub

If need more than 3 responses (2, 3, Cancel), you may want to consider creating
a userform to get the input from the user.

Debra Dalgleish has some notes:http://contextures.com/xlUserForm01.html
(video:  http://contextures.com/xlVideos05.html#UserForm01)
andhttp://contextures.com/xlUserForm02.html
Click to expand...

Thanks Dave. you gave me some good options! I just have to choose
which suits me best!!

Thanks again...

Tonso
 
Back
Top