Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing cells based upon criteria?
I have a worksheet that I would like to have specific cells cleared to
nothing if the value in one specific cell changes. So if I change the one cell at any time and point then the other specific cells clear out of anything. Can anyone tell me if this is possible and how I would go about it. Currently I have a macro set up that clears the cells if you "CTRL + c" but I would like it to do it automatically. Thanks for your time. Shauna |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing cells based upon criteria?
You could use a worksheet_change event that would look at that key cell and
process the clearing when you make a change to that cell. But since you have a macro already available, you could add a button from the Forms toolbar and assign the same macro to that button. Then just click the button (instead of ctrl-c) to run the macro to clear the range. shaunap wrote: I have a worksheet that I would like to have specific cells cleared to nothing if the value in one specific cell changes. So if I change the one cell at any time and point then the other specific cells clear out of anything. Can anyone tell me if this is possible and how I would go about it. Currently I have a macro set up that clears the cells if you "CTRL + c" but I would like it to do it automatically. Thanks for your time. Shauna -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing cells based upon criteria?
Use a Worksheet_Change macro.
Right-click on the sheet tab and select "View Code." In the drop-down at the top of the right-side work area, where it says "(General)" select the drop-down list entry called "Worksheet" Then in the drop-down to the right of that select the entry called "Change" Now just write your macro. Here is an example which clears the range C3:C4 if A1's value is 2008: Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value = "2008" Then Range("C3:C4").Value = "" End If End Sub -- Please remember to indicate when the post is answered so others can benefit from it later. "shaunap" wrote: I have a worksheet that I would like to have specific cells cleared to nothing if the value in one specific cell changes. So if I change the one cell at any time and point then the other specific cells clear out of anything. Can anyone tell me if this is possible and how I would go about it. Currently I have a macro set up that clears the cells if you "CTRL + c" but I would like it to do it automatically. Thanks for your time. Shauna |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing cells based upon criteria?
First of all, select the cells to clear and InsertNameDefine
Give the range a name of TheRange. Add this event code to the sheet module. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo stoppit Application.EnableEvents = False If Target.Address = "$A$2" And Target.Value < "" Then 'adjust the $A$2 Me.Range("TheRange").ClearContents End If stoppit: Application.EnableEvents = True End Sub Question: How do the cleared cells get re-populated? Otherwise not much point in doing it automatically each time A2 gets changed. Gord Dibben MS Excel MVP On Tue, 30 Dec 2008 08:45:00 -0800, shaunap wrote: I have a worksheet that I would like to have specific cells cleared to nothing if the value in one specific cell changes. So if I change the one cell at any time and point then the other specific cells clear out of anything. Can anyone tell me if this is possible and how I would go about it. Currently I have a macro set up that clears the cells if you "CTRL + c" but I would like it to do it automatically. Thanks for your time. Shauna |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing cells based upon criteria?
Hi,
You might use a formula, but then the cell will display as blank but it will actually have a formula in it. For example =IF(A1=10,"",A1) This formula displays as blank when A1=10 otherwise it displays the value in A1. If this is not good enough, you can add a Worksheet_Change event macro Here is some sample code: Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("A1")) If Not isect Is Nothing Then 'Your code here End If End Sub This code goes into the Sheet1 object. (or the equivalent object in your file.) The range A1 is the cell whose change triggers the macro. If this helps, please click the Yes button. Cheers, Shane Devenshire "shaunap" wrote in message ... I have a worksheet that I would like to have specific cells cleared to nothing if the value in one specific cell changes. So if I change the one cell at any time and point then the other specific cells clear out of anything. Can anyone tell me if this is possible and how I would go about it. Currently I have a macro set up that clears the cells if you "CTRL + c" but I would like it to do it automatically. Thanks for your time. Shauna |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing cells based upon criteria?
Thank you all for your responses. I'm probably being thick headed here but
I'm having trouble wrapping my brain around the Worksheet Change function. I did play with it a bit before posting but don't understand it enough. Cell I4 always has a string in it. If no string it buggers up the query and resulting calculations. "TheRange" and cell I4 are user input. I4 is the main input. "TheRange" is additional query pulls. Not every query pull requires additional pulls therefore to eliminate user forgetfullness it will be better to clear "TheRange" everytime the main pull (I4) is changed. I4 is possible to be different everytime the sheet is reopened and will never be the same string, therefore I need the Worksheet Change to clear "TheRange" regardless of what was changed in I4 simply that there was a change to the string. Hopefully this makes more sense to all who are/can help. Thank you! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing cells based upon criteria?
Thank you all for your responses. I'm probably being thick headed here but I'm having trouble wrapping my brain around the Worksheet Change function. I did play with it a bit before posting but don't understand it enough. Cell I4 always has a string in it. If no string it buggers up the query and resulting calculations. "TheRange" and cell I4 are user input. I4 is the main input. "TheRange" is additional query pulls. Not every query pull requires additional pulls therefore to eliminate user forgetfullness it will be better to clear "TheRange" everytime the main pull (I4) is changed. I4 is possible to be different everytime the sheet is reopened and will never be the same string, therefore I need the Worksheet Change to clear "TheRange" regardless of what was changed in I4 simply that there was a change to the string. Hopefully this makes more sense to all who are/can help. Thank you! "Gord Dibben" wrote: First of all, select the cells to clear and InsertNameDefine Give the range a name of TheRange. Add this event code to the sheet module. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo stoppit Application.EnableEvents = False If Target.Address = "$A$2" And Target.Value < "" Then 'adjust the $A$2 Me.Range("TheRange").ClearContents End If stoppit: Application.EnableEvents = True End Sub Question: How do the cleared cells get re-populated? Otherwise not much point in doing it automatically each time A2 gets changed. Gord Dibben MS Excel MVP On Tue, 30 Dec 2008 08:45:00 -0800, shaunap wrote: I have a worksheet that I would like to have specific cells cleared to nothing if the value in one specific cell changes. So if I change the one cell at any time and point then the other specific cells clear out of anything. Can anyone tell me if this is possible and how I would go about it. Currently I have a macro set up that clears the cells if you "CTRL + c" but I would like it to do it automatically. Thanks for your time. Shauna |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting based on criteria from two cells??? | Excel Worksheet Functions | |||
Selection of Cells based on a criteria | Excel Discussion (Misc queries) | |||
Fill cells with color based on criteria in two cells | Excel Worksheet Functions | |||
SUM cells together based on 2 criteria | Excel Worksheet Functions | |||
copying cells based on criteria | Excel Worksheet Functions |