VBA Clear Range on Cell Change?

    Hello All,


    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.


    Paul.

    Paul.

    Hi,


    assuming cell A1 is the client and cell B1 is to be changed

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    If .Address = "$A$1" Then
        Range("b1").Value = ""
    End If
End With
End Sub


    hope it helps


    jindon

    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.


    Paul.

    Paul.

    Hi,


    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

    Code
    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


    jindon

    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?

    Paul.

    Hello Will.


    I have entered this code into the worksheet code window (right click > VIEW CODE) . . .


    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


    Have I misinterpretted what I read about change procedures ignoring Data Validation changes?

    Paul.

    Hi Toad,


    If nothing happen when you change value in cell B2, then possibly event might be disabled.


    try


    Code
    sub a()
application.enableevents=true
end sub


    paste the code onto thisworkbook module and run once.


    then test to change cell B2


    good luck
    jindon

    Will,


    Thanks for the kind offer.


    I've had problems when attaching files in the past but was just about to try with this one when I read down below that the max size for attachments is 45KB. Unfortunately the file I wish to attach is larger than that.


    Paul.

    Paul.

    Hi Taod


    Would you try this one?


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 2 And .Row = 1 Then
            Range("a12:h500").Value = ""
        End If
    End With
End Sub


    I hope it will work for you


    jindon

    Eureka!


    Jindon,


    Who's a clever boy?! OOPS, forgive me (could be girl?)


    I think you've cracked it. I'll test it fully in a little while and let you know for sure.


    Would you mind explaining your thought process on this one?


    Thanks,


    Paul.

    Paul.

    Hi Paul,


    Sorry, I'm just an old boy.
    Glad to hear now the code is working for you.


    I'm not sure the readon why the former code didn't work and it is not working here, too when I check it again.
    I was working on Excel 2000 when I made the first code and was working, but not on 2002.
    I'd like someone explain this matter, please!


    The second one refering Column and Row to specify cell B1 instead of address.
    if the target cell is in column2, col.B, and row1, first row. then do the action...


    I will go back to sleep
    See you tommorow


    jindon

    I think I know what the problem was with getting the code to run when the cell was changed . . .


    When we encountered the problem I was working on a PC running Excel 97 - that's when it wouldn't work. Later, when I did finally get it to run I had switched to a machine running Excel XP.


    I'm now working in 97 with the file that was working last night on XP and I'm having the same problem . . . the Worksheet_Change code will not execute.


    Is it not possible to get it to run in 97?


    Does anyone know if / why this would cause the problem?


    Thanks,


    Paul.

    Paul.

    Hi Paul,


    Can you just check with '97


    1) right click on any sheet tub to get into VB Editor
    2) select "worksheet" from the lefthand dropdown list on the top.
    3) check if there is "Change" in the right-hand dropdown list


    could be different.


    rgds,
    jindon

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login