Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 7 1 2 3 5 ... LastLast
Results 1 to 10 of 63

Thread: VBA Clear Range on Cell Change?

  1. #1
    Join Date
    2nd April 2003
    Location
    Nottingham, England.
    Posts
    146

    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. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,412
    Hi,

    assuming cell A1 is the client and cell B1 is to be changed
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    2nd April 2003
    Location
    Nottingham, England.
    Posts
    146
    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.

  4. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,412
    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
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd April 2003
    Location
    Nottingham, England.
    Posts
    146
    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.

  6. #6
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677
    Toad,

    Jindon's code should work...

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

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  7. #7
    Join Date
    2nd April 2003
    Location
    Nottingham, England.
    Posts
    146
    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.

  8. #8
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,412
    Hi Toad,

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

    try

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    2nd April 2003
    Location
    Nottingham, England.
    Posts
    146
    Sorry guys, it's still not working.

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

    Strange!
    Paul.

  10. #10
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677
    can you attach the file... I'll take a look from home later...
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: May 8th, 2009, 23:01
  2. Clear Range To Right Of Active Cell Down
    By Randal in forum EXCEL HELP
    Replies: 1
    Last Post: April 17th, 2009, 12:15
  3. Clear Cell In Variable Dynamic Range
    By marin820 in forum EXCEL HELP
    Replies: 2
    Last Post: February 21st, 2008, 02:55
  4. Change Chart Range On Cell Change
    By MikeBrough in forum EXCEL HELP
    Replies: 1
    Last Post: February 19th, 2007, 21:46
  5. Automatically Clear Range Based on Cell Criteria
    By Domski in forum EXCEL HELP
    Replies: 10
    Last Post: January 10th, 2007, 17:11

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno