Message Box on workbook open to control displayed worksheets

jmartin79

New Member
Joined
Sep 25, 2006
Messages
2
I have a workbook that contains 3 worksheets. I want to have a popup when the sheet is open with 3 options. A little background.. The workbook contains a "Equipment Guide" Shows equipment and qualification for that equipment for our sales reps. Also has a "Price List" that shows our wholesale price if a customer wants to buy equipment. The last sheet is a master list that shows our cost, our wholesale price and the MSRP of the equipment.. I want to, on workbook open, have a popup with 3 choices. "Open Equipment Guide", this will have to kick off a Macro to hide the other 2 sheets, "Open Price List" would hide the 2 not needed a 3rd "Open as Administrator" which would prompt for a password then display all 3 worksheets and unprotect them. Is this possible?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
How about something like this. It needs to be put in the module for the workbook

Code:
Private Sub Workbook_Open()
    Dim varAnswer
    Dim blnOK As Boolean
    Dim ws As Worksheet
    
        
    'keep looping until input is OK
    blnOK = False
    
    Do
        'get user input
        varAnswer = InputBox("please choose one option" & vbCrLf & vbCrLf & _
                "1) Open Equipment Guide" & vbCrLf & _
                "2) Open Price List" & vbCrLf & _
                "3) Open as Administrator")
                
        'check for allowed entry
        If IsNumeric(varAnswer) Then
            'hide sheets check for password
            Select Case varAnswer
                Case Is = 1
                    Sheets("Master List").Visible = xlVeryHidden
                    Sheets("Price List").Visible = xlVeryHidden
                    blnOK = True
                Case Is = 2
                    Sheets("Master List").Visible = xlVeryHidden
                    Sheets("Equipment Guide").Visible = xlVeryHidden
                    blnOK = True
                Case Is = 3
                    varAnswer = InputBox("please input the password")
                    If varAnswer = "password" Then   'change for your password
                        blnOK = True
                        For Each ws In ThisWorkbook.Sheets
                            'may need password as argument in unprotect method
                            ws.Unprotect
                        Next ws
                    Else
                        MsgBox "Incorrect password - returning to Starting menu"
                    
                    End If
                Case Else
                    MsgBox "must be a number 1, 2 or 3. Please re-enter"
            End Select
        
        Else
            MsgBox "must be a number 1, 2 or 3. Please re-enter"
        End If
    
    Loop Until blnOK
        
End Sub
 

jmartin79

New Member
Joined
Sep 25, 2006
Messages
2
I got something like this to work by googling and stealing some other peoples code.. It is very similar, thanks for replying.
 

Forum statistics

Threads
1,141,049
Messages
5,703,937
Members
421,321
Latest member
blusky4

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
Top