Need an input box when user selects a value in a drop down box

[Susan]

Hello... I need some help... not sure how to do the following:
I have a spreadsheet that contains a drop down list in column B (note only
various rows in Column B contain the drop down list). This drop down list
contains text values example: Level1, Level2, Level3 and LevelZ

When user selects Level1, Level2, or Level3 there is a default rate value
associated to them eg.. Level1 = $10, Level2 = $20 and Level3=$30 but when
the user selects LevelZ then I need an Input Message to pop up asking user to
enter a value... this value will be stored in a cell associated to the LevelZ
for that particular row (note there can be more than 1 for any of the Levels)
but I need the Msg Box to appear every time LevelZ is selected...

So I sort of want Excel to do the following when the condition is (if B3 =
LevelZ and C3=is empty then Message Box appears.... user enters the value and
C3 now contains the value)... keeping in mind that the range could be B8 or
B20 or B55 etc...

I am a Exel basic user... but I do know that Excel is a powerful tool if one
knows how to use it... hope someone can help

 

[excelent]

Rightclick on sheet-tab and select show programcode
insert this code in window to the right
in this ex. there is a dropdown in B3,B8,B12 change if nessesery

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x
If Intersect(Target, Range("B3,B8,B12")) Is Nothing Then Exit Sub
If Target = "LevelZ" And Range("C3") = Empty Then
x = InputBox("Input value ")
Range("C3") = x
End If
End Sub


"Susan" skrev:

 

[Gord Dibben]

Susan

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B:B")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case Target.Value
Case "LevelZ"
whatval = InputBox("enter a value")
Target.Offset(0, 1).Value = whatval
End Select
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module.

Operates only on those cells which have the DV dropdowns unless you manually
type LevelZ into a blank cell, which you or your users would never
do......right?

Otherwise you could adjust the range for just those "various" cells with the DV
dropdowns.

If Intersect(Target, Me.Range("B1,B3,B6,B8,B12")) Is Nothing Then Exit Sub


Gord Dibben MS Excel MVP

 

[Susan]

Thanks!!! this worked like a charm... it was exactly what I was looking for!!!!

 

[Sara]

I tried doing this on a drop down menu which extends from Q18 to U18 but it
doesn't seem to work. What changes should I be making to the code?