I have 18 departments and each has 1

[nberryman]

I have 18 departments and each has an Absence workbook of 13 sheets, one per month and a summary. I have just ammended the sheets (13 sheets X 18 workbooks)and thanks to Dave Wilson on Tek-Tip managed it via VB making it less painful.

As I am thinking ahead to next year is there any way I can have all departments data in one workbook and use a form to get the user to log in and apply a filter form the login form to show just his or her department?

It would also need a password to prevent them from seeing other departments data but they are not "Power Users" so it needn't be bullet proof.

So what I want to do is

On open display form, select dept from combo box and type password into a text box. Check the dept name against a password on a hodden sheet.

If ok

Apply filter to all sheets (April - March and Summary where dept = combo box selection

If password is wrong exit Excel

I can do this in Access but they don't us that so I am stuck with Excel and my Vb is weak


Thanks in advance (Again) Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[Loomah]

this is really rough but it's a lunch job
it's the bones on which to build

Option Explicit
Public strPW As String

Private Sub cmdGetPW_Click()
Dim strResp As String
Me.Hide
strResp = InputBox("enter password", "Password")
If strResp = strPW Then
hidesheets
Else:
MsgBox "wrong password"
End If
End Sub

Private Sub lstDept_Click()
Select Case Me.lstDept.Text
    Case "Dept 1"
        strPW = "pw1"
    Case "Dept 2"
        strPW = "pw2"
    Case "Dept 3"
        strPW = "pw3"
    Case "Dept 4"
        strPW = "pw4"
End Select
End Sub

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To 4
    Me.lstDept.AddItem ("Dept " & i)
Next i
End Sub


Sub hidesheets()
ActiveWorkbook.Unprotect
Select Case strPW
    Case "pw1"
        Sheets(Array("Sheet8", "Sheet7", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select
        ActiveWindow.SelectedSheets.Visible = False
    Case "pw2"
        Sheets(Array("Sheet1", "Sheet2", "Sheet7", "Sheet8", "Sheet5", "Sheet6")).Select
        ActiveWindow.SelectedSheets.Visible = False
    Case "pw3"
        Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet7", "Sheet8")).Select
        ActiveWindow.SelectedSheets.Visible = False
    Case "pw4"
        Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select
        ActiveWindow.SelectedSheets.Visible = False
End Select
ActiveWorkbook.Protect

End Sub

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[nberryman]

Thanks Loomah, you knocked that up in your lunchtime?

Very impressed

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com