• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Drop down list options

A

AUTO

New Member
Hi Folks!

I have a drop down list with four choices. If one particular choice is made then I would like a pop-up (or some other option)to ask a yes/no question which requires a response. Is this possible using data validation or conditional formatting or a combination of both? I'm even prepared to consider VBA(*gasp*)if someone will hold my hand. :)
 
what about a simple

=If(cell=Value,"Please enter Yes/No","")


and then in the cell next to the question use a Conditional Format

which has the formula =Cell<>""

and set a Background color when it is true

where cell is the cell with =If(cell=Value,"Please enter Yes/No","") in it
 
Thanks Hui!

That's pretty much what I did.

I'm now trying an "option" form control button but, I can't hide the buttons when the conditions are negative. Any thoughts?

Failing anything else I'll stick with the first option.

Thanks again!

Auto
 
Yes, You can do it with an Active X Control and some VBA


Goto the Developers Tab and insert an Active X control in your worksheet

Set it up using the properties button on the Developers tab


Add some code to the Worksheet page in VBA

Alt F11 double click on your worksheet name and paste the code in the blank window on the right

Code

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
' Change the Cell's address
' Target value and
' Combo Box names as appropriate
'
If Target.Address = "$L$4" And Target.Value = "10" Then
ActiveSheet.Shapes.Range(Array("ComboBox1")).Visible = True
Else
ActiveSheet.Shapes.Range(Array("ComboBox1")).Visible = False
End If
End Sub
[/pre]
 
Knew it Was too good to be true.

I'm getting a Runtime Error 13 Error.

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
' code provided by
'Chandoo.org(Hui)
If Target.Address = "$D$9" And Target.Value = "HSR Health Safety & Regulatory" Then
ActiveSheet.Shapes.Range(Array("OptionButton1")).Visible = True
ActiveSheet.Shapes.Range(Array("OptionButton2")).Visible = True
Else
ActiveSheet.Shapes.Range(Array("OptionButton1")).Visible = False
ActiveSheet.Shapes.Range(Array("OptionButton2")).Visible = False

End If
End Sub
[/pre]
Error in "If Target.Address" line


I know SFA about VBA so I don't know where to begin.

Suggestions welcome.
 
It works fine with me


Please check the following:

Did you copy the code to the Worksheet Object in VBA which has the Option Buttons on it?

The Option Buttons need to be Active X Option Button's not Form Control Option Buttons

Did you save your file as a Excel Macro enabled file *.xlsm, not *.xlsx?

Are you Running the latest version of Excel ie Office XP Service Pack 3 or later?
 
Problem appears to be that Auto has merged a number of cells and hence the address isn't a simple $D$9 but a more complex $D$9:$J$9


Un-merging fixed the problem, but messed up his layout a small bit
 
I am having a similar issue with basically the same set-up in a table. Pull down menu in a cell with four number options. If the user picks number 2 the a pop-up window opens. Works great until I add a row in the table. Immediately get run-time error 13, but the pop-up window still works once I dismiss the error.


Here is the code in the sheet:


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub

If Target.Value <> "2" Then Exit Sub

MsgBox "Enter cancelled check as a credit and include old and new check numbers in a comment."

End Sub


Debugging shows the third line as the issue. Is there any way to either fix this or add code to ignore the error?
 
In the future, I'd encourage you to start a new thread (maybe with a link to relevant discussion) rather than tacking on to an old thread. I almost bypassed this thread because I thought it had already been answered.

To address your question, the problem is that when you add a row, col B is getting changed, but more than 1 cell is affected, so the Target.Value <> "2" is messing things up. We can probably work with this:

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
'Check for multiple cell edits
If Target.Count > 1 Then Exit Sub
If Target.Value <> "2" Then Exit Sub
MsgBox "Enter cancelled check as a credit and include old and new check numbers in a comment."
End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top