PDA

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