A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Macro and hidden sheets



 
 
Thread Tools Display Modes
  #1  
Old January 18th 06, 12:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Macro and hidden sheets

Hi,

I have a WB with 10 worksheets.
At the time I did this WB, I also created a Macro that had the task to
change the format of all the sheets.
Today, I have decided to hide some of the sheets and the macro does no
longer work.
I guess this is because it cannot find the sheets which are hidden.

My question is: Is there a way to allow the Macro to work as before, meaning
to continue changing the format of the hidden sheets as well?
Something like a condition that says, if Sheet XY is hidden then look for it
into etc.

Thank you
Alex

Ads
  #2  
Old January 18th 06, 12:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Macro and hidden sheets

Hi Alex,

A hidden sheet cannot be selected, so try re-writing your code to remove
selections.

If this does nor resolve your problem, post your code.


---
Regards,
Norman


"Metallo" > wrote in message
...
> Hi,
>
> I have a WB with 10 worksheets.
> At the time I did this WB, I also created a Macro that had the task to
> change the format of all the sheets.
> Today, I have decided to hide some of the sheets and the macro does no
> longer work.
> I guess this is because it cannot find the sheets which are hidden.
>
> My question is: Is there a way to allow the Macro to work as before,
> meaning
> to continue changing the format of the hidden sheets as well?
> Something like a condition that says, if Sheet XY is hidden then look for
> it
> into etc.
>
> Thank you
> Alex
>



  #3  
Old January 18th 06, 01:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Macro and hidden sheets

Norman,

I am aware that it cannot be selected, but my question was another, that is,
is there a workaround to make the hidden sheets selectable even if they are
hidden?

For instance, the formulas work fine, the values generated as a result of a
calculation turn out in the hidden sheets as if the they were unhidden,
therefore I was wondering: If the formulas work on hidden sheets, why cannot
the macros?

Hope this is clearer.

Thank you!
Alex

"Norman Jones" wrote:

> Hi Alex,
>
> A hidden sheet cannot be selected, so try re-writing your code to remove
> selections.
>
> If this does nor resolve your problem, post your code.
>
>
> ---
> Regards,
> Norman
>
>
> "Metallo" > wrote in message
> ...
> > Hi,
> >
> > I have a WB with 10 worksheets.
> > At the time I did this WB, I also created a Macro that had the task to
> > change the format of all the sheets.
> > Today, I have decided to hide some of the sheets and the macro does no
> > longer work.
> > I guess this is because it cannot find the sheets which are hidden.
> >
> > My question is: Is there a way to allow the Macro to work as before,
> > meaning
> > to continue changing the format of the hidden sheets as well?
> > Something like a condition that says, if Sheet XY is hidden then look for
> > it
> > into etc.
> >
> > Thank you
> > Alex
> >

>
>
>

  #4  
Old January 18th 06, 01:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Macro and hidden sheets

You can reach your end goal by altering your Macro to first check each sheet
to see if it is Hidden, and if so then unhide it > perform your reformatting
> and then rehide it and go on to the next.......if the sheet is not hidden,

then just perform the reformatting and go on.....

Vaya con Dios,
Chuck, CABGx3



"Metallo" wrote:

> Hi,
>
> I have a WB with 10 worksheets.
> At the time I did this WB, I also created a Macro that had the task to
> change the format of all the sheets.
> Today, I have decided to hide some of the sheets and the macro does no
> longer work.
> I guess this is because it cannot find the sheets which are hidden.
>
> My question is: Is there a way to allow the Macro to work as before, meaning
> to continue changing the format of the hidden sheets as well?
> Something like a condition that says, if Sheet XY is hidden then look for it
> into etc.
>
> Thank you
> Alex
>

  #5  
Old January 18th 06, 01:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Macro and hidden sheets

Hi Alex.

Post the problematic code.


---
Regards,
Norman



"Metallo" > wrote in message
...
> Norman,
>
> I am aware that it cannot be selected, but my question was another, that
> is,
> is there a workaround to make the hidden sheets selectable even if they
> are
> hidden?
>
> For instance, the formulas work fine, the values generated as a result of
> a
> calculation turn out in the hidden sheets as if the they were unhidden,
> therefore I was wondering: If the formulas work on hidden sheets, why
> cannot
> the macros?
>
> Hope this is clearer.
>
> Thank you!
> Alex
>
> "Norman Jones" wrote:
>
>> Hi Alex,
>>
>> A hidden sheet cannot be selected, so try re-writing your code to remove
>> selections.
>>
>> If this does nor resolve your problem, post your code.
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> "Metallo" > wrote in message
>> ...
>> > Hi,
>> >
>> > I have a WB with 10 worksheets.
>> > At the time I did this WB, I also created a Macro that had the task to
>> > change the format of all the sheets.
>> > Today, I have decided to hide some of the sheets and the macro does no
>> > longer work.
>> > I guess this is because it cannot find the sheets which are hidden.
>> >
>> > My question is: Is there a way to allow the Macro to work as before,
>> > meaning
>> > to continue changing the format of the hidden sheets as well?
>> > Something like a condition that says, if Sheet XY is hidden then look
>> > for
>> > it
>> > into etc.
>> >
>> > Thank you
>> > Alex
>> >

>>
>>
>>



  #6  
Old January 18th 06, 07:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Macro and hidden sheets

Chuck,

Easy to say, but I have no clou on how to instruct the macro to check if the
sheet is hidden or not.
Can you give me a sample so that I understand how it works and then I can
apply it to my macro?

Thanks man

Vai con Dio, indeed!
Alex

"CLR" wrote:

