Need to clear contents of a cell where values are from dropdown menu

[Lo Baan]

I have two cells, A1 and B1, where values for each are from dropdown menus. Any way to clear the contents of B1 automatically if I delete the value in A1? When I try to use a formula to say if A1 is blank, then B1 is blank, but I get the error that what I entered is not part of the drop down.

Any help is appreciated.
Thanks, Lo Baan

 

[Nebu]

Hi:
You can do this using vba
something like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Range("B" & ActiveCell.Row).ClearContents
End If
End Sub

Thanks

 

[Lo Baan]

Thanks Nebu, however, I am an Excel novice and do not know where to put this VBA code in my worksheet. Can you provide more guidance?
Thanks, Lo Baan

 

[Nebu]

Hi:

Right click on the tab where you have drop downs , select the view code option and paste the code there.

Thanks

 

[NARAYANK991]

Hi Nebu ,

Shouldn't this be implemented using the Worksheet_Change event procedure ?

This will be triggered when the data in A1 is deleted :

Private Sub Worksheet_Change(ByVal Target As Range)
            If Application.Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
           
            If Target = vbNullString Then Target.Offset(, 1).ClearContents
End Sub

Narayan

 

[Nebu]

@NARAYANK991

Yes, you are right I missed that.

Thanks

 

[Lo Baan]

Thanks Nebu and Narayan. I have a test file that I am uploading for your advise. I put in the code and now get a VBA error - Run-time error '1004': Cannot change part of a merged cell. My apologies as the cell that I am referring to is really merged cells B6,B7,C6,C7. When these merged cells are deleted, I am trying to clear contents of merged cell D6,D7.

I need this to work on all of the similar cells in the Employee Time In tab.

Hope this is clear and sorry for the confusion.
Thanks, Lo Baan

 

[NARAYANK991]

Hi ,

See this file.

However , I would like to advise you to do away with all those merged cells. Merged cells are almost never necessary , since Excel allows you to change column widths , row heights , wrap text ; all of these either singly or in combination can give your worksheets a neatly formatted look.

I have copied the worksheet , and removed the merged cells in the copied sheet. See if you can use this tab.

Narayan

 

[Lo Baan]

Thanks Narayan. I will try this and include the dropdowns to see how it works and will let you know...

Lo Baan

 

[Lo Baan]

Thanks Narayan, your code is working beautifully. I'm taking your advise on not using merged cells. The reason I had them to begin with was that the user asked to have a cell for time in and time out for each employee. Later the user changed their mind.
Thank you for your help as this has solved my problem.
Much appreciated, Lo Baan