VBA Message Box Help...

[Golfpro1286]

I am working on a code to fire a message box is a certain CheckBox133 is selected and if cell B225 is greater than $75,000. The code below works and message fires, the problem is that it fires any time a cell is changed. How do I limit this VBA to only changes to that checkbox and cell?

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("Commercial Loan Worksheet").CheckBox133.Value = True And Sheets("Commercial Loan Worksheet").Range("B225").Value >= 75000 Then
MsgBox "Alert: This loan is for business purpose and exceeds $75M. A loan memo is required for the file."
Sheets("Commercial Loan Memo").Visible = True
End If
End Sub

Thanks,

Adam

 

[Fluff]

Changing a checkbox will not trigger the change event.
Is B225 changed manually?

 

[Steve_]

I am working on a code to fire a message box is a certain CheckBox133 is selected and if cell B225 is greater than $75,000. The code below works and message fires, the problem is that it fires any time a cell is changed. How do I limit this VBA to only changes to that checkbox and cell?

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("Commercial Loan Worksheet").CheckBox133.Value = True And Sheets("Commercial Loan Worksheet").Range("B225").Value >= 75000 Then
MsgBox "Alert: This loan is for business purpose and exceeds $75M. A loan memo is required for the file."
Sheets("Commercial Loan Memo").Visible = True
End If
End Sub

Thanks,

Adam

You mean like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$225" Then
        If Sheets("Commercial Loan Worksheet").CheckBox133.Value = True And Sheets("Commercial Loan Worksheet").Range("B225").Value >= 75000 Then
            MsgBox "Alert: This loan is for business purpose and exceeds $75M. A loan memo is required for the file."
            Sheets("Commercial Loan Memo").Visible = True
        End If
    End If
End Sub

 

[Golfpro1286]

You mean like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$225" Then
        If Sheets("Commercial Loan Worksheet").CheckBox133.Value = True And Sheets("Commercial Loan Worksheet").Range("B225").Value >= 75000 Then
            MsgBox "Alert: This loan is for business purpose and exceeds $75M. A loan memo is required for the file."
            Sheets("Commercial Loan Memo").Visible = True
        End If
    End If
End Sub

I will give this a try when I get back to the office. I tried something similar earlier but tried to include the checkbox in the if target.address line. Thanks for the help!

 

[Fluff]

Changing a checkbox will not trigger the change event.

Could you also answer my question from post#2?

 

[Golfpro1286]

Changing a checkbox will not trigger the change event.

Could you also answer my question from post#2?

Cell B225 is a formula that sums a group of other cells. If the checkbox will not directly trigger it, could I link it to another cell and have that cell + B225 trigger it?

 

[Fluff]

Fraid not. That will still not trigger the change event & nor will cell B225 if it has a formula.
Are the cells that B225 sums all on that sheet & are they all manually entered?

 

[Golfpro1286]

Fraid not. That will still not trigger the change event & nor will cell B225 if it has a formula.
Are the cells that B225 sums all on that sheet & are they all manually entered?

Yes, those cells are manually entered.

 

[Steve_]

Yes, those cells are manually entered.

First, put this code in your Sheet module..

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$225" Then
        DoStuff
    End If
End Sub

Next, create a new module and add this code to it...

Sub CheckBox133_Click()
    DoStuff
End Sub
Function DoStuff() 'Change this to whatever...
    If Sheets("Commercial Loan Worksheet").CheckBox133.Value = True And _
       Sheets("Commercial Loan Worksheet").Range("B225").Value >= 75000 Then
        MsgBox "Alert: This loan is for business purpose and exceeds $75M. A loan memo is required for the file."
        Sheets("Commercial Loan Memo").Visible = True
    End If
End Function

 

[Fluff]

Ok, this should work for when B225 changes value

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KyCell As Range
   If Target.CountLarge > 1 Then Exit Sub
   Set KyCell = Range("B225")
   On Error Resume Next
   Set KyCell = Union(KyCell, KyCell.Precedents)
   On Error GoTo 0
   If Not Intersect(Target, KyCell) Is Nothing Then
      If Me.CheckBox133.Value = True And Range("B225").Value >= 75000 Then
         MsgBox "Alert: This loan is for business purpose and exceeds $75M. A loan memo is required for the file."
         Sheets("Commercial Loan Memo").Visible = True
      End If
   End If
End Sub

For the checkbox, you would need to have code behind the check box