Dependent data validation question

**MGT2000** · 01-06-2009, 12:00 PM

Good morning,

I have a sheet with 2 drop down lists using data validation, the second list will populate based on the value chosen in the first. My question is, when changing the independent list, the value in the dependent list remains the same. Is there any way to have the dependent value clear upon choosing a new value in the independent list?

Thanks in advance,
Chris

**ChemistB** · 01-06-2009, 01:10 PM

You'll need to use Visual Basic for that. Here's one solution.
Right click on the sheet tab and choose "View Code"
Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("B2").ClearContents
End If

End Sub

This assumes your initial list is in A2 with your dependent list in B2. Does this work for you?

**MGT2000** · 01-06-2009, 02:12 PM

Thanks for the quick reply. The only problem I'm having is that my dependent list is part of a merged cell and I'm getting a run time error 1004 cannot change part of a merged cell. Anyway around this that you know of?

Thanks,
Chris

**ChemistB** · 01-06-2009, 03:17 PM

First thought is "Don't used merged cells." (You'll see that again and again on these forums)

Can you change it? Is it merged rows or merged columns? With Columns, you can use Cell alignment >Center across selection instead of merging. If you can't unmerge them, what cells do you have merged and I'll try to rewrite the code.

**MGT2000** · 01-06-2009, 05:28 PM

Sorry about the merged cells, I use them instead of changing column widths. I am merging across columns, columns A-C to be exact.

Thanks,
Chris

**Leith Ross** · 01-06-2009, 07:39 PM

Hello MGT2000,

It is only problem when you don't know how to deal with it. Here is a code example that checks if the cell is part of a merged group of cells and clears the merged range or the single cell.

Sub ClearCell()

  Dim Rng As Range
  
    Set Rng = Range("D2")
    
      If Rng.MergeCells = True Then
        Rng.MergeArea.ClearContents
      Else
        Rng.ClearContents
      End If
          
End Sub

Sicnerely,
Leith Ross

Dependent data validation question

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Dependent data validation question

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions