Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
I need some vba code to make a macro run if any cell changes from blank to URGENT
I am using Excel 2003 and need some code to kick off a macro when any cell in the spreadsheet changes from blank to URGENT
Thanks
Thanks
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
For this you will need a backup sheet which will store the previous values of the cells.
Make a copy of the sheet in question and then use this event-driven macro in the worksheet module of the sheet
Make a copy of the sheet in question and then use this event-driven macro in the worksheet module of the sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Sheets("Sheet1 (2)").Range(Target.Address) = "" And Target = "URGENT" Then
MsgBox "Do it now"
Else
Sheets("Sheet1 (2)").Range(Target.Address) = Target
End If
End If
End Sub
Not sure I understand this. My sheet is changing throughout the day so a backup sheet is not and option for me. But thanks for trying. Maybe its not possible then to have a macro that will run if the cell changes?????
There is a macro event handler which can check that
Cell value changes to URGENT
But cannot check
Cell value changes from blank to URGENT
To be able to check this we would need a helper sheet which will store the previous values and then check the previous values against the new value.
See the attached sample.
Eventchangesfromblank2urgent.xls
Cell value changes to URGENT
But cannot check
Cell value changes from blank to URGENT
To be able to check this we would need a helper sheet which will store the previous values and then check the previous values against the new value.
See the attached sample.
Eventchangesfromblank2urgent.xls
Please see if this works for you:
Dim oldValue
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Len(Trim(Target.Value)) = 0 Then
oldValue = Target.Value
ElseIf Len(Trim(oldValue)) = 0 And Target.Value = "URGENT" Then
' Your macro is called
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
oldValue = Target.Value
End If
End Sub
I find at least one scenario where the accepted macro fails.
When you open the file and the cursor is at a cell which is not blank and, without changing the selected cell, you change the current cell to URGENT then the macro is fired which is not what is wanted.
When you open the file and the cursor is at a cell which is not blank and, without changing the selected cell, you change the current cell to URGENT then the macro is fired which is not what is wanted.
Hi ssaqibh
would it be difficult to change the macro so that it looks at ranges. i.e. E2:E28, I2:I28, M2:M28
I am new to all this stuff so would ppreciate your help.
would it be difficult to change the macro so that it looks at ranges. i.e. E2:E28, I2:I28, M2:M28
I am new to all this stuff so would ppreciate your help.
Which code? Mine or pkwan's
Try this
If Not Intersect(Target, Range("E2:E28, I2:I28, M2:M28")) Is Nothing Then
.
.
rest of the code
.
.
End If
Try this
If Not Intersect(Target, Range("E2:E28, I2:I28, M2:M28")) Is Nothing Then
.
.
rest of the code
.
.
End If
Thanks ssaqibh, yes I was referring to your code and that works. Unfortunately I have another problem which I am not sure is fixable. The word URGENT doe not get keyed into the cell it is derived from =IF(G6="",IF(BY6=1,"URGENT
so when I use your macro with this code it does not work.
I thought an alternative would be to use a range and then to say if the cell changes from blank to non blank call my macro but this also does not work.
Is there a way to make your macro work with the formula I have =IF(G6="",IF(BY6=1,"URGENT
so when I use your macro with this code it does not work.
I thought an alternative would be to use a range and then to say if the cell changes from blank to non blank call my macro but this also does not work.
Is there a way to make your macro work with the formula I have =IF(G6="",IF(BY6=1,"URGENT
ok hope this works.
The cells 'BY', 'BZ' etc in the actual version change as they reach they reach their specific times. I have taken that out so if you change BY4 to a 1 E4 will change to URGENT. If, as I mentioned in my previous post it is not possible to make the macro run this way I could say if the cell E4 changes from blank to non blank run macro [using the range E:E etc.
Thanks
Time.xls
The cells 'BY', 'BZ' etc in the actual version change as they reach they reach their specific times. I have taken that out so if you change BY4 to a 1 E4 will change to URGENT. If, as I mentioned in my previous post it is not possible to make the macro run this way I could say if the cell E4 changes from blank to non blank run macro [using the range E:E etc.
Thanks
Time.xls
Try this. You should let the code pickup the changes from the cells changed and not by cells changed by formula
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("BY3:CC6")) Is Nothing Then
If Target.Count = 1 Then
If Len(Trim(Target.Value)) = 0 Then
oldValue = Target.Value
ElseIf Len(Trim(oldValue)) = 0 And Cells(Target.Row, (Target.Column - 77) * 4 + 5).Value = "URGENT" Then
' Your macro is called
MESSAGE
End If
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("BY3:CC6")) Is Nothing Then
If Target.Count = 1 Then
oldValue = Cells(Target.Row, (Target.Column - 77) * 4 + 5).Value
End If
End If
End Sub
The previous code picked up changes only in the BY-CC range. This code also takes care of the G3 etc ranges as well
Private Sub Worksheet_Change(ByVal Target As Range)
roff = 0
If Not Intersect(Target, Range("G3:G6,K3:K6,O3:O6,S3:S7,W3:W6")) Is Nothing Then
roff = -2
ElseIf Not Intersect(Target, Range("BY3:CC6")) Is Nothing Then
roff = (Target.Column - 77) * 4 + 5 - Target.Column
End If
If roff <> 0 Then
If Target.Count = 1 Then
If Len(Trim(Target.Offset(, roff).Value)) = 0 Then
oldValue = Target.Value
ElseIf Len(Trim(oldValue)) = 0 And UCase(Target.Offset(, roff).Value) = "URGENT" Then
' Your macro is called
MESSAGE
End If
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
roff = 0
If Not Intersect(Target, Range("G3:G6,K3:K6,O3:O6,S3:S7,W3:W6")) Is Nothing Then
roff = -2
ElseIf Not Intersect(Target, Range("BY3:CC6")) Is Nothing Then
roff = (Target.Column - 77) * 4 + 5 - Target.Column
End If
If roff <> 0 Then
If Target.Count = 1 Then
oldValue = Target.Offset(, roff).Value
End If
End If
End Sub
yes that works brilliantlt thanks. I understand the Target column -77 but can you explain the *4 +5 for me,
Thanks
Regards
Thanks
Regards
when you move one cell BY to BZ you have to move 4 cells E to I, which explains the 4.
5 represents column E where this starts.
5 represents column E where this starts.
Although the macro provided to me runs when I manually change the cell, when the cell changes the macro does not run.
I have a sheet called Static Data which has a clock that runs. When the time reaches a certain time cells will change from a 0 to a 1. when this occurs, on my sheet Daily Data the corresponding cell [BY3] will also change to a 1. when this happens Cell E3 changes to URGENT. Changing 'BY3 to a 1 manually kicks off the Macro but the macro does not run when the above condition takes place from the clock. Maybe I am making this too complicated.
Could I simplify this to say if any cell in the range S;S changes to 1 Run my macro and if so what would the code be?
Thanks for any help in advance
I have a sheet called Static Data which has a clock that runs. When the time reaches a certain time cells will change from a 0 to a 1. when this occurs, on my sheet Daily Data the corresponding cell [BY3] will also change to a 1. when this happens Cell E3 changes to URGENT. Changing 'BY3 to a 1 manually kicks off the Macro but the macro does not run when the above condition takes place from the clock. Maybe I am making this too complicated.
Could I simplify this to say if any cell in the range S;S changes to 1 Run my macro and if so what would the code be?
Thanks for any help in advance
Premium Content
You need an Expert Office subscription to comment.Start Free Trial