Consulting

Results 1 to 12 of 12

Thread: Run-time error "438"

  1. #1
    VBAX Regular
    Joined
    Apr 2018
    Posts
    6
    Location

    Run-time error "438"

    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
    Attached Images Attached Images

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Apr 2018
    Posts
    6
    Location
    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
    Last edited by Haaris; 05-04-2018 at 01:54 PM.

  4. #4
    VBAX Regular
    Joined
    Apr 2018
    Posts
    6
    Location

    Post Run-time error "438" workbook attachment

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

    User name - User1
    User Pass - u1pass



    Book1 - project.xlsm

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Last edited by Paul_Hossler; 05-04-2018 at 02:35 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
           '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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Apr 2018
    Posts
    6
    Location
    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.

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Regular
    Joined
    Apr 2018
    Posts
    6
    Location
    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

  11. #11
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    1
    Location
    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
    Attached Images Attached Images

  12. #12
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    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)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •