PDA

View Full Version : Run-time error "438"



Haaris
05-04-2018, 10:53 AM
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
05-04-2018, 01:30 PM
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
05-04-2018, 01:39 PM
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
05-04-2018, 01:58 PM
I attached the sample excel workbook that i'm working on... see if this helps

User name - User1
User Pass - u1pass



22179

Paul_Hossler
05-04-2018, 02:21 PM
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
05-04-2018, 02:35 PM
'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
05-07-2018, 11:19 AM
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 :crying: - 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
05-07-2018, 01:35 PM
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
05-07-2018, 02:50 PM
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
05-30-2018, 12:01 PM
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
04-23-2019, 03:25 AM
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
04-29-2019, 01:32 AM
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)