Solved: Unhide only certain sheets Macro

[Barryj]

I had a previous thread where the macro unhide a sheet and then hide it once another sheet was activate, is there a way when the button calls the dialog box showing all the hidden sheets of only showing sheets that I want others to access, ie: if there were 5 sheets and I only want them to have access to sheets 4 & 5.

Is this possible, I have tried to do this with the code that is in module 1 but it does not like it?

[Simon Lloyd]

You can hide sheets from that dialog box by making them very hidden like this: [VBA]Sheets("Sheet1").Visible = xlVeryHidden[/VBA]this way they can only be made visible by using [VBA]Sheets("Sheet1").Visible = TRUE[/VBA]Regards,
Simon

[mvidas]

Hi Barry,

There is not a way using the build-in dialog that you're using. However, you can always just make a form that acts the same way, but with the functionality you need.

I'm attaching your worksheet again, but slightly modified. I added a form to it (to use it with another workbook, simply click/drag the form object from this to whatever book you want to use. You could also export the form (i believe it creates two files, a .frm and .frx), then import as desired into other projects. Currently the way the code is written it shows all non-visible sheets. This is the UserForm_Initialize event from the form:[vba]Private Sub UserForm_Initialize()
Dim WSNames() As String
WSNames = GetHiddenSheetNames
If Len(WSNames(0)) = 0 Then Exit Sub
cmdOK.Enabled = True
lbSheets.List = WSNames
End Sub[/vba]

If there are any specific sheets you want to exclude, send their names as arguments to the GetHiddenSheetNames function, like[vba] WSNames = GetHiddenSheetNames("Sheet3", "Sheet4")[/vba]

If there are any specific sheets you ONLY want to be included (rather than specifically excluding any), change the _Initialize event to be something like:[vba]Private Sub UserForm_Initialize()
cmdOK.Enabled = True
lbSheets.List = Array("Sheet1", "Sheet2")
End Sub[/vba]

I didn't know which method you'd prefer to use, so I thought I'd give you instructions for both. I'm guessing you're going to want to only show/hide specific sheets based on the user (see the KB here for various ways of obtaining the Username of who is logged on to the computer), using Select Case would likely be the best way of using that username to hide/show specific sheets.

Please don't hesitate to ask if you have any questions!
Matt

[Barryj]

Simon where does this part of the code fit into what I already have? I have tried putting it into the sheet code but it still shows up in the diolog box?

[Simon Lloyd]

To be honest i didnt check your workbook, but try putting it in a standard module step through it with F8 and sheet1 should hide and not be available in the Format | Sheets | UnHide dialog box.

Regards,
Simon

[mvidas]

Simon,

His sheets are already very hidden .. it is strange that [vba]Application.Dialogs(xlDialogWorkbookUnhide).Show[/vba]Ignores the Very part of VeryHidden and still allows you to unhide it, but going to the menu option does not allow it..?

I also think it is strange that the dialog "xlDialogWorkbookUnhide" unhides sheets in a workbook, whereas "xlDialogUnhide" unhides hidden workbooks. Go figure.

[Barryj]

Thanks for the information mvidas, I had a play arround with the workbook and the idea is to exclude the user from viewing certian sheets, I tried putting the code you gave in module 1 but could not find it when I wanted to assign the macro, am I missing somthing, thanks for the help on this.

[mvidas]

Hi,

You wouldn't see it when you want to assign, due to the Private keyword there. You do want that there though. When you open the file, right-click on the user form and select View Code. You'll see a subroutine called "UserForm_Initialize", and you'll want to replace the existing code there (currently the way it is written (my first posted code above) shows all non-visible sheets) to whatever variation you want.

I have already put the code in there to call the form, in your existing "UnhideSheets" macro:[vba]Sub UnhideSheets()
frmUnhideSheets.Show
End Sub[/vba]

[Barryj]

I tried replacing [VBA]

Sub UnhideSheets()
frmUnhideSheets.Show
End Sub

[/VBA]

With
[VBA]

Private Sub UserForm_Initialize()
cmdOK.Enabled = True
lbSheets.List = Array("Sheet1", "Sheet2")
End Sub

[/VBA]

But it is telling me it cannot find macro!

little lost here.

But I do want to show only certain sheets say sheet 1 & 2 out of the 5

Thanks for the your help.

[mvidas]

Sorry about that, probably just a slight miscommunication.

Leave the UnhideSheets sub (as I wrote it) as-is.

Look at the code for the form (right-click on the form object and select View Code). Find the UserForm_Initialize sub in there, and replace it with the above UserForm_Initialize sub.

That should do it!
Matt

[johnske]

If the idea is only to restrict access to some hidden sheets and not necessarily to restrict the user from seeing the names of all the hidden sheets you could use
[VBA]
Option Explicit

Sub UnhideSomeSheets()
If Application.Dialogs(xlDialogWorkbookUnhide).Show = True Then
Select Case ActiveSheet.Name
'insert the names of the restricted sheets below
Case "Sheet2", "Sheet3", "Sheet4"
ActiveSheet.Visible = xlSheetVeryHidden
MsgBox "Access to this sheet is restricted"
End Select
End If
End Sub
[/VBA]

[Barryj]

Thanks Guys Got it all operational, thanks again for all your help I am very greatful for the assistance this forum provides.