Creating a Pop Up Window on a worksheet

[jolie523]

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

[Mudface]

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

[Yogi Anand]

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!

[Mudface]

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?

[Al Chara]

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 ]

[Mudface]

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.

[jolie523]

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

[jolie523]

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

Thanks again,
Jolie

[Yogi Anand]

Hi Mudface, Al Chara, jolie523:

T H A N K S !

[Al Chara]

jolie523

Delete the code or comment it out.