macro to hidden worksheet

[murph306]

hi
i was wanting to set up a system which has a front sheet, similar to a contents sheet, where each content is a link to a hidden worksheet. I had a feeling this would be a problem so i tried it out, and found that when the worksheet was hidden the macro had an error

pleeeeeeeeeeeeas help!

thanks
chris

[Bob Phillips]

Favour us with the code?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"murph306" <murph306.28pwlm_1149155700.9881@excelforum-nospam.com> wrote in
message news:murph306.28pwlm_1149155700.9881@excelforum-nospam.com...
>
> hi
> i was wanting to set up a system which has a front sheet, similar to a
> contents sheet, where each content is a link to a hidden worksheet. I
> had a feeling this would be a problem so i tried it out, and found that
> when the worksheet was hidden the macro had an error
>
> pleeeeeeeeeeeeas help!
>
> thanks
> chris
>
>
> --
> murph306
> ------------------------------------------------------------------------
> murph306's Profile:
http://www.excelforum.com/member.php...o&userid=35000
> View this thread: http://www.excelforum.com/showthread...hreadid=547392
>

[murph306]

it was a simple macro from one sheet to another:

Sub TEST1()
'
' TEST1 Macro
' Macro recorded 01/06/2006 by KMC
'

'
Sheets("Sheet3").Select
End Sub


however once the worksheet was hidden the macro no longer worked

[Bob Phillips]

Well obviously you cannot select a hidden sheet, as that displays it, and
display and hidden are the antithesis of one another.

But, it is rarely necessary to select anything, so what do you want to do
with/get from the hidden sheet?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"murph306" <murph306.28pxiy_1149156901.0802@excelforum-nospam.com> wrote in
message news:murph306.28pxiy_1149156901.0802@excelforum-nospam.com...
>
> it was a simple macro from one sheet to another:
>
> Sub TEST1()
> '
> ' TEST1 Macro
> ' Macro recorded 01/06/2006 by KMC
> '
>
> '
> Sheets("Sheet3").Select
> End Sub
>
>
> however once the worksheet was hidden the macro no longer worked
>
>
> --
> murph306
> ------------------------------------------------------------------------
> murph306's Profile:
http://www.excelforum.com/member.php...o&userid=35000
> View this thread: http://www.excelforum.com/showthread...hreadid=547392
>

[murph306]

ye i understand.
What i am trying to do is set up a page with a list of activities we do at my place of work. I want each activity to link to another page where the activity is described in more detail. However i don't want it to be possible to access the page by any other means than clicking the macro, if that makes sence??

is this possible?

[Pauliec]

why not just unhide the sheet as part of the macro and have a macro that upon closing the file the sheet is automatically hidden again?

[Bob Phillips]

If one of those pages is the hidden page, you have no choice but to unhide
it before you activate it. How else will you see it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"murph306" <murph306.28pywy_1149158700.7977@excelforum-nospam.com> wrote in
message news:murph306.28pywy_1149158700.7977@excelforum-nospam.com...
>
> ye i understand.
> What i am trying to do is set up a page with a list of activities we do
> at my place of work. I want each activity to link to another page where
> the activity is described in more detail. However i don't want it to be
> possible to access the page by any other means than clicking the macro,
> if that makes sence??
>
> is this possible?
>
>
> --
> murph306
> ------------------------------------------------------------------------
> murph306's Profile:
http://www.excelforum.com/member.php...o&userid=35000
> View this thread: http://www.excelforum.com/showthread...hreadid=547392
>

[Dave Peterson]

Sub TEST1()
with Sheets("Sheet3")
.visible = xlsheetvisible
.Select
end with
End Sub

Will make it visible and then select that sheet.

But I would guess that you want that sheet to disappear when you click on
another sheet?

If yes, rightclick on Sheet3's tab and select view code. Paste this into the
code window:

Option Explicit
Private Sub Worksheet_Deactivate()
Worksheets("IndexSheetNameHere").Visible = xlSheetVisible
Me.Visible = xlSheetHidden
End Sub

Change that name "indexsheetnamehere" to match the worksheet that will always be
visible.

It just verifies that it's visible before it tries to hide sheet3.



murph306 wrote:
>
> it was a simple macro from one sheet to another:
>
> Sub TEST1()
> '
> ' TEST1 Macro
> ' Macro recorded 01/06/2006 by KMC
> '
>
> '
> Sheets("Sheet3").Select
> End Sub
>
> however once the worksheet was hidden the macro no longer worked
>
> --
> murph306
> ------------------------------------------------------------------------
> murph306's Profile: http://www.excelforum.com/member.php...o&userid=35000
> View this thread: http://www.excelforum.com/showthread...hreadid=547392

--

Dave Peterson

[murph306]

thanks peeps thats a massive help!!