Solved: MsgBox appears only once

[K. Georgiadis]

I have two worksheets in the same workbook that contains a total of 35 sheets. When the user opens either of these two specific worksheets for the first time, I would like a MsgBox with the messgae:

"Please select a sourcing method before proceeding"

and an "OK" button that makes the MsgBox disappear.

However, I would only like the message box to appear once after the workbook has been opened, not to appear again until the workbook has been closed and reopened. Is that Posssible? Should the code be embedded in each target worksheet or should it be in ThisWorkbook, pointing to the target sheets?

[p45cal]

Well, I tried the following and was surprised it worked. Without having an autoopen, or workbook_open or work_close event to reset the variables, without having to have different variable names in each module:

For each sheet where you want the warning to occur, right click the tab of that sheet and choose 'View Code', where the cursor is flashing, paste this code:

Dim BeenActivated As Boolean
Private Sub Worksheet_Activate()
If Not BeenActivated Then
MsgBox "Please select a sourcing method before proceeding"
BeenActivated = True
End If
End Sub
You don't need to save when that's done, just close the window. The code will get saved when you save the document.

The warning appears the first time each worksheet is activated after the file has been opened. If you want for the warning to appear once when one of the worksheets has been activated, but NOT to appear when the second one is later activated, then instead of the above:

Put this code in a standard code module:

Dim BeenActivated As Boolean
Sub blah()
If Not BeenActivated Then
MsgBox "Please select a sourcing method before proceeding"
BeenActivated = True
End If
End Sub
and this in each of the sheet modules (as described above):

Private Sub Worksheet_Activate()
blah
End Sub

[K. Georgiadis]

Works like a charm! Thanks p45cal!