+ Reply to Thread
Results 1 to 3 of 3

Error on Sheet Name Change: "Unable to set the Hidden Property of the Range Class"

  1. #1
    Registered User
    Join Date
    12-02-2012
    Location
    Silver Bay, MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Error on Sheet Name Change: "Unable to set the Hidden Property of the Range Class"

    This is partial code (there are additional case statements) which errors out and then crashes file when I change the tab from say 2013 to 2014. Any help will be greatly appreciated.

    This code refers to a a combobox list and associated range names on sheet1 named '2013'. Sheet1 is designed to hide designated columns or reveal all columns. Users select the Program from the combo box which hide all other program columns. Everything works perfectly until I attempt to change the sheet name which reflects the year being planned for. There is only one sheet in workbook.

    Private Sub cboGotoProgramsSelection_Change()
    ''Hides programs columns, except for selected Program Name. Case arguments reference named ranges _
    in worksheet. ComboBox list is located at "C220". Note: program names (shown in E2:P2) change _
    so they are linked in ComboBox list to names that do not change, i.e.: numberzero, _
    numberone, etc. NumberZero is located in cell D2, labled "SHOW ALL" and text set to white.
    Dim Value As String

    Application.ScreenUpdating = False

    Select Case cboGotoProgramsSelection.Value
    Case "NumberZero"
    Sheet1.Columns("E:HV").EntireColumn.Hidden = False 'Unhides columns <--Crashes here when changing sheet name. Code name is Sheet1. Sheet name is 2013.
    Range("C2").Select 'Places cursor at "C2"

    Case "NumberOne"
    Sheet1.Columns("E:HV").EntireColumn.Hidden = False 'Unhides columns
    Sheet1.Columns("F:Q").EntireColumn.Hidden = True 'Hides selected columns
    Range("C2").Select

    End Select
    Application.ScreenUpdating = True

    End Sub

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Error on Sheet Name Change: "Unable to set the Hidden Property of the Range Class"

    Hi,

    as you are using the codename (not the name displayed on the tabs) of the worksheet it shouldn't crash because of a worksheet.name change, but you have to make sure that Sheet1 exists.. are you deleting worksheets somewhere?

    Regards
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Registered User
    Join Date
    12-02-2012
    Location
    Silver Bay, MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Error on Sheet Name Change: "Unable to set the Hidden Property of the Range Class"

    Thanks for responding. This is a single sheet workbook. No sheets added or substracted. I have even added the following code in the Workbook_Open event to insure that no shape such as a comment is set to other than allow Move & Size as failure to do so can trigger the above error when hiding columns. No protection has been set, although I would normally do so to help guide users to appropriate cells for data entry.

    Option Explicit

    Sub Workbook_Open()
    Dim s As Shape


    Range("3:3").Select 'Insures FreezePanes is set
    ActiveWindow.FreezePanes = True

    Range("C2").Select 'Scrolls sheet to designated range.
    ActiveWindow.ScrollRow = ActiveCell.Row

    On Error Resume Next 'Sets all shapes to Move & Size _
    Shapes that do not Move & Size can cause errors when hiding columns.
    For Each s In ActiveSheet.Shapes
    s.Placement = xlMoveAndSize
    Next

    End Sub

    Sub Workbook_Close()

    Sheet1.Columns("E:HV").EntireColumn.Hidden = False 'Unhides columns on close

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1