Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Creating a Pop Up Window on a worksheet

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?


  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thanks again,
    Jolie

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mudface, Al Chara, jolie523:

    T H A N K S !

  10. #10
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    jolie523

    Delete the code or comment it out.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •