Macor Help

[max8719]

Hi Guys,

Below is a macro for a user form I have in my workbook however I am struggling to add more viewable sheets per user.

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean

bOK2Use = False
bError = True
If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
bError = False
Select Case txtUser.Text
Case "lucy"
Sheets
sSName = "Stoke" I would like to add more sheets here
If txtPass.Text <> "lucy" Then bError = True
Case "rikin"
sSName = "Nottingham"
If txtPass.Text <> "rikin" Then bError = True
Case "rob"
sSName = "Birmingham"
If txtPass.Text <> "rob" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If
End Sub


Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close (True)
End If
End Sub

 

[dmt32]

Hi,
Rather than hard code each user & their sheet name(s) why not create a table on worksheet that you can add to as required. Also, if you check each user against their network username this would negate the need to enter password.

Try following & see if helps you.

1 – add a worksheet & name it “Users”
1-1 In column 1 (A) enter each users Network UserName
1-2 In column 2 (B) enter True if user has admin access (view all sheets) or False
1-3 In column 3 (C) onward enter each sheet name user has access to

2 – The 1st Sheet always remains visible ensure that it is the Home sheet


3 – Place following code in the Thisworkbook code page.

 Private Sub Workbook_Open()   
    Dim sh As Integer, c As Integer
    Dim rng As Range
    Dim UsersName As String, ws As String
    Dim m As Variant
    Dim wsUsers As Worksheet
    
    'hide all but first sheet
    For sh = 2 To Worksheets.Count
        Worksheets(sh).Visible = xlSheetVeryHidden
    Next sh
    
    Set wsUsers = Worksheets("Users")
    Set rng = wsUsers.Range(wsUsers.Range("A1"), wsUsers.Range("A" & wsUsers.Rows.Count).End(xlUp))
    
    UsersName = Environ("USERNAME")
    
    m = Application.Match(UsersName, rng, False)
    
    On Error GoTo myerror
    If Not IsError(m) Then
        If CBool(wsUsers.Cells(Val(m), 2).Value) Then
        'admin user
        For sh = 2 To Worksheets.Count
            Worksheets(sh).Visible = xlSheetVisible
        Next sh
        
        Else
        'show users sheet(s)
        c = 3
        Do
            ws = CStr(wsUsers.Cells(Val(m), c).Text)
            If Len(ws) = 0 Then Exit Do
            Worksheets(ws).Visible = xlSheetVisible
            c = c + 1
        Loop
        
        End If
        
    Else
    
        MsgBox "You Do Not Have Authorised Access To This Workbook.", 16, "No Access"
        
    End If
    
myerror:
If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

When workbook opened, code first ensures all but Home page sheet are hidden & then unhides sheets based on users access – If Admin value (column C) set True then all sheets will be visible otherwise, only sheets listed in Column C onward will be shown.

If you want to run the code from your userform, place code in standard module & rename it as required.

Hope Helpful

 

[max8719]

I seem to keep getting duplication conflictions when running the code, and not sure how to run it from the user form.......I know amateur.

I really don't mind hard coding as the code was working, I just need to be able to add more visible sheets to the user (highlighted in original post)

I like the idea of a user table but again the term amateur comes to mind.

Thanks in advance for any feedback

 

[dmt32]

I seem to keep getting duplication conflictions when running the code, and not sure how to run it from the user form.......I know amateur.

I really don't mind hard coding as the code was working, I just need to be able to add more visible sheets to the user (highlighted in original post)

I like the idea of a user table but again the term amateur comes to mind.

Thanks in advance for any feedback

Solution should work fine but If you already have a Workbook_Open event then you need to place code within it or call it from there as another procedure.

e.g.

Private Sub Workbook_Open()
'your existing code
'
'




    ShowSheets
End Sub

where ShowSheets is the renamed procedure in a standard module.

If you are able to place copy of your workbook in a dropbox & place link to it here, may be able to assist further.

Dave

 

[max8719]

Hi Dave thanks for you support. Please see the link below.

https://www.dropbox.com/s/2hm32iv7z11sktx/Branch Network Lead Sheet.dropbox.xlsm?dl=0

I have removed the macro from "thisworkbook" to stop frustration when opening the document. However I have listed below just in case.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim w As Worksheet
Dim bSaveIt As Boolean


bSaveIt = False
For Each w In Worksheets
If w.Visible Then
Select Case w.Name
Case "lucy"
w.Protect ("lucy")
w.Visible = False
bSaveIt = True
Case "rikin"
w.Protect ("rikin")
w.Visible = False
bSaveIt = True
End Select
End If
Next w
If bSaveIt Then
ActiveWorkbook.CustomDocumentProperties("auth").Delete
ActiveWorkbook.Save
End If
End Sub


Private Sub Workbook_Open()
UserForm1.Show
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Main" Then
If Sh.Name <> ActiveWorkbook.CustomDocumentProperties("auth").Value Then
Sh.Visible = False
MsgBox "You don't have authorization to view that sheet!"
End If
End If
End Sub

 

[max8719]

Hello Dave,


You code works great !! Thank you very much. I have tried it out and it works. I like the idea of it being based on the network name and that it is a table format.


Just a couple of questions though if you can help please.


1. If I wanted to make this a shared document will this be compatible? As I would like users to open the document and make changes simultaneously.


2. Is there a way of enabling the macros and content automatically when the document opens?


Many Thanks.

 

[dmt32]

Hello Dave,


You code works great !! Thank you very much. I have tried it out and it works. I like the idea of it being based on the network name and that it is a table format.


Just a couple of questions though if you can help please.


1. If I wanted to make this a shared document will this be compatible? As I would like users to open the document and make changes simultaneously.


2. Is there a way of enabling the macros and content automatically when the document opens?


Many Thanks.

Hi,
Glad solution worked OK for you.
With regards to your other points:

1- My personal view (and probably that of others here) is that you should avoid setting a workbook as shared – apart from loss of some functionality, a shared workbook is unlikely to perform in manner you want & you risk loss of workbook due to possible corruption & edit conflicts.
There are alternative workable solutions you could consider like creating templates for each of your users & these read / write from a central database (Access or could be another workbook) or, have workbooks using Web Excel (available in SharePoint and OneDrive) which should I understand, prove more reliable but I personally have no experience of setting up the latter – perhaps others here may be able to give guidance.


2 – You cannot force users to enable Macro’s by enabling them programmatically, it’s part of EXCELS security feature & gives users a warning that workbook contains macros.
What you can do though is control how your workbook is saved & if it is opened without macros being enabled, a warning sheet only is displayed.
Have a look here for examples you could try:

Forcing Your Clients to Enable Macros | Bacon Bits

VBA Express : Excel - Force users to enable macros in a workbook

Dave