PDA

View Full Version : Solved: Linking a Macro to an IF Statement



Durham09
12-24-2008, 10:35 AM
I have an excel file that has a range of cells (E54:E63) with the following IF Statement:

=IF(A58="","",IF($B$19+60<=TODAY(),$B$19+60,""))

(Note: $B$19 is a date)

I would like to write a code that would run the following Macro if the IF Statement in the cell is true.


Sub YesNoMessageBox()
Dim Answer As String
Dim MyNote As String
'Place your text here
MyNote = "Do you want to email Manager/Employee?"
'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Email")
If Answer = vbNo Then
'Code for No button Press

Else
'Code for Yes button Press
Application.Dialogs(xlDialogSendMail).Show
End If
End Sub

I tried the following formula:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bRun As Boolean
Dim strFormula As String

On Error Goto FormulaError
bRun = Range("E54, E63")


If bRun = True Then
Run "YesNoMessageBox"

End If

Exit Sub
FormulaError:
MsgBox "Error in formula"

End Sub

But it doesn't quite do what I would like; which is, to have a message box displayed based on the results of the IF Statement in each individual cell. Additionally, if possible I would like it to disable the macro if Range (D54:63) is filled out(text). I don't know if I have to write a code for each seperate row or not but Rows (54:63) are filled out independently by the user so the Macro will only need to run If the statement in Column E(54:63) is true and column D(54:63) is not filled out. There is probably an easier way to do this but I don't know it.

Thanks in advance for your help...I am a bit VBA illiterate.:banghead:

Edit Lucas: VBA tags added to code. Durham09, you can select your code and hit the vba button when posting to format your code.

GTO
12-24-2008, 03:44 PM
Greetings Durham,

First, welcome to the forum :-) You will 'meet' plenty of great folks here who are very helpful, so don't worry about being a 'newbie' to vba.

Now as to your question:

I have an excel file that has a range of cells (E54:E63) with the following IF Statement:
=IF(A58="","",IF($B$19+60<=TODAY(),$B$19+60,""))
(Note: $B$19 is a date)
If I understand at least part of what you're asking, then:

Cell E54....contains the formula....=IF(A58="","",IF($B$19+60<=TODAY(),$B$19+60,""))
Cell E55....contains the formula....=IF(A59="","",IF($B$19+60<=TODAY(),$B$19+60,""))
Cell E56....contains the formula....=IF(A60="","",IF($B$19+60<=TODAY(),$B$19+60,""))
...and so on, til...
Cell E63....contains the formula....=IF(A67="","",IF($B$19+60<=TODAY(),$B$19+60,""))

If I have that part correct, then reference:

I would like to write a code that would run the following Macro if the IF Statement in the cell is true.
Sorry, but what does that mean? If the initial test is passed, there's nothing in the cell. Do you mean that when the second IF is passed (the, "IF($B$19+60<=TODAY()" part), then...

But it doesn't quite do what I would like; which is, to have a message box displayed based on the results of the IF Statement in each individual cell. Additionally, if possible I would like it to disable the macro if Range (D54:63) is filled out(text). I don't know if I have to write a code for each seperate row or not but Rows (54:63) are filled out independently by the user so the Macro will only need to run If the statement in Column E(54:63) is true and column D(54:63) is not filled out...

Okay, this is guessing a bit, but let's see if this is what you want:

When the results of the formula in E54:E63 result in a date being placed in the cell, you would like the cell to the left checked. If there's nothing in the offset cell, then you would like the user to be offered the choice as to emailing the workbook as an attachment.

If I have that wrong, could you restate your problem and/or post a sample workbook? If I got it right, you can try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim _
rngRangeToCheck As Range, _
rngCellInRangeToCheck As Range

Set rngRangeToCheck = Range("E54:E63")

For Each rngCellInRangeToCheck In rngRangeToCheck

If IsDate(rngCellInRangeToCheck.Value) _
And rngCellInRangeToCheck.Offset(, -1).Value = "" Then
Call YesNoMessageBox
Exit For
End If

Next


End Sub

Also - when you are posting example code, use the little green/white VBA button at the top of the message window, and paste the code between the tags. This makes the code easier to read.

Happy coding, and hope this helps,

Mark

rbrhodes
12-24-2008, 06:08 PM
Hi,

I'm on the same track

-E54 changes based on A58
-E55 changes based on A59
- etc, to
-E63 changes based on A67

This code checks changes in the Column A range, then checks Col D in the appropriate row...


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Answer As String
Dim MyNote As String
'See if change was in Col A range
If Not Intersect(Target, Range("A58:A67")) Is Nothing Then
'Check if Col E value is ""
If Range("E" & Target.Row - 4) = vbNullString Then Exit Sub
'Check if Col D is blank
If Range("D" & Target.Row - 4) = vbNullString Then
'Place your text here
MyNote = "Do you want to email Manager/Employee?"
'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Email")
If Answer = vbNo Then
'Code for No button Press
Exit Sub
Else
'Code for Yes button Press
Application.Dialogs(xlDialogSendMail).Show
End If
End If
End If
End Sub

mikerickson
12-24-2008, 08:37 PM
If the user changes a precedent cell of E54:E63, YesNoMessageBox will be called if the dependent of the cell the user changed returns a non-blank value. It will be called even if the non-blank value is the same as before the users action.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rangeOfFormulas As Range
Dim keyRange As Range
Dim oneCell As Range

