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!! |
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!! |
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!! |
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. |
All times are GMT +1. The time now is 09:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com