Macro Error When Connecting to Hidden Sheet

[Guest]

I have a very simple macro that, when run, takes the user to a specified
location on another sheet in the same workbook:

Sub ToProjectInfoPage()
Sheets("Project Info").Select
Range("A1:E1").Select
End Sub

This works fine when the destination sheet ("Project Info") is visible, but
when it's hidden, the macro returns an error.

Is there a way to fix the macro so that it works when the destination sheet
is hidden?

Thanks in advance!

 

[Dave Peterson]

Unhide the sheet first.

You could even hide what you're doing from the user:

Sub ToProjectInfoPage()
application.screenupdating = false
with Sheets("Project Info")
.visible = xlsheetvisible
.Select
.Range("A1:E1").Select
.visible = xlsheethidden
end with
application.screenupdating = true
End Sub

But there are not that many things that would require you to select a range.

Maybe you can just work on that range directly.

 

[Franz Verga]

I have a very simple macro that, when run, takes the user to a
specified location on another sheet in the same workbook:

Sub ToProjectInfoPage()
Sheets("Project Info").Select
Range("A1:E1").Select
End Sub

This works fine when the destination sheet ("Project Info") is
visible, but when it's hidden, the macro returns an error.

Is there a way to fix the macro so that it works when the destination
sheet is hidden?

Thanks in advance!

Hi Wuddus,

the error raise because you cannot select an hidden sheet and also you
cannot select a range on an hidden sheet...

So you can modify your macro in this way:

Sub ToProjectInfoPage()
With Sheets("Project Info")
.Visible = xlSheetVisible
.Range("A1:E1").Select
End with
End Sub

or if you don't want to make your sheet visible you can use this one:

Sub ToProjectInfoPage()

Application.ScreenUpdating = False

With Sheets("Project Info")
.Visible = xlSheetVisible
.Range("A1:E1").Select
'other code for calculations
End with

Application.ScreenUpdating = True

End Sub


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

Hi Wuddus,

the error raise because you cannot select an hidden sheet and also you
cannot select a range on an hidden sheet...

So you can modify your macro in this way:

Sub ToProjectInfoPage()
With Sheets("Project Info")
.Visible = xlSheetVisible
.Range("A1:E1").Select
End with
End Sub

or if you don't want to make your sheet visible you can use this one:

Sub ToProjectInfoPage()

Application.ScreenUpdating = False

With Sheets("Project Info")
.Visible = xlSheetVisible
.Range("A1:E1").Select
'other code for calculations
End with

Application.ScreenUpdating = True

End Sub

I forgot a line in both macro... You cannot select a range on a sheet
without selecting the sheet... So the macro should be:

Sub ToProjectInfoPage()
With Sheets("Project Info")
.Visible = xlSheetVisible
.Select
.Range("A1:E1").Select
End with
End Sub

if you want to see the previously hidden sheet, and:

Sub ToProjectInfoPage()

Application.ScreenUpdating = False

With Sheets("Project Info")
.Visible = xlSheetVisible
.Range("A1:E1").Select

'other code for calculations

.Visible = xlSheetHidden

End with

Application.ScreenUpdating = True

End Sub


if you don't want show the hidden sheet.

Anyway, as also suggested from Dave, you can work on a range based on an
hidden sheet without selecting it...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Thanks, Dave. I think this will do the trick. Very helpful!

Sorry about the delayed response: I posted my question last night but then
didn't get a chance to log on until this morning.

 

[Guest]

Thanks, Frank. Both your and Dave's responses, above, have been really
useful. (Perhaps I should just get more comfortable in VBA so I don't have to
ask dumb questions!)