If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need an input box when user selects a value in a drop down box
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 |
#2
|
|||
|
|||
Need an input box when user selects a value in a drop down box
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: 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 |
#3
|
|||
|
|||
Need an input box when user selects a value in a drop down box
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 On Fri, 18 May 2007 08:42:00 -0700, Susan wrote: 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 |
#4
|
|||
|
|||
Need an input box when user selects a value in a drop down box
Thanks!!! this worked like a charm... it was exactly what I was looking for!!!!
"Gord Dibben" wrote: 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 On Fri, 18 May 2007 08:42:00 -0700, Susan wrote: 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 |
#5
|
|||
|
|||
Need an input box when user selects a value in a drop down box
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? |
Thread Tools | |
Display Modes | |
|
|