Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / July 2006

Tip: Looking for answers? Try searching our database.

Message Box On One Sheet Only

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Risky Dave - 25 Jul 2006 12:31 GMT
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 - 25 Jul 2006 12:45 GMT
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.)

> I have a large spreadsheet with multiple worksheets in it.
>
[quoted text clipped - 13 lines]
>
> Any help/suggestions/sample code gratefully received.

Signature

Dave Peterson

Ron de Bruin - 25 Jul 2006 12:46 GMT
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

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

>I have a large spreadsheet with multiple worksheets in it.
>
[quoted text clipped - 13 lines]
>
> Any help/suggestions/sample code gratefully received.
Risky Dave - 25 Jul 2006 13:20 GMT
Ron & Dave,

Worked a treat :-)

Many thanks

> Hi Risky
>
[quoted text clipped - 25 lines]
> >
> > Any help/suggestions/sample code gratefully received.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.