Sleeper: UserForms help

[spaz9876]

I am trying to create a userform with a list of checkboxes so that employees can check which worksheets they want to view. I also want to put a password on certain worksheets. I am just learning VBA and don't know how to do a code for this.

UserForm looks like this:
Quoting
Checkbox 1 - Price List
Checkbox 2 - Routing Slip
Checkbox 3 - Lease Option
CHeckbox 4 - Summary Sheet

After the Sale (Password Protected sheets)
Checkbox 5 - Statement of Work
Checkbox 6 - Financial Info Sheet
Checkbox 7 - Project Info Sheet
Checkbox 8 - Approval Sheet

If anyone can help me with this code, I would greatly appreciate it!

I tried using http://www.vbaexpress.com/kb/getarticle.php?kb_id=33 but couldnt get it to work.

[Bob Phillips]

I am trying to create a userform with a list of checkboxes so that employees can check which worksheets they want to view. I also want to put a password on certain worksheets. I am just learning VBA and don't know how to do a code for this.

UserForm looks like this:
Quoting
Checkbox 1 - Price List
Checkbox 2 - Routing Slip
Checkbox 3 - Lease Option
CHeckbox 4 - Summary Sheet

After the Sale (Password Protected sheets)
Checkbox 5 - Statement of Work
Checkbox 6 - Financial Info Sheet
Checkbox 7 - Project Info Sheet
Checkbox 8 - Approval Sheet

If anyone can help me with this code, I would greatly appreciate it!

I tried using http://www.vbaexpress.com/kb/getarticle.php?kb_id=33 but couldnt get it to work.

That's a busy flag!

A little more info please.

What do you mean by after the sale?

What determines only sheets 1-4 at the start?

Do you want to view or protect/unprotect, or maybe even hide?

How did we get from 1-4 to 5-8?

[spaz9876]

I would like one department to see all 8 sheets but another department to only see the first 4 sheets. "After the Sale" is just the heading - that department should not be able to see our financial information - only able to see the sheets for quoting purposes.
I'm thinking the checkboxes arent going to work or going to be too difficult for me.
Maybe this option - if the person types in the password when they first open the workbook, they should only be able to view the sheets per that password.

[lucas]

Hey Spaz,
here is a link to a kb entry by kpuls that might be just what your looking for.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=380

I also attached a zip file that I think originally came from DRJ on this forum. It allows multiple password protected sheets and passwords.

[Bob Phillips]

I would like one department to see all 8 sheets but another department to only see the first 4 sheets. "After the Sale" is just the heading - that department should not be able to see our financial information - only able to see the sheets for quoting purposes.
I'm thinking the checkboxes arent going to work or going to be too difficult for me.
Maybe this option - if the person types in the password when they first open the workbook, they should only be able to view the sheets per that password.

Maybe something like this.

Create a userform with a label, a textbox and two commandbuttons. The label would caption something like 'Input password", and the buttons would be OK and Cancel (maybe even don't. On the textbox, set the Passwordchar property to *.

Add this code to the OK button


Private Sub CommandButton1_Click()
Dim arySheets
Dim i As Long
Dim sh As Worksheet
Dim fVisible As Boolean
With TextBox1
If .Text = "finance" Then
arySheets = Array("Price List", "Routing Slip", "Lease Option", "Summary Sheet")
ElseIf .Text = "hr" Then
arySheets = Array("Sheet1", "Sheet3")
'some other values in arysheets
Else
MsgBox "Invalid password"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Exit Sub
End If
End With
For Each sh In ActiveWorkbook.Worksheets
fVisible = False
For i = LBound(arySheets) To UBound(arySheets)
If sh.Name = arySheets(i) Then
fVisible = True
Exit For
End If
Next i
If fVisible Then
Worksheets(arySheets(i)).Visible = xlSheetVisible
Else
sh.Visible = xlSheetVeryHidden
End If
Next sh
End Sub

and on the Cancel button, close the workbook.

Add a workbook_open event procedure to run the form.

Oh, one other thing. Add a dummy sheet, perhaps with a Mission statement or something, and make this the only visible sheet on workbook close. It also ensure always one visible sheet.

[spaz9876]

I just tried http://www.vbaexpress.com/kb/getarticle.php?kb_id=380 and it works but I think I will try the next reply to see which one I like better.
Thank you all for helping me!
If anyone needs help with Access, let me know - I am a wizz at that!