Run-time error "438"

[Haaris]

Hi guys, i'm new in this form and vba. I'm coding an excel workbook with multiple sheets. trying to set password and user name for each worksheet in order to keep confidentiality and staff accessing other peoples sheet. I coded the form and now, i'm getting a run-time error "438". I've added the screenshot and the location where the error is. I can provide additional details if needed. Looking to hear from you all at your earliest.

Thank you in advance

[Paul_Hossler]

1. The screen shot is unreadable. Use the [#] icon to add code tags and paste the macro between them

2. Best is to add a simple workbook with the macro(s) that show the problem since what causes an error for you might not cause an error for me if I have to re-create a workbook just to test

3. What is a error 438? There are lots of different Excel Error codes, so people might not want to look it up

4. Mark the line that is causing an error.

[Haaris]

Thanks Paul for the reply, here part of the code where the error is:

        Sheet1(User1).Visible = True --------> this is where the error shows :(
        Sheet2(User2).Unprotect (txtPass.Text)
        Sheet3(User3).Activate

        bOK2Use = True
        Unload UserForm1
    
    End If
    
End Sub

The run-time error code "438" says - Object doesn't support this property or method

[Haaris]

I attached the sample excel workbook that i'm working on... see if this helps

User name - User1
User Pass - u1pass



Book1 - project.xlsm

[Paul_Hossler]

1. I added Option Explicit and when I complied (Alt-D) I got "Variable Not Defined" on the User1 line

Option Explicit

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 "User1"
                sSName = "u1sheet"
                If txtPass.Text <> "u1pass" Then bError = True
            Case "User2"
                sSName = "u2sheet"
                If txtPass.Text <> "u2pass" 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
   
        Sheet1(User1).Visible = True
        Sheet2(User2).Unprotect (txtPass.Text)
        Sheet3(User3).Activate
        bOK2Use = True
        Unload UserForm1
    
    End If
    
End Sub
Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub

2. 'Sheet1' is the Code Name for that sheet. It can also have a .Name like on the tabs at the bottom of the grid. I'm not sure about the User1, User2, and User3 so I can't suggest anything more specific

        Sheet1(User1).Visible = True
        Sheet2(User2).Unprotect (txtPass.Text)
        Sheet3(User3).Activate

The way to refer to worksheets is like one of these

Dim User1 as String

Sheet1.Visible = True
Worksheets ("User1").Visible = True

User1 = "User1"
Worksheets (User1).Visible = True

3. I'm guessing you intended

        Worksheets("User1").Visible = True
        Worksheets("User2").Unprotect (txtPass.Text)
        Worksheets("User3").Activate

but when I try to run it further, I get a "1004 unable to set the visible property of the worksheet class" so maybe the sample is corrupted

[SamT]

       'Sheet1(User1).Visible = True 'Error
                      'True = 0, 0 = xlSheetHidden.., False = -1, -1 = xlSheetVisible... Also an error
        Sheet1(User1).Visible = xlSheetVisible
        Sheet2(User2).Unprotect (txtPass.Text)
        Sheet3(User3).Activate

Sheet1 is visible, Sheet2 is unprotected, and Sheet3 is Activated... Makes no sense to me
Activating a hidden sheet will either do nothing or it will raise an error.

If User1, 2, and 3 are Tab Names, they should be inside quotes.

If Sheet1 is the Codename and "user1" is the tab Name then Sheet1("User1") is a syntax error

[Haaris]

Thank you guys, I really appreciate your comments. I guess I figured out the UserForm1 where the 438 error was after creating a new test workbook.

Now I'm facing another run-time error 1004 - Method "visible" of 'object_worksheet' failed - this is at the actual Workbook where the 2nd macro is applied, the code is:

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 "Abdi"
                    w.Protect ("u1pass")
                    w.Visible = False   ---------> this is where the error is
                    bSaveIt = True
                Case "Anisa"
                    w.Protect ("u2pass")
                    w.Visible = False ​---------> this is where the error is
                    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

The error accord after a user saves and try to close the sheet.

[SamT]

Show the Sheet = Sheets("Sheet1").Visible = xlSheetVisble = -1

Hide the sheet = xlSheetHidden = 0

Really hide the sheet do the User cant use the Excel Menu to show it = xlSheetVeryHidden = 1

The error accord after a user saves and try to close the sheet.

That's because that is the first time that code is ran.

After any bit of coding, use the VBA Menu Item "Debug" >> Compile to find most of those errors without running the code.

Also, putting Option Explicit at the top of all your code pages will show a lot of errors before you even compile.

[Paul_Hossler]

I'm guessing that when that runs, thee is only one visible sheet and a WB needs to have at least one sheet visible

Unhide that other sheet first, and then hide this one, or Add a 'Billboard' sheet (like 'This is my wonderful workbook' with pictures) and show that before hiding the rest

                Case "Abdi"
                    w.Protect ("u1pass")
                    w.Visible = False   ---------> this is where the error is
                    bSaveIt = True
                Case "Anisa"
                    w.Protect ("u2pass")
                    w.Visible = False ​---------> this is where the error is
                    bSaveIt = True

[Haaris]

Hi, what is the possibility of making this workbook, multiuser? any suggestion? I've 2 codes, one for form and another one for the workbook itself

[engtobe]

Hey everyone. I'm new here. I have a question related to error 438 (the run-time error code '438' says - Object doesn't support this property or method). Here is my code:


Option Explicit

Private Sub Main()
Dim ru As Double
ru = CDbl(InputBox("Unesite radijus unutrasnje kruznice ", "Unos radijusa ", 25))
Dim s As String
Dim rv As Double
rv = CDbl(InputBox("Unesite radijus vanjske kruznice "))
Dim n As Integer
n = CInt(InputBox("Unesite broj kruznica "))
s = InputBox("Unesite centar (format x,y,z)", "Unos centra ", "1,1,0")
Dim v As Variant
v = Split(s, ",")
Dim i As Integer
Dim centar(0 To 2) As Double

For i = 0 To UBound(v)
centar(i) = CDbl(v(i))
Next i

Dim c As AcadCircle
Set c = ThisDrawing.ModelSpace.AddCircle(centar, ru)
c.Update

Dim centarv As Variant

Dim PI As Double
PI = 4# * Math.Atn(1#)
Dim ugao As Double
ugao = (2# * PI) / n

For i = 0 To n - 1
centarv = ThisDrawing.Utility.PolarPoint(centar, ugao * i, rv + ru)
c = ThisDrawing.ModelSpace.AddCircle(centarv, rv)
Next i

MsgBox "Nacrtano"

End Sub

[大灰狼1976]

Hi engtobe!
I don't know which line of code has error "438". But the following code must add "set".

set c = ThisDrawing.ModelSpace.AddCircle(centarv, rv)

Or don't use c

ThisDrawing.ModelSpace.AddCircle(centarv, rv)