Run code once

**emoandy05** · 06-13-2012, 09:00 AM

Hi,

I have the following code for one of my worksheets:

Private Sub Worksheet_Calculate()
Dim c As Range
For Each c In Range("O4:O46")
If c.Value <= 0 Then MsgBox "Updates are available", vbInformation
Next c
End Sub

How do I get this to run only once when I open Excel?

Any help you be appreciated.

**Bob Phillips** · 06-13-2012, 09:07 AM

Put it in the Thisworkbook code module

Private Sub Worksheet_Open()
Dim c As Range
    For Each c In Worksheets("whatever it is called").Range("O4:O46")
        If c.Value <= 0 Then MsgBox "Updates are available", vbInformation
    Next c
End Sub

**tom1977** · 06-13-2012, 09:16 AM

I think that You wants run Msgbox only once. If so You can use this

Private Sub Workbook_Open()
Dim c As Range
Dim x As Long
For Each c In Range("O4:O46")
If Not IsEmpty(c) And c.Value <= 0 Then
x = x + 1
End If
Next c
If x > 0 Then MsgBox "Updates are available", vbInformation
End Sub

**emoandy05** · 06-13-2012, 09:22 AM

Tom, I just replaced my code with yours. The only thing I changed was the range, but no message box appears. Im just right clicking on the sheet tab, selecting view code, and pasting it in.

Any suggestions?

**Bob Phillips** · 06-14-2012, 05:09 AM

Originally Posted by emoandy05

Tom, I just replaced my code with yours. The only thing I changed was the range, but no message box appears. Im just right clicking on the sheet tab, selecting view code, and pasting it in.

Any suggestions?

Maybe qualify the range with the sheet name as I suggested.