Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

I need some vba code to make a macro run if any cell changes from blank to URGENT

Posted on 2012-12-24
18
Medium Priority
?
744 Views
Last Modified: 2013-01-07
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
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38718386
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

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

Open in new window

This macro works on sheet1 with the backup on sheet1 (2)
0
 

Author Comment

by:Jagwarman
ID: 38718394
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?????
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38718403
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 16

Accepted Solution

by:
Peter Kwan earned 2000 total points
ID: 38718446
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

Open in new window

Temp.xls
0
 

Author Closing Comment

by:Jagwarman
ID: 38718839
thanks that works brilliantly
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38718922
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.
0
 

Author Comment

by:Jagwarman
ID: 38722971
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.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38723021
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
0
 

Author Comment

by:Jagwarman
ID: 38723060
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",IF(BY6=2,"OVERDUE","")),"")

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",IF(BY6=2,"OVERDUE","")),"") or is this not possible?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38723112
Can you mock a small (fictitious) sample and upload it for me to work on?
0
 

Author Comment

by:Jagwarman
ID: 38723274
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38726285
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

Open in new window

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38726319
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

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 38726403
yes that works brilliantlt thanks. I understand the Target column -77 but can you explain the *4 +5 for me,

Thanks
Regards
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38726518
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.
0
 

Author Comment

by:Jagwarman
ID: 38726550
Many thanks I understand now
0
 

Author Comment

by:Jagwarman
ID: 38750383
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
0
 

Author Comment

by:Jagwarman
ID: 38750391
the first sentence should read Although the macro provided to me runs when I manually change the cell, when the cell changes automatically the macro does not run.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question