Input Dialog Box

[NoviceExcelUser]

I am trying to create a dialog box in one cell which checks value in another
cell (e.g. yes or no) and if it is true, then asks for a value number (1-100)
and if it is not then enters a zero. Can anybody help with this box, I am
novice use of excel so a step by step instructiion will be greatly
appreciated. Or you can refer me to a website or tutorial. Thanks

 

[Daniel.C]

Paste the following code in the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
' macro exits if address <> B1
If Target.Address <> "$B$1" Then Exit Sub
Dim Number
' sets "number" to a non numeric value
Number = ""
'if B1 =yes, whatever the characters case
If LCase(Target) = "yes" Then
' loop until answer is correct
Do Until IsNumeric(Number) = True And Number >= 0 And Number < 101
Number = InputBox("Enter number 1-100")
' cell A1 gets the number
[A1] = Number
Loop
ElseIf LCase(Target) = "no" Then
[A1] = 0
Else
MsgBox "Cell B1 value is neither ""Yes"" nor ""No"""
End If
End Sub