Message Box On One Sheet Only

[Guest]

I have a large spreadsheet with multiple worksheets in it.

When one specific worksheet is selected (it does the calculations used on
the other pages) I would like a pop-up text box to appear warning the user
not to print it.

Is there a relatively easy way to do this given that I can't write VB
(though I can follow it in the VB editor)? I have been able to create a
pop-up that appears on every page by putting an Auto_Open Msgbox in the
ThisWorkbook module, but can't figure out how to get it to work on only one
worksheet - is this a syntax probem or can't it be done?.

Another option would be to completely hide the tab, but when I do this, the
macros that refer to the page from other worksheets fail to function
correctly. Is there a way around this?

Any help/suggestions/sample code gratefully received.

 

[Dave Peterson]

If you rightclick on the worksheet tab, you can select View Code.

Then use the code window (right hand side) to choose Worksheet and Activate from
those two dropdowns at the top of the code window.

Then you could modify what you see to look like:

Option Explicit
Private Sub Worksheet_Activate()
MsgBox "Don't print this page"
End Sub



And you can select that sheet
then Format|Sheet|Hide
to hide the sheet.

If your macro breaks, it could be that you've used .select's within your code.
These aren't usually necessary--and since the sheet has to be selected (and
therefore, visible) to select any cells on that sheet, the code breaks.

You have at least a couple of choices.

Rewrite your code to avoid the select's. It may be more difficult, but it's
probably worth it to make the code easier to understand/update later.

Or (not recommended)....

Have your code turn off the screenupdating, unhide that sheet, do your real
work, hide the sheet, and turn screenupdating back on.

It may seem like a quicker fix, but I bet in a couple of weeks when you have to
tweak the code, you'll see the problem.

(Recorded code can work, but it's not efficient and it's too hard to see what's
going on.)

 

[Ron de Bruin]

Hi Risky

There is a sheet activate event that run when you select the sheet

Right click on the sheet tab and choose view code
Copy this event in the sheet module that is active now
Alt Q to close the VBA editor

Private Sub Worksheet_Activate()
MsgBox "Not Print"
End Sub

 

[Guest]

Ron & Dave,

Worked a treat

Many thanks

Hi Risky

There is a sheet activate event that run when you select the sheet

Right click on the sheet tab and choose view code
Copy this event in the sheet module that is active now
Alt Q to close the VBA editor

Private Sub Worksheet_Activate()
MsgBox "Not Print"
End Sub