-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Launching macro based on value of a cell
- Thread starter CHawk
- Start date
Justinlabenne
Well-known Member
- Joined
- Jun 12, 2004
- Messages
- 957
Example:
'Right-Click on a Sheet Tab > View Code
'This is called a "Worksheet_Code module"
'Paste this code into the open white area
'Press ALT + Q to close the Visual Basic Editor
'Right-Click on a Sheet Tab > View Code
'This is called a "Worksheet_Code module"
'Paste this code into the open white area
'Press ALT + Q to close the Visual Basic Editor
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub 'Assumes Column A any cell
If Target(1, 1).Value = "True" Then
MsgBox Target.Value 'Replace with your code here
End If
If Target(1, 1).Value = "False" Then
MsgBox Target.Value 'Replace with your code here
End If
End Sub
Upvote
0
Thank you very much for your quick reply. I am very new to this and have 3 questions for you.
1) The cell that is updating True or False is Cell I11 I am not sure from what you sent me exactly where I enter I11 as the cell.
2) The cell may update True, True, True then False, Then True Again. I only want the Macro to launch when it changes from False to True not when it changes from True to True. Will the code you provided do this?
3) The macro that I want do run is called LogES. Where in the code to I put this macro name?
Sorry for such basic questions and thanks for your help.
1) The cell that is updating True or False is Cell I11 I am not sure from what you sent me exactly where I enter I11 as the cell.
2) The cell may update True, True, True then False, Then True Again. I only want the Macro to launch when it changes from False to True not when it changes from True to True. Will the code you provided do this?
3) The macro that I want do run is called LogES. Where in the code to I put this macro name?
Sorry for such basic questions and thanks for your help.
Upvote
0
Justinlabenne
Well-known Member
- Joined
- Jun 12, 2004
- Messages
- 957
This will ask you when the cell value has been changed to true, if you want to run the "LogEs" code
'Right-Click on a Sheet Tab > View Code
'This is called a "Worksheet_Code module"
'Paste this code into the open white area
'Press ALT + Q to close the Visual Basic Editor
'Right-Click on a Sheet Tab > View Code
'This is called a "Worksheet_Code module"
'Paste this code into the open white area
'Press ALT + Q to close the Visual Basic Editor
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans As String
If Range("I11").Value = "True" Then
ans = MsgBox("Run the LogEs code now?", vbYesNo + vbQuestion, "Title")
If ans = vbNo Then Exit Sub
Call LogEs
End If
End Sub
Upvote
0
Justinlabenne
Well-known Member
- Joined
- Jun 12, 2004
- Messages
- 957
This should take care of it, the reason I put the mesage box in is that it will run if it changes from TRUE to TRUE, not sure how to prevent it if it already contains TRUE then gets changed to TRUE again, because to run automatically, it runs offa Worksheet_Change event, wihich means it will run when the cell I11 value changes, even if it changes to the same value it already contains, I have to see what I can come up with to prevent this but for now here it is without the msgbox:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("I11").Value = "True" Then
Call LogEs
End If
End Sub
Upvote
0
Thanks Justin. With the changed code -- and i guess it was happening before as well -- it runs the macro if I11 is True and any change is made anywhere on the entire spreadsheet . Since many fields are updated continually via DDE this won't work as it will bog down my computer. Hope you are successful coming up with a way to run the macro only if changes from False to True. Thanks for all your help, Cathy
Upvote
0
Justinlabenne
Well-known Member
- Joined
- Jun 12, 2004
- Messages
- 957
Not sure why I forgot that part: try this,
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("I11") Then Exit Sub
If Range("I11").Value = "True" Then
Call LOGES
End If
End Sub
Upvote
0
just_jon
Legend
- Joined
- Sep 3, 2002
- Messages
- 10,473
Couldn't you have a static variable, which would be set to the cell value on workbook open, then in the worksheet change macro you would 1st verify that the changed cell was the cell of interest, then 2nd verify that the current value is <> the static value.
If both above conditions are true, then is the new value = True? If so, then assign the static vriable as True, and run code; otherwise, just change the static value and exit.
If both above conditions are true, then is the new value = True? If so, then assign the static vriable as True, and run code; otherwise, just change the static value and exit.
Upvote
0
Justin, For some reason that code change caused the macro not to run at all. Do I need another end if statement somewhere?
Also, Just_jon, as you can probably tell from my posts I am not much of a techie. I am not sure how I would code what you said. Also, The worksheet stays open most of the day, would your solution work if the cell value changes all day long but the worksheet is only closed at the end of the day?
Thanks, Cathy
Also, Just_jon, as you can probably tell from my posts I am not much of a techie. I am not sure how I would code what you said. Also, The worksheet stays open most of the day, would your solution work if the cell value changes all day long but the worksheet is only closed at the end of the day?
Thanks, Cathy
Upvote
0
