Hiding Rows in Inactive Sheets in Excel 2013

3
votes

Switching to Excel 2013 from Excel 2010,

When Sheet2 is inactive

The code

Worksheets("Sheet2").Rows("432:432").EntireRow.Hidden = False

gives Error

Unable to set the Hidden property of the Range class

But works fine when Sheet2 is the Active Sheet

In Excel 2010 , VBA had no problem hiding rows in InactiveSheets .

Is this a change in Excel 2013. If so, any fix.

EDIT: Worksheets("Sheet2").Protect Password:="password", userinterfaceonly:=True Worksheets("Sheet2").Rows("2:2").EntireRow.Hidden = False When setting userinterfaceonly option to true, VBA Code to hide row only works when the sheet is active

I can't recreate this error on a new 2013 Worksheet. But only when opening 2010 Excel app in 2013. Wonder If I am playing with any Settings here.

2
vbaexcelexcel-2013
Does this happen on all files or just one? Have you verified this on Excel 2010 for the same file(s) or just generalizing? Make sure the workbook/worksheet is not protected. – David Zemens
I was able to recreate the 1004 error by protecting Sheet2, great suggestion @DavidZemens! Of course, you can clear this by using Worksheet.Unprotect Password:="da_password", noting that there may not be a password (in which case you can simply use the .Unprotect method – Dan Wagner
Worksheets("Sheet2").Protect Password:="password", userinterfaceonly:=True Worksheets("Sheet2").Rows("2:2").EntireRow.Hidden = False When setting userinterfaceonly option to true, VBA Code to hide row only works when the sheet is active – Shorn Jacob
With AllowFormattingRows:=True, userinterfaceonly:=True , this was fixed this. – Shorn Jacob

2 Answers

-1
votes
Sub MakeScript1()
application.ScreenUpdating = False
Sheets("Script1").Visible = True
ThisWorkbook.Sheets("Script1").Select
  Dim x As Long, Z As Long, FF As Long, TextOut As String
  Const OutputPath As String = "c:\temp\"   '<==Note the trailing backslash
  Const BaseFileName As String = "Script1"
  Const StartColumn As Long = 1   'Assumed Column A
  Const StartRow As Long = 1      'Assumed Row 1
  For x = StartColumn + 1 To StartColumn + 1
    TextOut = ""
    For Z = StartRow To StartRow + 19
      TextOut = TextOut & Cells(Z, StartColumn).Value & " " & Cells(Z, x).Value & vbNewLine
    Next
    FF = FreeFile
    Open OutputPath & BaseFileName & ".vbs" For Output As #FF
    Print #FF, TextOut
    Close #FF
  Next
ThisWorkbook.Sheets("Instructions").Select
Sheets("Script1").Visible = False
application.ScreenUpdating = True
End Sub

see how i did

Sheets("Script1").Visible = True

and then

Sheets("Script1").Visible = False

-1
votes

oops, my bad the above isnt what you wanted.

here try code from this:

Dim RowsToHide As Range
Dim RowHideNum As Integer

' Set Correct Start Dates for Billing in New File
Workbooks("----- Combined_New_Students_Updated.xlsx").Activate
Sheets("2015").Activate
StartDateLine1 = Format(START_DATE_1, "ww") - 1 ' Convert Start Date to Week Number
StartDateLine1 = (StartDateLine1 * 6) - 2 ' Convert Start Date to Line Number
If StartDateLine1 >= "10" Then
 Cells(4, "q").Value = ""
 Cells(StartDateLine1, "q").Value = STATUS_1
 Cells(StartDateLine1, "z").Value = "START DATE " + START_DATE_1
 RowHideNum = StartDateLine1 - 2
 Set RowsToHide = Range(Cells(3, "a"), Cells(RowHideNum, "ab"))
 RowsToHide.Select
 RowsToHide.EntireRow.Hidden = True
End If