VBA to display MsgBox only once

[kmcroft13]

Hello,

I am trying to write some VBA code that will check a cell value and display a message box warning if it is below a certain threshold. the key here is that I want the message box to display only once. I figured to do this I would have to write some values in a hidden cell and check those values to see if the message has been displayed.

This is what I have so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim maxRange As Worksheet
Dim maxCell As Range


Set maxRange = ThisWorkbook.Sheets("Sheet4")
Set maxCell = maxRange.Range("C19")


For Each cell In Range("F22")
If maxCell.Value <> 1 Then
    Select Case cell.Value
        Case Is >= 5
        Case Is = ""
        Case Else
        Application.EnableEvents = False
        cell.Select
        MsgBox ("blah blah blah")
        Application.EnableEvents = True
        Set maxCell.Value = 1
    End Select
End If
Next cell
End Sub

I'm getting a "subscript out of range" error when I try to run the code, but I'm not sure what that means or why it's happening. Any thoughts?

Thanks!

 

[ChrisM]

You're probably getting that error because of this line:

Set maxCell.Value = 1

You don't use the "Set" in this context, it should just be maxCell.Value = 1

However, I think you've over-complicated what you are trying to do here. If all you want to do put up a warning one time you can use a static variable. Even if you don't use a static variable, the way you've written this is overkill.

Here's how to do it with a static variable. It will retain it's value for the whole excel session (or until you have a critical VBA error, that will reset this value)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRange As Worksheet
Static bWarning as Boolean

Set myRange = ThisWorkbook.Sheets("Sheet4").Range("F22")
If myRange = "" Then Exit Sub

If bWarning = False Then
   If myRange < 5 Then
       msgbox "Blah"
       bWarning = True
   End If
End If

End Sub

 

[kmcroft13]

I actually was using something very similar before, but it became annoying to have the message box appear in subsequent sessions. With this method, the message will truly appear only once, regardless of the session.

I tried removing the "set" and now just have maxCell.Value = 1 but it is still giving me an error. The de-bugger show this line "Set maxRange = ThisWorkbook.Sheets("Sheet4")" as the culprit. Any other thoughts?

 

[ChrisM]

That line should work fine. Is "Sheet4" really the name of your worksheet - that's what you see in the tabs at the bottom of the main window? Or is sheet4 the codename? In which case your code should just be: Set maxRange = ThisWorkbook.Sheet4

 

[kmcroft13]

Oh man, yep that was it. Sheet4 is the codename. Sorry, that was a silly mistake. But thanks for your help!