Forum home » Delegate support and help forum » Microsoft Excel Training and help » Data validation macro
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Data validation macro
Resolved · High Priority · Version 2007
Reply Wed 19 Oct 2011, 15:31Delegate Sachin said...
Sachin has attended:
Excel Intermediate course
Excel VBA Intro Intermediate course
Data validation macro
I have two data validation drop downs. The first is for a user to select a country and the second is for the user to select a hospital within that country.
I have already set these up to function correctly i.e.data lists and data validation.
The problem is; if after selecting a hospital from the 2nd drop down menu, I wish to change my mind and select another country, the original hospital I had chosen still remains in the 2nd drop down menu.
I want a macro to say if I change the country, the hospital field will remain blank, until I chose another hospital from the drop down list.
Does it have something to do with worksheet_change (target=range)?
For upcoming training course dates see: Pricing & availability
RE: Data validation macro
Hello Sachin,
You don't specifiy if your using just VBA Userforms, Or ComboBox form Fields in the Worksheet, as they are both different command syntax.
Please provide more info about your setup.
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
Reply Mon 24 Oct 2011, 09:30Delegate Sachin said...
RE: Data validation macro
Hi Mark,
The setup is not userforms or combo boxes. The drop down boxes were created using data validation from the data ribbon.
Does this help?
RE: Data validation macro
Hello Sachin,
Not sure how you are using data validation, but try this code on your sheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$H$2" Then
Range("J2").ClearContents
End If
End Sub
Note: The H2 is where your main drop list is.. the J2 is the location of the referred to drop list, change these refs accordingly.
This code goes in the code window of VB Editor, quick way to get to correct sheet code, is right click the sheet tab and select View Code, then paste the above code in the code sheet, on right of screen!
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
Mon 31 Oct 2011: Automatically marked as resolved.
|