VBA: Dependent Drop Down List (Clear cell)

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
Greetings,

I need help clearing cell (B4) when the value in B2 Changes.

:confused: I'm Stumped. . .
 

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
855
Hi BIGTONE559,

Maybe this ?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B2") Then Range("B4") = ""
End Sub
 

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
Hey Sahak,

Thanks for the response. I actually used the following code:

Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Range("B2:B3")) Is Nothing Then
    Range("B4:H4").ClearContents
End If
End Sub
The only problem with this code is that when the cell itself is select (i'm assuming because of Target) it clears the info in B4. This does work for me however, i was attempting to have B4:H4 cleared once the value in b2 changed.

e.g. the way it is now when activecell equals B2 or B3 it Clears contents.

I was looking for when a different selection was made from the drop down list in B2. Once the value was selected it would clear the contents in B4
 

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
855
Hi BIGTONE559,

If in
when a different selection was made from the drop down list
you mean the list from Data Validation, then we will declare global variable “Before", & what you need will be done if you " Deactivet " & then " Activate“ the sheet where you make your selections.
Code:
Option Explicit
Public Before
Private Sub Worksheet_Activate()
    Before = Range("B2").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B2") And Target <> Before Then Range("B4") = ""
End Sub
I have tried, it works
 

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
Sahak,

Didn't work for me buddy. Upon entering the code it had no results. B4 contents don't clear at all. I don't know if this is a factor but in B2 it is a drop down list as well. once the drop down value is changed B4 should have it's contents cleared.
 

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
855
If you like give me your email & will send you sample file.
 

sidetracked350

New Member
Joined
Nov 28, 2012
Messages
6
This works for what I'm doing so far except I need to do it in each row is there a way of making it work so that if c58 is changed e58 defaults, and if c59 is changed e59 defaults...etc...etc
 

Forum statistics

Threads
1,086,243
Messages
5,388,668
Members
402,134
Latest member
McKnze21

Some videos you may like

This Week's Hot Topics

Top