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

0
Metallo (10)
1/18/2006 12:51:06 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
1943 Views

Similar Articles

[PageSpeed] 30

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" <Metallo@discussions.microsoft.com> wrote in message 
news:AE72C4F3-4EE6-47DD-B83C-AD8AF2A15A7D@microsoft.com...
> 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
> 


0
normanjones (1047)
1/18/2006 12:58:35 PM
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" <Metallo@discussions.microsoft.com> wrote in message 
> news:AE72C4F3-4EE6-47DD-B83C-AD8AF2A15A7D@microsoft.com...
> > 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
> > 
> 
> 
> 
0
Metallo (10)
1/18/2006 1:13:03 PM
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
> 
0
CLR (807)
1/18/2006 1:32:04 PM
Hi Alex.

Post the problematic code.


---
Regards,
Norman



"Metallo" <Metallo@discussions.microsoft.com> wrote in message 
news:146507EB-FB5B-42EE-8E7C-B0A9AE032E9F@microsoft.com...
> 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" <Metallo@discussions.microsoft.com> wrote in message
>> news:AE72C4F3-4EE6-47DD-B83C-AD8AF2A15A7D@microsoft.com...
>> > 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
>> >
>>
>>
>> 


0
normanjones (1047)
1/18/2006 1:32:10 PM
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
> > 
0
Metallo (10)
1/18/2006 7:56:13 PM
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" <Metallo@discussions.microsoft.com> wrote in message
news:E47E04B1-9C6D-4ED3-9CF0-B2B6D615B62F@microsoft.com...
> 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
> > >


0
croberts (1377)
1/19/2006 12:14:39 AM
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" <Metallo@discussions.microsoft.com> wrote in message
> news:E47E04B1-9C6D-4ED3-9CF0-B2B6D615B62F@microsoft.com...
> > 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
> > > >
> 
> 
> 
0
Metallo (10)
1/19/2006 9:33:02 AM
Reply:

Similar Artilces:

Will a Macro do this? If so, HOW?
I have a tracking spreadsheet going right now. Lots of dates and names. I would like to set up this sheet so that if I change a status (via dropdown menu) to complete, it automatically changes all dates to the next calendar year and clears that 'status'. Is this possible? Do I need a macro? And how do I set it up? I've never coded a macro, just recorded them... ...

Excel macro or command button to search using a user form
I have a user form which shows info about each student. I want to be able to enter a student's name in a textbox and have all the fields on the form populated with the student information. You need to be a lot more specific on what you are wanting else we have to make a lot of assumptions. I would recommend using a combobox instead of a textbox. This way you can utilize the Change Event and have the students info. automatically filled in the rest of your userform. I will assume you have a list of students in Sheet1 in Col. A. Just set the RowSource property of the c...

macro query disable download failure
Hello, I have a macro which retrieve different queries from the web. It happens that a resource at that moment isn''t available. If this happens the querie gives a message which said unable to open http://webpage This message i don't want to display (other routines herefor). I use "Application.DisplayAlerts = False" but this won't help. Can anybody help me with this problem. Thanks ...

Hidden Rows
Hi there, Thank you in advance for any help. I have a worksheet with the top 9 rows hidden. I can't seem to get them to unhide. I've checked the help file and tried the suggestion there Edit > Go To > A1 then Format > Row > Unhide but it doesn't seem to work. I still can't see those top 9 rows. Any other suggestions I can try? Thanks, KD Hi KD! Try Data > Filter Remove check from AutoFilter -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au No check by Autofilter, but I tried checking it then unchecking it just...

Saving Input on User Form to Hidden Worksheet
I have a User Form that once it is filled in, it updates several other Work Books from different Control Buttons. I need to save the Input on the User Form in one of the Wook Books, so that if the Work Book is opened again the User Form automatically Fills back in. Would the Information on the User Form be saved on a hidden Work Sheet in the Work Book that I want to save it in? Any Ideas on how to achive this? You must write code to save and reload informations like this. Lots of ideas. Where to save is a question of what's practical. You can write to a sheet in the ...

Disable 'Macro' dialog box...
Thanks Geoff, for the info on how to protect the VBA code. Have one further question; Now that the workbook and code is protected, I have sub routines that are run from command buttons. However, it is still possible to push ALT-F8 to list my routines, then click on a routine, then choose 'Run'... Is there a way to disable this option, so that users can not view or run the list of available routines (I need them to run the routines, only from the command buttons). Ruskin You can do what you want by making each macro a Private macro. This will preclude that macro from appearing...

