Activate Code on Change to one of two Dropdown Data Validation objects

pliskers

Active Member
Joined
Sep 26, 2002
Messages
427
Office Version
  1. 2016
Platform
  1. Windows
I have two data validation dropdown objects that are interdependent (Region and Market). They are linked to a dynamic list on a separate tab, so that when the Region is selected in Dropdown A, the list of available Markets in dropdown B displays only the correct options.

However, the way it currently works, if a selection has previously been made of a Region and Market, when the Region dropdown is changed, the Market selection remains unchanged, which presents a mismatch of Region-Market.

I would like to attach some code to the sheet so that when the Region dropdown is changed, the previously selected Market dropdown item is cleared out and replaced by "", which is included in my data validation list range.

Hope this is clear, and any help would be appreciated. I assume it's some type of Worksheet Change Event code, but am not clear on the most efficient way to code it.

Thank you!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,067
Office Version
  1. 365
Platform
  1. Windows
What cells are the DV in?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,067
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A1" Then
      Range("B1").Value = ""
   End If
End Sub
 

pliskers

Active Member
Joined
Sep 26, 2002
Messages
427
Office Version
  1. 2016
Platform
  1. Windows
Worked like a charm! Thanks again.
Ok, how about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A1" Then
      Range("B1").Value = ""
   End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,067
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,042
Messages
5,767,793
Members
425,435
Latest member
cmardaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top