Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing a simple macro | Excel Worksheet Functions | |||
excel 4.0 macro removal tool | Excel Discussion (Misc queries) | |||
excel 4.0 macro remover tool | Excel Discussion (Misc queries) | |||
hide a worksheet so that a macro can still find it | Excel Worksheet Functions | |||
Can a macro format a hidden sheet? | Excel Discussion (Misc queries) |