MACROS SETUP
I would like to set up a macro to do the following: RUN MACRO = SENDING E-MAIL Set up Retreive info from several files (specefic worksheet). Retreive base on number e.g (111-0 or 187-1 etc).Each number has an e-mail address. Format to send as e-mail: 1st TEN rows of the worksheet and rows corresponding with the numbers (117-0) Retreive ONE row above and below numbers Page break if several numbers has the same e-mail within a file/worksheet Page break when retreive info from different file Hi just as a starting point: http://www.rondebruin.nl/mail/add-in.htm -- Regards Frank Kabel Frankf...

Excel Sheet does not display properly
Hello all, I have a basisc Excel chart that included 4 columns and approximately 30 lines, certain columns have different colors...As you can see nothing much... When I save the document as html, and upload that file to my web server - people are complaining that they can only see the top portion of the chart - they keep clicking on the Internet Explorer refesh button and some are able to view it afterwards, some need to remove their cache - and some don't have a problem (some times)...We are all using the exact same version of Office, and IE... Does anyone have any suggestions on...

Summary sheet including only open projects.
Have a workbook with 2 worksheets. Main worksheet contains all projects, opened, cancelled and closed. Contains columns A-Q. Column Q is entitled "Complete". Valid entries in the column appear to be cancelled, yes or blank. They want the second worksheet to contain only current projects and only columns A-M. As a project completes they want it to be deleted from the current projects sheet. I know there's a way to do this somehow but not very familiar with coding VBA. Thanks! -- KTB Two possible ideas: Record a macro of you clearing sheet 2, and th...

Macro to run when opening doc
Hi all Is there any way to make a macro run when opening a specific document?? Tks in advance António Hi Antonio, Yes, you can name the macro 'AutoOpen' or call it from a macro so-named in a normal code module. Alternatively, you can use a 'Document_Open' macro in the document's 'ThisDocument' module, instead of the 'AutoOpen' macro. -- Cheers macropod [Microsoft MVP - Word] "Antonio" <Antonio@discussions.microsoft.com> wrote in message news:965EBAAC-A781-4163-9530-8454EDE1832B@microsoft.com... > Hi all &g...

Macro Error when moved Xcel Sheets from User's Home Dir to Networ
Hello Xcel spreadsheets Copied from User's Network Home Folder to Another Network Folder. copy \\network\home\userA\*x.s \\network\REPGROUP Getting "object variable or with block.." What else I need to copy, when copying from one network location to another. I reverted back to old way, still same problem Thanks Basheer Use FileCopy to copy files. -- Jim "Basheer" <Basheer@discussions.microsoft.com> wrote in message news:744DF6C5-96B3-48B2-99AF-657487D87FCD@microsoft.com... | Hello | Xcel spreadsheets Copied from User's Network Home Folder to Another...

Print Column Headings on multiple sheets
I have a budget that when printed is one page wide and three tall. How can I add the column headings to the second and third pages? click file/page setup and click the sheet tab click the icon next to rows to repeat at top select the rows click the icon again and then ok -- Gary Keramidas Excel 2003 "wx4usa" <wx4usa@gmail.com> wrote in message news:19421c6b-0ea5-4524-8574-8dea96d44bde@22g2000yqr.googlegroups.com... >I have a budget that when printed is one page wide and three tall. How > can I add the column headings to the second and third pages?...

Macros failing in different language versions of Word
My company wants to standardize on templates across several countries - we have offices in many countries, but for simplicity I will just talk about UK and Sweden. My problem is that some features in the English version of Word do not work in the Swedish version of Word. For example, StylRef. I use StyleRef in the header margin to refer to "Heading 1" style. The problem is that it is not called "Heading 1" in the Swedish version of Word. It is called "Rubrik 1", which translates back to "Heading 1" of course. In the UK we use many macro...

Time stamp in Excel Sheet
I am looking to entering a value into column A (Data Point), then on column B (Time Stamp), the time that value A was entered. Then another value will be entered into A2 and would like the time that Data Point 2 was entered into B2 and so on. I have tried the IF statement =IF(A1,NOW(),"") in B1,B2, B3... but every time the work sheet reculculates, the Time Stamp column becomes the same. How can I stop this from happening or am I going about this the wrong way. Take a look at http://www.4-consulting.com/News/Ezine_4/Ezine_4_03.htm#t16 "John M" <John M@discussi...

Exchanging information from one sheet to another.
I have a spreadsheet with basically inventory on it. I want to make another sheet for a wholesale price list. I want the information from sheet1 to move automatically into sheet2 if a certain cell is empty in sheet1. To be more detailed, I have a list of slabs of granite, if they are not on hold for a customer i want them to be included in the wholesale price list. There is a column for last names of customers who put slabs on hold. If the cell for the customer's name is empty the slab is available to sell. Each slab has it's own row on the spreadsheet. there are about 9 columns for ea...

Add the contents of two sheets together
I am looking for a way to add the content of two sheets or even 1 shee together. I have a large list of part numbers with quantities. Som of the part numbers are repeated. I would like to add the duplicat part number quantities together and have just 1 line with the par number and the total quantity. Does anyone have any insight? Thank -- Message posted from http://www.ExcelForum.com Hi Sounds like a job for a pivot table. See here for an intro: http://www.cpearson.com/excel/pivots.htm -- Andy. "mwiggins367 >" <<mwiggins367.16x77f@excelforum-nospam.com> wrote...

Run Code in General Module Upon Activation of Other Sheets
I have VBA code residing in a general module of a workbook. The outpu from the code updates several sheets in the workbook. I would like t automatically Run the code in the general module if any of thes several sheets are activated (by clicking on the sheet�s tab). How ca I do this? Thanks -- Message posted from http://www.ExcelForum.com Hi Steve You can use the folowwing events in the sheet module(not a normal module) Right click on a sheet tab and choose view code. Paste the events in there and press Alt-Q to go back to Excel. You can copy the code in the event or type the macro name ...

Referencing a macro in an Excel formula
Is it possible to reference a macro in an Excel formula. I want to create a formula that if a condition does not exist in a cell on that line, then delete the entire line. Is a macro the way to go or is there some other way? ...

How do I copy a graph to new worksheet and get data from new sheet?
When I copy a worksheet with a graph or just copy a graph and paste in a new worksheet, the source data is still linked to the original worksheet. I've tried editing the sorce data to remove the sheet name to try to make it relative to the current sheet, but that gives me "Error in your formula". Is there a quick way to get the graph to link to the data in the new worksheet? Even a macro would help, if that's what it takes. Thanx in advance... If the chart is embedded in the worksheet, you should be able to copy the worksheet elsewhere, and the copied chart will point t...

Help: Can't display contents of Adobe Acrobat Document in Sheet
Hoping that someone can help with this. I'm trying to insert an Adobe Acrobat document into an Excel worksheet. I'd like to see the document contents, not an Icon. According to the Excel help files, it should be possible to display the file as it shows up in Acrobat but no matter what I do, I see only an Icon. I went through the procedure listed in the help files to convert the document (ensuring that "show as an Icon" is not checked) but nothing makes any difference! Any idea what I'm doing wrong? When I try this with other document types (eg AutoCad) everythi...

make an excel worksheet (sheet 2) open w/ the cursor located in t.
I have an excel worksheet with three sheets. Sheets 2 and 3 are locked so that entries can be made in only certain cells. Sheet 3 is linked to sheet 2 so that when an entry is made on sheet 2, the entry appears on sheet 3 in the corresponding cell. I would like sheet 3 to open in the cell the last entry was made (which is the corresponding cell on sheet 2 because sheet 3 is completly locked except for the link to sheet 2) Is there a simple way to perform this task? ...

Unhiding All hidden Worksheets with a Macro/VBA Code
Hi, Can anyone help me please ? Whilst working with an Excel Workbook I often hide all unwanted Worksheets leaving only the ones I need to work on visible. What I would like to do is have a macro or VBA code to click on a button to unhide all hidden worksheets rather repeating Format-Sheet-Unhide to unhide each worksheet. Any help would be greatly appreciated (Excel version Excel 2000) Many thanks Rob -- robertguy 44 Married two kids ------------------------------------------------------------------------ robertguy's Profile: http://www.excelforum.com/member.php?action=getinf...

AutoArchive Properties in Hidden Profile?
Background: A month ago I obtained a new laptop with Win7 and Office/Outlook 2007. My files, settings, and many things were migrated from my old WinXP with Office/Outlook 2007 laptop to this one (identical hardware). This migration caused down-the-wire backups to prompt for a password whenever it wanted to backup my PST files. The fix for this was to remove the datafiles from Outlook (Tools --> Options --> Mail Setup --> Data Files) and then re-add them. It worked, with a downside. Issue: Now, each time AutoArchive runs, it presents me a report with eight entries ...

Protect macro
I have a workbook that contains a macro that many people will be using. can the macro be protected to prevent changes to the macro. I can protect worksheets but I don't know how to protect a macro. Garry Open your workbook Open the VBE hit ctrl-r to see the project explorer Select your project in the project explorer window Tools|VBAProject Properties|Protection Tab Give it a memorable password (and lock the project from viewing) But remember, this password, like any password, can be broken by anyone with enough time or money. On 07/07/2010 08:57, Paladin046 wrote: > I have a wo...

How do I set a macro to run at specific times?
Hi I have recorded a macro which I want to run automatically every hour. Could someone tell me how I do this You can read about the scheduling procedures at Chip Pearson's site: http://www.cpearson.com/excel/ontime.htm Nick123 wrote: > > Hi > > I have recorded a macro which I want to run automatically every hour. Could > someone tell me how I do this -- Dave Peterson ...