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]

You could use this

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, Range("[COLOR=#ff0000]B4[/COLOR]"))
   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
Private Sub CheckBox133_Click()
   Range("[COLOR=#ff0000]B4[/COLOR]").Value = CheckBox133.Value
End Sub

Change the B4 in red to a cell of your choosing

 

[Golfpro1286]

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

This worked perfectly, thank you so much for your help!

 

[Fluff]

You're welcome & thanks for the feedback.