Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a "ChangeCell" event?
I have a pulldown list of values. I need to run a macro whenever the value
of the cell with the pulldown changes. For example, If the cell is changed from "Master" to "Balance", I need a macro to run to unhide certain sheets and hide others. The macro (vba code) is not the problem - it's just having excel execute the code if the cell value changes. Any help in pointing me in the right direction is appreciated!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a "ChangeCell" event?
Use a Change Event Macro. Say we are interested in detecting changes to cell
B9: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set b = Range("B9") If Intersect(t, b) Is Nothing Then Exit Sub MsgBox ("B9 has changed") End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200840 "Rich" wrote: I have a pulldown list of values. I need to run a macro whenever the value of the cell with the pulldown changes. For example, If the cell is changed from "Master" to "Balance", I need a macro to run to unhide certain sheets and hide others. The macro (vba code) is not the problem - it's just having excel execute the code if the cell value changes. Any help in pointing me in the right direction is appreciated!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a "ChangeCell" event?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myintersect As Range Set myintersect = Application.Intersect(Target, Range("A1")) If Not myintersect Is Nothing Then If UCase(Target) = "MASTER" Then MsgBox "Call the M subroutine" ElseIf UCase(Target) = "BALANCE" Then MsgBox "Call the B subroutine" End If End If End Sub Note that this must go onto a worksheet module - right click the sheet's tab and select View Code. Change reference to A1 to suit your needs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rich" wrote in message ... I have a pulldown list of values. I need to run a macro whenever the value of the cell with the pulldown changes. For example, If the cell is changed from "Master" to "Balance", I need a macro to run to unhide certain sheets and hide others. The macro (vba code) is not the problem - it's just having excel execute the code if the cell value changes. Any help in pointing me in the right direction is appreciated!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a "ChangeCell" event?
Thanks, Gary.
I had actually just stumbled onto something that works, too. Here is what I did: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$25" _ Then ***run my code to hide and unhide sheets*** End if end sub Cell B25 is the cell that I want to monitor for changes. Changes to any other cell will not run my code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |