时间戳

首页 > 解决方案 > UserForm1 没有被检测为普通用户，但在这样登录时被检测为管理员

excel - UserForm1 没有被检测为普通用户，但在这样登录时被检测为管理员

问题描述

我已经设置了一些权限，所以普通用户看不到excel上的所有工作表，只有管理员可以查看所有工作表（下面的代码）当我以管理员身份登录并单击“主管”页面上设置的命令按钮时，它工作正常。如果我使用普通用户执行此操作，则会显示以下错误

对象变量或未设置对象

并Load UserForm1在下面的macro_click 上标记。我找不到什么是错的，或者什么是缺失的。我需要一个大手，因为前几天它工作得很好，但现在它坏了......

带有宏的按钮设置在显示按钮的页面上。以及 userform1 上的登录代码。

Private Sub btnEntrar_Click()
    'Declare the variables
    Dim AddData As Range, AddData2 As Range, AddData3 As Range, Current As Range
    Dim Code As Variant
    Dim PName As Variant, AName As Variant, UName As Variant
    Dim ws As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, ws6 As Worksheet
    Dim result As Integer
    Dim TitleStr As String
    Dim msg As VbMsgBoxResult
    Dim LogsUsuarios As Worksheet


    'Set the pages variables
    Set ws = Worksheets("Inicio")
    Set ws2 = Worksheets("Supervisores")
    Set ws3 = Worksheets("Registros")
    Set ws4 = Worksheets("Ingreso")
    Set ws5 = Worksheets("LogsUsuarios")
    Set ws6 = Worksheets("LookupList")
    Set LogsUsuarios = Hoja6

    'Variables
    user = Me.txtUser.Value
    Code = Me.txtPass.Value
    TitleStr = "Password check"
    result = 0
    Set Current = ws4.Range("M3")

    'Error handler
    On Error GoTo errHandler:

    'Destination location for login storage
    Set AddData = ws5.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
    Set AddData2 = ws5.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0)
    Set AddData3 = ws5.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0)

'Check the login and passcode for the Administrator
    If user <> "" And Not IsNumeric(user) And Code <> "" Then
        For Each AName In ws4.Range("R4:R6")
            'If AName = Code Then 'Use this for passcode text
            If AName = Code And AName.Offset(0, -1) = user Then ' Puede usar texto Y números
                MsgBox "Inicio como Administrador de: " & user

                'record user login
                AddData.Value = user
                AddData.Offset(0, 1).Value = Now
                'Add usernmae to the worksheet
                Current.Value = user
                'Change variable if the condition is meet
                result = 1
                'Unload the form
                ws.Visible = True   'Cambiar para que lo tome de la tabla de permisos y no manual como ahora, usar el cod de abajo
                ws2.Visible = True
                ws3.Visible = True
                ws4.Visible = True
                ws5.Visible = True
                ws6.Visible = True
                Unload Me

            'sort the Registros by "Último Ingreso"
            Hoja6.Select
            With LogsUsuarios
                .Range("C4:C1000").Sort Key1:=Range("C4"), Order1:=xlDescending, Header:=xlGuess
            End With
            ws4.Select 'Dirigirme a la pagina "Ingreso"
                Exit Sub
            End If
        Next AName
    End If

        'Check user login with loop
        If user <> "" And Not IsNumeric(user) And Code <> "" Then
            For Each UName In ws4.Range("R11:R19")
                'If UName = Code Then 'Use this for passcode text
                If UName = Code And UName.Offset(0, -1) = user Then ' Puede usar texto Y números
                    MsgBox "Ingreso satisfactorio para: " & user

                'record user login
                If user = "MCARULO" Then
                AddData2.Value = user
                AddData2.Offset(0, 1).Value = Now

                'record user login
                ElseIf user = "BRAVO" Then
                AddData3.Value = user
                AddData3.Offset(0, 1).Value = Now

                Else
                AddData.Value = user
                AddData.Offset(0, 1).Value = Now
                End If

                'Add user to the worksheet
                Current.Value = user

                Set PName = Hoja2.Range("E:E").Find(What:=user, LookIn:=xlValues)

                'unhide worksheet for user
                If PName.Offset(0, 2) = "Inicio" Then
                'Set ws = Worksheets(PName.Offset(0, 2).Value)
                ws.Visible = True
                End If

                If PName.Offset(0, 3) = "Supervisores" Then
                'Set ws2 = Worksheets(PName.Offset(0, 3).Value)
                ws2.Visible = True
                ws2.Select 'Se cambia la pantalla a este para los Supervisores
                'Inicio Automático el Multi
                'InicioSuperv
                End If

                If PName.Offset(0, 4) = "Registros" Then
                'Set ws3 = Worksheets(PName.Offset(0, 3).Value)
                ws3.Visible = True
                End If

                If PName.Offset(0, 5) = "Ingreso" Then
                'Set ws4 = Worksheets(PName.Offset(0, 4).Value)
                ws4.Visible = True
                ws4.Select 'Se cambia la pantalla a este para RRHH
                End If

                If PName.Offset(0, 6) = "LogsUsuarios" Then
                'Set ws5 = Worksheets(PName.Offset(0, 5).Value)
                ws5.Visible = True
                End If

                If PName.Offset(0, 7) = "LookupList" Then
                'Set ws5 = Worksheets(PName.Offset(0, 5).Value)
                ws6.Visible = True
                End If

                'show sheet tab if hidden
                ActiveWindow.DisplayWorkbookTabs = True

                'Change variable if the condition is meet
                result = 1

                'Unload the form
                Unload Me

                'sort the Registros by "Último Ingreso"
                Hoja6.Select
                With LogsUsuarios
                .Range("C4:C1000").Sort Key1:=Range("C4"), Order1:=xlDescending, Header:=xlGuess
                End With

                'Esconde la Hoja Nuevamente
                ws5.Visible = xlSheetVeryHidden
                Exit Sub
            End If
        Next UName
    End If

Private Sub cmdMacro2_Click() 'Inicia en el Inicio Gral

Load UserForm1: UserForm1.MultiPage1.Value = 0: UserForm1.Show vbModeless

If UserForm1.MultiPage1.Value = 0 Then
'Desactiva los botones del inicio que no correspondan para los supervisores}
UserForm1.inicioNR.Enabled = False
UserForm1.inicioBD.Enabled = False

'Desabilita las páginas que no pueden utilizar los supervisores
'UserForm1.MultiPage1.Pages(0).Enabled = False
UserForm1.MultiPage1.Pages(1).Enabled = False
UserForm1.MultiPage1.Pages(2).Enabled = False

End If
End Sub

标签： excelvba

解决方案

我添加了存储 userform1 的工作表，现在看起来一切正常。这是最终代码：

Private Sub cmdMacro2_Click() 'Inicia en el Inicio Gral

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Registros")

    With Registros
        Load UserForm1:
        UserForm1.MultiPage1.Value = 0:
        UserForm1.Show vbModeless

        If UserForm1.MultiPage1.Value = 0 Then
            'Desactiva los botones del inicio que no correspondan para los supervisores}
            UserForm1.inicioNR.Enabled = False
            UserForm1.inicioBD.Enabled = False

            'Desabilita las páginas que no pueden utilizar los supervisores
            'UserForm1.MultiPage1.Pages(0).Enabled = False
            UserForm1.MultiPage1.Pages(1).Enabled = False
            UserForm1.MultiPage1.Pages(2).Enabled = False

        End If
    End With
End Sub

推荐阅读