Announcement

Collapse
No announcement yet.

VBA Clear Range on Cell Change?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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

    Comment


    • #3
      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.

      Comment


      • #4
        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

        Comment


        • #5
          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.

          Comment


          • #6
            Toad,

            Jindon's code should work...

            What code are you using & where is it going wrong ?
            Kind Regards, Will Riley

            LinkedIn: Will Riley

            Comment


            • #7
              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.

              Comment


              • #8
                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

                Comment


                • #9
                  Sorry guys, it's still not working.

                  Tried the enable events code too, but to no avail.

                  Strange!
                  Paul.

                  Comment


                  • #10
                    can you attach the file... I'll take a look from home later...
                    Kind Regards, Will Riley

                    LinkedIn: Will Riley

                    Comment


                    • #11
                      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.

                      Comment


                      • #12
                        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

                        Comment


                        • #13
                          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.

                          Comment


                          • #14
                            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

                            Comment


                            • #15
                              Thanks again old BOY - you're a star . . . just like all the other gifted folks on here.

                              Enjoy your ZZZzzzs!
                              Paul.

                              Comment

                              Working...
                              X