> You can reach your end goal by altering your Macro to first check each sheet
> to see if it is Hidden, and if so then unhide it > perform your reformatting
> > and then rehide it and go on to the next.......if the sheet is not hidden,

> then just perform the reformatting and go on.....
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Metallo" wrote:
>
> > Hi,
> >
> > I have a WB with 10 worksheets.
> > At the time I did this WB, I also created a Macro that had the task to
> > change the format of all the sheets.
> > Today, I have decided to hide some of the sheets and the macro does no
> > longer work.
> > I guess this is because it cannot find the sheets which are hidden.
> >
> > My question is: Is there a way to allow the Macro to work as before, meaning
> > to continue changing the format of the hidden sheets as well?
> > Something like a condition that says, if Sheet XY is hidden then look for it
> > into etc.
> >
> > Thank you
> > Alex
> >

  #7  
Old January 19th 06, 12:14 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Macro and hidden sheets

Sorry I took so long to get back....I was at work before and got busy, and
am home now....
This is crude, but does perform the desired task.....enough to give you the
idea, anyway.

Sub CheckIfHidden()
Sheets("sheet1").Select
'Do Your code here to sheet1 which was not hidden
If Sheets("sheet2").Visible = False Then
Sheets("sheet2").Visible = True
Sheets("sheet2").Select
'Do your code here to sheet2 if it was hidden
Sheets("sheet2").Visible = False
Else
Sheets("sheet2").Select
'Do your code here to sheet2 if it was not hidden
End If
End Sub

Vaya con Dios,
Chuck, CABGx3



"Metallo" > wrote in message
...
> Chuck,
>
> Easy to say, but I have no clou on how to instruct the macro to check if

the
> sheet is hidden or not.
> Can you give me a sample so that I understand how it works and then I can
> apply it to my macro?
>
> Thanks man
>
> Vai con Dio, indeed!
> Alex
>
> "CLR" wrote:
>
> > You can reach your end goal by altering your Macro to first check each

sheet
> > to see if it is Hidden, and if so then unhide it > perform your

reformatting
> > > and then rehide it and go on to the next.......if the sheet is not

hidden,
> > then just perform the reformatting and go on.....
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Metallo" wrote:
> >
> > > Hi,
> > >
> > > I have a WB with 10 worksheets.
> > > At the time I did this WB, I also created a Macro that had the task

to
> > > change the format of all the sheets.
> > > Today, I have decided to hide some of the sheets and the macro does no
> > > longer work.
> > > I guess this is because it cannot find the sheets which are hidden.
> > >
> > > My question is: Is there a way to allow the Macro to work as before,

meaning
> > > to continue changing the format of the hidden sheets as well?
> > > Something like a condition that says, if Sheet XY is hidden then look

for it
> > > into etc.
> > >
> > > Thank you
> > > Alex
> > >



  #8  
Old January 19th 06, 09:33 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Macro and hidden sheets

Chuck,

Thank you, i see what you mean now.
I will try and see.

Cheers
Alex

"CLR" wrote:

> Sorry I took so long to get back....I was at work before and got busy, and
> am home now....
> This is crude, but does perform the desired task.....enough to give you the
> idea, anyway.
>
> Sub CheckIfHidden()
> Sheets("sheet1").Select
> 'Do Your code here to sheet1 which was not hidden
> If Sheets("sheet2").Visible = False Then
> Sheets("sheet2").Visible = True
> Sheets("sheet2").Select
> 'Do your code here to sheet2 if it was hidden
> Sheets("sheet2").Visible = False
> Else
> Sheets("sheet2").Select
> 'Do your code here to sheet2 if it was not hidden
> End If
> End Sub
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Metallo" > wrote in message
> ...
> > Chuck,
> >
> > Easy to say, but I have no clou on how to instruct the macro to check if

> the
> > sheet is hidden or not.
> > Can you give me a sample so that I understand how it works and then I can
> > apply it to my macro?
> >
> > Thanks man
> >
> > Vai con Dio, indeed!
> > Alex
> >
> > "CLR" wrote:
> >
> > > You can reach your end goal by altering your Macro to first check each

> sheet
> > > to see if it is Hidden, and if so then unhide it > perform your

> reformatting
> > > > and then rehide it and go on to the next.......if the sheet is not

> hidden,
> > > then just perform the reformatting and go on.....
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > > "Metallo" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a WB with 10 worksheets.
> > > > At the time I did this WB, I also created a Macro that had the task

> to
> > > > change the format of all the sheets.
> > > > Today, I have decided to hide some of the sheets and the macro does no
> > > > longer work.
> > > > I guess this is because it cannot find the sheets which are hidden.
> > > >
> > > > My question is: Is there a way to allow the Macro to work as before,

> meaning
> > > > to continue changing the format of the hidden sheets as well?
> > > > Something like a condition that says, if Sheet XY is hidden then look

> for it
> > > > into etc.
> > > >
> > > > Thank you
> > > > Alex
> > > >

>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Editing a simple macro Connie Martin Excel Worksheet Functions 5 November 29th 05 09:19 PM
excel 4.0 macro removal tool Sachin Shah Excel Discussion (Misc queries) 0 August 25th 05 04:17 AM
excel 4.0 macro remover tool Sachin Shah Excel Discussion (Misc queries) 0 August 25th 05 04:14 AM
hide a worksheet so that a macro can still find it frendabrenda1 Excel Worksheet Functions 1 June 17th 05 04:30 PM
Can a macro format a hidden sheet? Robert Excel Discussion (Misc queries) 1 February 9th 05 06:13 PM


All times are GMT +1. The time now is 09:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.