object required error

[bebe1279]

Hi everyone

I'm working on a project and am stuck on how to express something in vba.
Here is the code that I'm trying to run to see if it will, first run and second, give me what I'm looking for.
What I'm trying to express is: if the value of sn is a worksheet name within the workbook then make that sheet visible otherwise don't show it.

For Each sn In ua
                If sn.Value = Worksheet.Name Then
                    Worksheet.Visible = True
                    Else: Worksheet.Visible = False

When I run/debug the code it give me an object required error message on the following line

If sn.Value = Worksheet.Name Then

What am I doing wrong and how can I fix it.

 

[MARK858]

You might have to tell us what is sn and show us where you define it (in other words show the full code) but basically if sn was a range then you would do something like...

Sub testit()
    Dim ws As Worksheet, sn As Range
    Set sn = Cells(1, "A")
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            If sn.Value = ws.Name Then
                ws.Visible = True
            Else: ws.Visible = False
            End If
        End If
    Next
End Sub

 

[bebe1279]

Here is the code

Private Sub CommandButton1_Click()
Dim user As String
Dim pass As String
Dim adminlog As Range
Dim userlog As Range
Dim r As Range
Dim Ws As Worksheet
Dim ac As Worksheet
Dim ua As Range
Dim sn As Range
Dim flag As Boolean
'assigning value to variables
user = Usertxtbox.Value
pass = Passtxtbox.Value
Set ac = Sheets("admin controls")
Set adminlog = ac.Range("C2:C6")
Set userlog = ac.Range("F2:F17")
'set all sheets to visible
For Each Ws In ActiveWorkbook.Worksheets
   Ws.Visible = True
Next Ws
        
'validating username & password of admins & set access to full
For Each r In adminlog
    If user = r.Value And pass = r.Offset(0, 1).Value Then
        Unload Me
        ac.Select
        Exit Sub
    End If
Next
'validate username & password for restricted users & set access levels
For Each r In userlog
    If user = r.Value And pass = r.Offset(0, 1).Value Then
       
       'lookup and store allowable sheets in var
        Set ua = ac.Range(r.Offset(0, 2), r.Offset(0, 7))
                
             'assign access level for user
             For Each sn In ua
                 If sn.Value = Worksheet.Name Then
                 Worksheet.Visible = True
                 Else: Worksheet.Visible = False
                
                        'close login form
                        Unload Me
                        End If
                        Next sn
    End If
Next
End Sub

Appreciate the help

 

[MARK858]

Worksheet isn't defined as a variable and so it isn't being recognised as an object (which is causing the error) and you shouldn't use the name Worksheet as one anyway (it probably won't let you use it anyway).

I am also a bit confused what you are trying to do. It appears you are looping through a range of strings and testing it against a single worksheet only.
Is that what you intend?
If yes then what worksheet?
If no then what are you intending?

 

[bebe1279]

I have a userform setup as a login form that will start assoon as the workbook is opened. There are two groups of users that will be ableto use this workbook, an admin or a regular user. The admins have full accessto the entire workbook. The regular users will be restricted based on whatsheets they need to complete their tasks. This info is stored in a sheet called “admincontrols”. I’ve included a link to a sample of the admin sheet below.

https://www.dropbox.com/s/u1i1r08xzoig98y/Project screen shot.PNG?dl=0

What I want the code to do is:
Check username & password against admin users list (in admin controls sheet, green table), if match is found, enable all sheets and close login form. If no match is found, then
Check username & password against regular user list (in admin controls sheet, red table with data validation), if match found, then enable only the sheet found to the right of that user’s login info (columns H through M) and close login form.
The table in “A” are the names of all the sheets in the workbook.

 

[MARK858]

Then at the very least it seems you need to loop through the worksheets....

    For Each sn In ua
        For Each ws In ActiveWorkbook.Worksheets
            If sn.Value = ws.Name Then ws.Visible = True
                'close login form
                Unload Me
         Next ws
    Next sn

and hide all the worksheets bar your admin control before the code above.

 

[bebe1279]