Set rangeOfFormulas = Range("E54:E63")

On Error Resume Next
Set keyRange = Application.Intersect(Target, rangeOfFormulas.Precedents)
On Error GoTo 0

If Not keyRange Is Nothing Then
For Each oneCell In keyRange.Dependents
If CStr(oneCell.Value) <> vbNullString Then
Call YesNoMessageBox
End If
Next oneCell
End If
End Sub
This is slower, but YesNoMessage box is called only if a formula in E54:E63 changes to a non-blank value.
Private Sub Worksheet_Calculate()
Static memory As Variant
Dim newValues As Variant
Dim i As Long

newValues = Range("E54:E63").Value: Rem adjust

On Error GoTo HaltRoutine

For i = 1 To UBound(newValues)
If newValues(i, 1) <> vbNullString And newValues(i, 1) <> memory(i, 1) Then
On Error Goto 0
Call YesNoMessageBox
End If
Next i

HaltRoutine:
memory = newValues
On Error GoTo 0
End Sub

Durham09
12-29-2008, 10:56 AM
Greetings Everyone:

Sorry I have been unable to reply back (I was on vacation:*) ). Thanks so much for your replies and with helping me on this. Just to clarify GTO this is correct:

Cell E54....contains the formula....=IF(A58="","",IF($B$19+60<=TODAY(),$B$19+60,""))
Cell E55....contains the formula....=IF(A59="","",IF($B$19+60<=TODAY(),$B$19+60,""))
Cell E56....contains the formula....=IF(A60="","",IF($B$19+60<=TODAY(),$B$19+60,""))
...and so on, til...
Cell E63....contains the formula....=IF(A67="","",IF($B$19+60<=TODAY(),$B$19+60,""))

Also,
If both statements are true (For Example: for cell E54, if A58 is filled but D58 is not and $B$19+60<=TODAY() then I want the macro "YesNoMessageBox" to be enabled; however, I want nothing to occur if D58 is filled as well.)

Basically,

When the results of the formula in E54:E63 result in a date being placed in the cell and if the cell to the left does not have a resolution in it (i.e. is not filled out by the user) then I would like the macro YesNoMessage Box to pop up allowing the user to decide whether they would like to email the application.

I hope this clarifies any questions.:think: I tried GTO's code but nothing happened. I haven't tried anyone else's yet. The portion of the document that I am working on has been attached as well.

rbrhodes
12-29-2008, 01:54 PM
Ok, I'm confused! The example shows the resolution as being in Col F.

Durham09
12-29-2008, 01:59 PM
I'm sorry....I meant F. Let me rephrase that one more time....

I wrote...
"Also,
If both statements are true (For Example: for cell E54, if A58 is filled but D58 is not and $B$19+60<=TODAY() then I want the macro "YesNoMessageBox" to be enabled; however, I want nothing to occur if D58 is filled as well.)"


I meant...
"Also,
If both statements are true (For Example: for cell E54, if A58 is filled but F58 is not and $B$19+60<=TODAY() then I want the macro "YesNoMessageBox" to be enabled; however, I want nothing to occur if F58 is filled as well.)"

Again...sorry about that. : pray2:

rbrhodes
12-30-2008, 06:55 AM
Hi,

"For Example: for cell E54, if A58 is filled but F58 is not ..."

I trust you meant E58.

Anyways here's GTO's code with some slight mods and I've included the called sub as well.

To see the code right click on the sheet tab and choose view code. NOTE: The called sub could go in a module but the Change sub _must_ be in the sheet module

Durham09
12-30-2008, 07:32 AM
Ugh...I'm so fustrated with myself... okay let me try to explain one more time...

For example: If the result of the formula in E58 results in a date in the cell (because A58 is filled out and $B$19+60<=TODAY()) and there is no resolution in F58 under "Date Cleared/Final Resolution" then I want the YesNoMessageBox macro to pop up; otherwise, If F58 has a date or text in it I don't want the macro to run. Does this make sense?

The problem I am having with the code that you have written and the one I used before is that I only need the macro to be enabled once but everytime I click outside of the row to edit any other part of the sheet the macro is enabled which I think will be fustrating to the user.

rbrhodes
12-30-2008, 08:29 AM
Hi,

This will limit it to Col A58:A67 changing. That way if there is a value put in that range the sub checks Cols E and F...

Delete the old sub and put this one in its place


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngRangeToCheck As Range
Dim rngCellInRangeToCheck As Range

'Only fire if Col A is changed (change range address to suit)
If Not Intersect(Target, Range("A58:A67")) Is Nothing Then

'Check if deleted
If Target = "" Then GoTo endo

'Change range address to suit
Set rngRangeToCheck = Range("E58:E67")

For Each rngCellInRangeToCheck In rngRangeToCheck
If IsDate(rngCellInRangeToCheck.Value) _
And rngCellInRangeToCheck.Offset(, 1).Value = "" Then
Call YesNoMessageBox
Exit For
End If
Next
endo:

Set rngRangeToCheck = Nothing
Set rngCellInRangeToCheck = Nothing

End If

End Sub

Durham09
12-30-2008, 08:34 AM
YOU ARE AWESOME!!! It does exactly what I need. Thank you so much! :bow: