View Full Version : Solved: Triggering Event on Cell Address Change
ajrob
08-23-2006, 12:26 PM
Background:
Below is a short procedure I wrote to hide/unhide sheets based on the status of a cell named "prod_Dispo2". The contents of that cell are "string" format, and change to one of four dispositions based on a series of user inputs on a "Main Form" sheet. The idea is to show the sheet relevant to the disposition.
Problem:
The procedure doesn't dynamically update when the disposition in "prod_Dispo2" changes. I've tried toying with the Worksheet_SelectionChange event trigger, but so far no success.
Here's the procedure ... which works:
Sub Show_PFD()
Dim Risk_Profile As String
Risk_Profile = Sheets("Main Form").Range("prod_Dispo2")
If Risk_Profile = "High Risk" Then
Sheets("Site PFD - High").Visible = True
Sheets("Site PFD - Med LD").Visible = False
Sheets("Site PFD - Med").Visible = False
Sheets("Site PFD - Low").Visible = False
ElseIf Risk_Profile = "Medium Risk - Long Duration" Then
Sheets("Site PFD - High").Visible = False
Sheets("Site PFD - Med LD").Visible = True
Sheets("Site PFD - Med").Visible = False
Sheets("Site PFD - Low").Visible = False
ElseIf Risk_Profile = "Medium Risk" Then
Sheets("Site PFD - High").Visible = False
Sheets("Site PFD - Med LD").Visible = False
Sheets("Site PFD - Med").Visible = True
Sheets("Site PFD - Low").Visible = False
ElseIf Risk_Profile = "Low Risk" Then
Sheets("Site PFD - High").Visible = False
Sheets("Site PFD - Med LD").Visible = False
Sheets("Site PFD - Med").Visible = False
Sheets("Site PFD - Low").Visible = True
Else
Sheets("Site PFD - High").Visible = False
Sheets("Site PFD - Med LD").Visible = False
Sheets("Site PFD - Med").Visible = False
Sheets("Site PFD - Low").Visible = False
End If
End Sub
Ken Puls
08-23-2006, 01:32 PM
Hi there,
I've edited your post to use our VBA tags. It makes the code a little more readable.
Okay, so as to your question, the Worksheet's Selection_Change event will fire every time you move to a new cell. Every time. Are you sure you want that?
If you're after only updating when the value in the "prod_dispo2" range's value changes, you want to use a Worksheet_Change event. This needs to go in the code module for that specific worksheet, not a generic code module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Worksheets("Main Form").Range("prod_Dispo2")) Is Nothing Then
Call Show_PFD
End If
End Sub
HTH,
mdmackillop
08-23-2006, 01:49 PM
Hi Ajrob
Welcome to VBAX
You need to use the Worksheet_Change event.
Regards
MD
ajrob
08-23-2006, 03:04 PM
MD-
First, thanks so much for your recommendations.
I updated my code based on your recommendations ... still doesn't work. But, I think I've isolated the problem...
If I manually change the cell named "prod_Dispo2", then the macro works like a champ. In my chase though, this cell is a calculated response. I seem to recall that Worksheet_Change only works with a user-initiated action. Any ideas?
ajrob
08-23-2006, 03:06 PM
Ken-
I'm new to this post, so I hope I haven't replied twice. Thanks so much for your recommendations.
I updated my code based on your recommendations ... still doesn't work. But, I think I've isolated the problem...
If I manually change the cell named "prod_Dispo2", then the macro works like a champ. In my chase though, this cell is a calculated response. I seem to recall that Worksheet_Change only works with a user-initiated action. Any ideas?
mdmackillop
08-23-2006, 03:16 PM
Hi Ajrob,
I've had this bit code lying around for ages, but never found a use for it before!
The code has to be posted into the WorkSheet module
Option Explicit
Option Compare Text
'Create variable to hold values
Dim Monitored
Private Sub Worksheet_Activate()
Monitored = Range("prod_Dispo2").Value 'Read in value prior to any changes
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Check target to determine if macro is triggered
If Intersect(Target, Range("prod_Dispo2")) Is Nothing Then Exit Sub
'Prevent looping of code due to worksheet changes
Application.EnableEvents = False
'Compare monitored cell with initial value
If Range("prod_Dispo2").Value <> Monitored Then
'Do things as a result of a change
Show_PFD
'Reset Variable with new monitored value
Monitored = Range("prod_Dispo2").Value
End If
'Reset events
Application.EnableEvents = True
End Sub
Ken Puls
08-23-2006, 03:46 PM
Hi ajrob,
No worries. I understood from your reply to Malcolm's that my idea wouldn't work.
Now... Malcolm's new code though... that is sweet! Nice work, Malcolm. Only one minor adjustment needed. ;)
Option Explicit
Option Compare Text
'Create variable to hold values
Dim Monitored
Private Sub Worksheet_Activate()
Monitored = Range("prod_Dispo2").Value 'Read in value prior to any changes
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Prevent looping of code due to worksheet changes
Application.EnableEvents = False
'Compare monitored cell with initial value
If Range("prod_Dispo2").Value <> Monitored Then
'Do things as a result of a change
Show_PFD
'Reset Variable with new monitored value
Monitored = Range("prod_Dispo2").Value
End If
'Reset events
Application.EnableEvents = True
End Sub
(Axed the exit code or it just exits all the time)
mdmackillop
08-23-2006, 04:02 PM
Thanks Ken,
I didn't have the Exit problem, but the line is not essential in this case.
Zack Barresse
08-23-2006, 04:04 PM
Well, I'm just posting on your sub routine, it can be shortened a little bit...
Sub Show_PFD()
Sheets("Site PFD - High").Visible = False
Sheets("Site PFD - Med LD").Visible = False
Sheets("Site PFD - Med").Visible = False
Sheets("Site PFD - Low").Visible = False
Select Case Sheets("Main Form").Range("prod_Dispo2").Value
Case "High Risk": Sheets("Site PFD - High").Visible = True
Case "Medium Risk - Long Duration": Sheets("Site PFD - Med LD").Visible = True
Case "Medium Risk": Sheets("Site PFD - Med").Visible = True
Case "Low Risk": Sheets("Site PFD - Low").Visible = True
End Select
End Sub
HTH
mdmackillop
08-23-2006, 04:13 PM
BTW, You can condense your code a bit with a Select statement
Sub Show_PFD()
Dim Risk_Profile As String
Risk_Profile = Sheets("Main Form").Range("prod_Dispo2")
Sheets("Site PFD - High").Visible = False
Sheets("Site PFD - Med LD").Visible = False
Sheets("Site PFD - Med").Visible = False
Sheets("Site PFD - Low").Visible = False
Select Case Risk_Profile
Case "High Risk"
Sheets("Site PFD - High").Visible = True
Case "Medium Risk - Long Duration"
Sheets("Site PFD - Med LD").Visible = True
Case "Medium Risk"
Sheets("Site PFD - Med").Visible = True
Case "Low Risk"
Sheets("Site PFD - Low").Visible = True
End Select
End Sub
mdmackillop
08-23-2006, 04:14 PM
Beat me to it Zack!
Zack Barresse
08-23-2006, 04:15 PM
ROFL! I'll say it ... great minds think alike! LOL!
Ken Puls
08-23-2006, 04:23 PM
Thanks Ken,
I didn't have the Exit problem, but the line is not essential in this case.
Really? I was finding that unless I was modifying the "prod_Dispo2" range, it would exit. Once I got rid of that, it was calculating beautifully.
:)
mdmackillop
08-23-2006, 04:41 PM
:banghead: Got it at last Ken! You're absolutely right.
ajrob
08-23-2006, 05:54 PM
Thanks to all who contributed ... this is working beautifully!!
Adam
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.