assuming cell A1 is the client and cell B1 is to be changed
hope it helpsVB:Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Address = "$A$1" Then Range("b1").Value = "" End If End With End Sub
Unfortunately I seem to have caught the VBA bug and now want it to do 'everything' for me!
Here's the scenario . . .
A database contains lots of client records. A display sheet contains a drop-down list courtesy of Data Validation. When a client is selected from the drop-down list the user then clicks a button which executes some code to drag all the details for that client from the database into the display sheet. It's working quite nicely (thanks to the folks who helped me with the code) but what I would like to happen is for the records in the display sheet to be cleared when the user changes the client in the drop-down cell, ready for the next lot of data when they click the button for the new search.
I've read quite a lot of old threads on here about WorkSheet_Change procedures and it would seem that Data Validation changes to a cell are not recognised by this kind of procedure. Is this correct? If so, can anyone offer any suggestions as to how I could work around this?
Summing up . . . I would like the search records to be cleared each time the client name in the Data Validation cell is changed.
Can it be done? Silly question that is I know because it seems that you people can do almost anything with VBA!
Help and advice appreciated as usual.
Impressive response time again - thanks.
Unfortunately the code supplied isn't doing what I want it to do. It's probably something I've done wrong but . . .
I enter the code you provided in the worksheet itself don't I? i.e. Right click the tab and VIEW CODE?
The Data Validation drop-down list is cell B1 in my worksheet and the area I want to be cleared whenever B1 is changed is A12:H500. I changed these parameters in the code provided but the cells are not being cleared. If I select a client in B1 and click the button I get the records for that client. If I then change the name in B1 I want the previous search to be cleared before the button is clicked for the new search.
As I said, it's probably something I'm doing wrong and I apologise if it is, but I'm really very new to VBA.
Thanks again for the prompt response.
just let me ask you what's in the range A12:H500?
formula? anything depend on other cell or cell reference?
would you like to try
jindonVB:Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Address = "$b$1" Then Range("a12:h500").Value = "" End If End With End Sub
The cells in range A12:A500 are devoid of any content until the button is clicked to drag the data from the database.
There is some conditional formatting applied to the cells in the range. Would that affect what I'm tring to achieve?
I have entered this code into the worksheet code window (right click > VIEW CODE) . . .
Private Sub Worksheet_Change(ByVal Target As Range)
If .Address = "$b$1" Then
Range("a12:h500").Value = ""
Have I misinterpretted what I read about change procedures ignoring Data Validation changes?
If nothing happen when you change value in cell B2, then possibly event might be disabled.
paste the code onto thisworkbook module and run once.VB:Sub a() application.enableevents=True End Sub
then test to change cell B2
Sorry guys, it's still not working.
Tried the enable events code too, but to no avail.
There are currently 1 users browsing this thread. (0 members and 1 guests)