CODEWITHSUNDEEP
excelvba
Zaid Kalthoum
Zaid Kalthoum

Reputation: 81

Unable to set the hidden property of the range class run time error '1003'

I have code in this module:

Sub HideSalTable()

User = Worksheets("log").Range("R1").Value
If User = ThisWorkbook.Worksheets("SSSSSS").Range("za1").Value Then
Columns("S:AA").EntireColumn.Hidden = True

ElseIf User = ThisWorkbook.Worksheets("SSSSSS").Range("za3").Value Then
Columns("S:AA").EntireColumn.Hidden = False

ElseIf User = ThisWorkbook.Worksheets("SSSSSS").Range("za4").Value Then
Columns("S:AA").EntireColumn.Hidden = False

End If

End Sub

I have a button to redirect me to ThisWorkbook.Worksheets("SSSSSS") with this code:

Private Sub Change_SSSSSS_Button_Click()

 Dim pass1 As String
  Dim pass2 As String
  pass1 = ThisWorkbook.Worksheets("SSSSSS").Range("za3").Value
   pass2 = ThisWorkbook.Worksheets("SSSSSS").Range("za4").Value
  Dim Inp
    Dim lTries As Long



    lTries = 1
    Do
        Inp = InputBoxDK("enter password", "Zmhnk")
        If Inp = "" Or Inp = vbCancel Then Exit Sub          '* Cancel button pressed or nothing entered
        If Inp = (pass1) Or Inp = (pass2) Then
            Exit Do
        End If
        lTries = lTries + 1
        If lTries > 4 Then
            MsgBox "Error", vbInformation, "Zmhnk"
            Exit Sub
        Else
            If MsgBox("try again", vbYesNo, "error_Zmhnk") = vbNo Then Exit Sub
        End If
    Loop

 Application.ScreenUpdating = False

 Sheets("SSSSSS").Visible = True
 Sheets("SSSSSS").Activate

 Application.ScreenUpdating = True

End Sub

The problem is when the user presses the button with the 2nd code I face an error and I don't know why.

The error:

Unable to set the hidden property of the range class run time error '1003'

Upvotes: 8

Views: 43174

Answers (3)

Boketto
Boketto

Reputation: 885

I had a similar issue (only the error code was 1004, but the error message was the same). What solved the issue at my Excel sheet was to remove a comment which was within the range that I tried to hide. It seems like comments are not allowed within the range that should be hidden.

Upvotes: -1

Rtronic
Rtronic

Reputation: 673

when you have the control from the Form there is no Problem
but if you have it from the worksheet itself then it works actually but with Error:1004
so just use ( On Error Resume Next) 

Private Sub ComboBox1_Change()
Dim wsMon As Worksheet
Set wsMon = ThisWorkbook.Worksheets("Montag")
On Error Resume Next
Select Case ComboBox1.ListIndex
       Case 0
            xHide (False)
            wsMon.Rows("12:25").EntireRow.Hidden = True
            xHide (True)
       Case 1
            xHide (False)
            wsMon.Rows("12:25").EntireRow.Hidden = False
            wsMon.Rows("19:25").EntireRow.Hidden = True
            xHide (True)
       Case 2
            xHide (False)
            wsMon.Rows("12:25").EntireRow.Hidden = False
            xHide (True)
End Select

End Sub

xHide is a Boolean Function : true

Application.ScreenUpdating = True
Application.DisplayAlerts = True

or False

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149305

Two things

1) You have not fully qualified your range. I understand that you are getting redirected but this is much safer.

Columns("S:AA").EntireColumn.Hidden = True

Change it to 

ThisWorkbook.Sheets("SSSSSS").Columns("S:AA").EntireColumn.Hidden = True

2) I believe your worksheet is protected. You have to unprotect it. You can do that as follows

ThisWorkbook.Sheets("SSSSSS").Unprotect "myPassword"

Upvotes: 14

Related Questions