Sleeper: Drop down list to activate sheet?

[lobo]

I have developed an application that has numerous sheets in it, which I want to hide from the end user. However, I need to be able to have them access these sheets.
I have tried to make a drop down list on my main page listing the names of the sheets, but don't seem to be able to find anyway of having the name choosen to activate the sheet assigned to the name (person's name).
I seem to be having trouble associating the person's name, which is on the tab, with the sheet assigned to him/her.
Anyway of doing this?
I have also looked at using an input box, but can't make this work.

[OBP]

You need the person's name and related work sheet held in cells on a worksheet.
In the Dropdown (combo is probably better) which is populated by the list of names on the sheet the on click event procedure you can then associate the person chosen with it's adjacent cell which contains the Sheet name to be made visible and opened. I have actually programmed this in the past.

[johnske]

Hi lobo, and welcome to VBAX,

What about using the inbuilt dialog box for this, wouldn't it be easier? i.e.

Sub TryThis()
      Application.Dialogs(xlDialogWorkbookUnhide).Show
End Sub

HTH,
John

[lobo]

Johnske, your suggestion works but leaves the sheet visible after I am done with it...I can't have this, it gives the end user an opportunity to manipulate the data whenever he wants, can't happen.

Going to try OBP's suggestion as soon as I get a chance.

There has to be a way....Oh yeah, I have changed the names of the sheets, in the property box, from Sheet1, Sheet2, etc....to the name associated with it...

Thanks

[Zack Barresse]

So what is the order of events here? When they go to the sheet (which Johnske's method you say will work for you) what is the catalyst to make the sheet hidden again? The end user deactivating the sheet (selecting another sheet)? If so, why not just add a worksheet_deactivate event to the worksheet code? If there are too many sheet to add code to, just use the event in your ThisWorkbook module and code for the specific sheets that you are going to be using it on.

[lucas]

DRJ has a kb entry that restricts users to a sheet assigned to the individual and they have to have a password to access it...

Worksheet Protection Manager For Multiple Users
http://www.vbaexpress.com/kb/getarticle.php?kb_id=33


Security issue with above script because as DRJ notes on his entry, if they disable macros they can get around the routine. Solution might be to incorporate Johnkies force macros script.

Force User to Enable Macros
http://www.vbaexpress.com/kb/getarticle.php?kb_id=578

[lobo]

The order of events is this: User either chooses driver's name, sheet opens, adds info, opens next driver's sheet, adds info. Etc...

The problem is that the sheets have to remain hidden, until called, then rehide so that he cannot go into a sheet and add data unless he does it through the dialog boxes....the man using it is just selecting a sheet, cause he can see them, and adding his forecast and then leaving this erroneous information in the sheet....I have a sub to do a forecast then remove the info, but if he doesn't have to use it, he doesn't.

So need to unhide the sheet, then rehide it....There are over 50 sheets in the workbook now and growing as our driver list grows....So this list has to grow and shrink as the need presents itself...

I have something to work with now however....thanks

[lobo]

lucas and the rest, I appreciate all your suggestions....I am going to work on this over the weekend and will let you know....think you have pointed me in right direction....
I can have the sheet activate, via John's suggestion, then rehide it when the user returns to the main screen to access another driver.....hopefully.....am new at this but having a lot of fun....thanks

[lucas]

I just posted the links as an alternative. Good Luck

[Bob Phillips]

The order of events is this: User either chooses driver's name, sheet opens, adds info, opens next driver's sheet, adds info. Etc...

The problem is that the sheets have to remain hidden, until called, then rehide so that he cannot go into a sheet and add data unless he does it through the dialog boxes....the man using it is just selecting a sheet, cause he can see them, and adding his forecast and then leaving this erroneous information in the sheet....I have a sub to do a forecast then remove the info, but if he doesn't have to use it, he doesn't.

So need to unhide the sheet, then rehide it....There are over 50 sheets in the workbook now and growing as our driver list grows....So this list has to grow and shrink as the need presents itself...

I have something to work with now however....thanks

Try this, assuming the list is a control toolbox combobox, and the sheet is called Master

Private Sub ComboBox1_Change()
    Worksheets(Me.ComboBox1.Value).Visible = xlSheetVisible
End Sub

This is worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module. To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "Master" Then
        For Each Sh In ThisWorkbook.Worksheets
            If Sh.Name <> "Master" Then
                Sh.Visible = xlSheetVeryHidden
            End If
        next Sh
    End If
End Sub

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code