Creating a Pop Up Window on a worksheet

jolie523

New Member
Joined
Mar 21, 2002
Messages
3
Is it possible to create a Pop up window that will appear when someone clicks onto one of the tabs in my document? I just want the window to to read 'Under Construction' with an 'OK' button. Once they click OK, the window goes away. I can't find anything like that in help. If anyone knows, that would be soooooo coooool!

Thanks so much,
Jolie
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I'm assuming you know how to enter VB code :). Insert a new worksheet and then hide it. For each of the worksheets that you want to display, right-click on the sheet tab and enter the following code (change the Sheet4 bit to the number of the worksheet you just added, eg if you have 7 worksheets in your file originally, change this to Sheet8):-

Private Sub Worksheet_Activate()

Sheet4.Range("A1").Value = Me.Name

End Sub

For each of the worksheets that you don't want to display, right-click on the sheet tab and paste in the following (again change Sheet4 to the worksheet you just added): -

Private Sub Worksheet_Activate()

MsgBox "Under construction"
Worksheets(Sheet4.Range("A1").Value).Activate

End Sub
 
Upvote 0
Hi Mudface:
I am trying to follow your response to post from jolie523 ... I need a bit of a lesson here.
1) What is the purpose of adding a sheet and then hiding it
2) I do get the window saying under construction to popup in the worksheet when I click on the associated worksheet tab, but after clicking on OK, it responds with Run-time error9: Subscript out of range
Please help with an explanation!
 
Upvote 0
Hi, Yogi, the sheet insert/ hide is to have a sheet to record which sheet was last activated, which can't be altered by the (casual) user.

When a sheet is activated, its name is recorded in cell A1 of the hidden sheet, unless it's a sheet you don't want displayed, in which case the message is shown and the last sheet selected is re-activated, based on the value in the hidden sheet. Not sure about your run-time error, can you give more details?
 
Upvote 0
Yogi,

The key is that you have to click on one of the sheets that you want displayed before you click on a sheet that you don't want displayed.

Mudface,

That code is pretty slick.
_________________
Hope this helps.
Kind regards, Al.
This message was edited by Al Chara on 2002-03-22 15:56
 
Upvote 0
Heh, not slick enough :). Good spot. Add the following to the workbook module:-

Private Sub Workbook_Open()

Sheet4.Range("a1").Value = Sheet1.Name

End Sub

Chnage the Sheet1 bit to the name of a valid sheet that you want displayed.
 
Upvote 0
Thanks you guys - you even worked out the kinks. I don't know visual basic, but I can try - it looks doable. Thank you so much.

Jolie
 
Upvote 0
I have another question though - how do I remove the pop up once I've completed the under construction page?

Thanks again,
Jolie
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top