I'm trying to run your suggestion to see if it will work but now it's giving me an object variable not set error. Here is the current code with your suggestion.

Private Sub CommandButton1_Click()
Dim user As String
Dim pass As String
Dim adminlog As Range, userlog As Range
Dim r As Range
Dim Ws As Worksheet, ac As Worksheet
Dim ua As Range, sn As Range
Dim flag As Boolean
'assigning value to variables
user = Usertxtbox.Value
pass = Passtxtbox.Value
'set object variables
Set ac = Sheets("admin controls")
Set adminlog = ac.Range("C2:C6")
Set userlog = ac.Range("F2:F17")
'user access sheet list range
Set ua = ac.Range(r.Offset(0, 2), r.Offset(0, 7))

'set all sheets to visible
For Each Ws In ActiveWorkbook.Worksheets
   Ws.Visible = True
Next Ws
        
'validating username & password of admins & set access to full
For Each r In adminlog
    If user = r.Value And pass = r.Offset(0, 1).Value Then
        Unload Me
        ac.Select
        Exit Sub
    End If
Next
'set all sheets to invisible
For Each Ws In ActiveWorkbook.Worksheets
   Ws.Visible = False
Next Ws
'validate username & password for restricted users & set access levels
For Each r In userlog
    If user = r.Value And pass = r.Offset(0, 1).Value Then
       For Each sn In ua
           For Each Ws In ActiveWorkbook.Worksheets
               If sn.Value = Ws.Name Then Ws.Visible = True
               Unload Me
               
           Next Ws
       Next sn
      
                
             
    End If
Next
End Sub

The error is happening here

Set ua = ac.Range(r.Offset(0, 2), r.Offset(0, 7))

 

[MARK858]

That is because r doesn't exist when you reach the line.

 

[bebe1279]

Thanks for all your help.
I've been playing around with the code a bit and finally got it to work the way I want except for one thing. Maybe you could suggest something or point me in the right direction.
So, here's my code

Private Sub CommandButton1_Click()
Dim r As Range
Dim adminlog As Range, userlog As Range
Dim user As String, pass As String
Dim ua As Range '(user access)
Dim ac As Worksheet 'look into if this is right type
Dim sn As Range '(sheet name) right type?
Dim ws As Worksheet 'find out, right type?
Dim x As Variant
user = Usertxtbox.Value
pass = Passtxtbox.Value
Set ac = Sheets("Admin Controls")
Set adminlog = ac.Range("C2:C6")
'check that user and pass fields are not blank
If user <> "" And pass <> "" Then
    
'Admin login process
    
    'loop through admin table values
    For Each r In adminlog
        'Validate username & password
        If user = r.Value And pass = r.Offset(0, 1).Value Then
           'Show all sheets
           For Each ws In ActiveWorkbook.Worksheets
               ws.Visible = True
               ac.Activate
           Next
                'close userform
                Unload Me
                Exit Sub
        End If
    Next
End If
'User login process
Set userlog = ac.Range("F2:F17")
'loop through user table values
For Each r In userlog
    'Validate username & password
    If user = r.Value And pass = r.Offset(0, 1).Value Then
       'set user access, ueer table H-M values
       Set ua = ac.Range(r.Offset(0, 2), r.Offset(0, 7))
           'Loop through user access values
           For Each sn In ua
              'Transfer value to variant
               x = sn.Value
                'loop through worksheets
                For Each ws In ActiveWorkbook.Worksheets
                     If x = ws.Name Then
                        ws.Visible = True
                     Else:
                        ws.Visible = False
                     End If
                Next ws
           Next sn
                        'Activate the Dashboard sheet
                        Sheets("Dash").Activate
                        'close userform
                        Unload Me
                     
    End If
Next
End Sub

The problem I'm having is that after the last if statement where it is setting the ws visibility to true or false, I need it to go to the For each sn loop, but it of course goes to the For each ws loop. Is there a way I can tell it where to go after it ends that final if statement?

 

[MARK858]

The statement

Exit For

leaves a For loop.