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
1881 Views

Similar Articles

[PageSpeed] 4

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:

vba for find last column with hidden columns in worksheet
Hello, This code has worked for finding the last column: lc = ws.cells(7, columns.count).end(xltoleft).column. However, when I used this code for a sheet that has hidden columns it would not work. What would be the correct code in this instance? Please assist. Thanks from OffDev wannabe Hi, lc = Rows(7).Find(What:="*", SearchDirection:=xlPrevious).Column Mike "OffDev" wrote: > Hello, > > This code has worked for finding the last column: > lc = ws.cells(7, columns.count).end(xltoleft).column. > > However, when I us...

How to run a Macro in MS Publisher from DOS command prompt?
Publisher 2003 with XP Prof. I am trying to run my macro as mspub.exe "document.pub" /mMyMacro where "MyMacro" is my macro but instead of running it, it's just MS Publisher opening "document.pub" file. How can I make it open the macro (this is for automate purposes). btw: my macro security setting are set to "low". Thanks joe1977@tlen.pl wrote: > Publisher 2003 with XP Prof. > I am trying to run my macro as mspub.exe "document.pub" /mMyMacro > where "MyMacro" is my macro but instead of running it, it's just MS >...

Protecting while still allowing macros to run
Hi, I have a spreadsheet with macros that update formulas in cells. Is there any way of protecting the worksheet while still allowing the user to run the macros. Hi Tammy, If you use VBA to turn on protection you can look in the help system for the arguments for Protect and you will find one called UserInterfaceOnly:=True which allows the macro to run in a protected workbook, but doesn't allow users to modify stuff manually. Cheers, Shane Devenshire "Tammy" <Tammy@discussions.microsoft.com> wrote in message news:1D21508D-2564-4AFB-AB73-98C7ABC27A7C@microsoft.com...

Macro help need with worksheet unprotect/protect and closing an externally referenced file...
Hi, I have the following macro that will shell out and open another spreadsheet to bring some data back into my workbook. Somehow, I need this macro to close that externally referenced file (without saving) once it opens it, manipulates some data and copies it to bring back into my workbook. Everything works now except the closing part. The second part of my problem is that one of the sheets in this workbook is protected because of a macro button I have on that sheet. It's protected without a password. I need to get the protection to temporarily be turned off to do some things and the...

Macros #7
I currently have a workbook that has 10 worksheets. Nine of the 10 worksheets are brought in from another application. The 10th worksheet is added at the beginning as a summary worksheet for the other 9. At this time someone has created a macro to look in column H of worksheets 2 - 10 (may not always be 10 worksheets, could be more, could be less) for a value of 1.00 and if it exists to delete that row. While this works great, it has to be manually applied to all 9 worksheets in the workbook. Is there a way to get the macro to run for all worksheets except the first one? Thanks in...

running macros in excel
Does anyone know if I can run a macro automatically when I open the spreadsheet ? thanks. Call the macro Auto_Open, or add your code to the Workbook_Open event. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "bobuk" <anonymous@discussions.microsoft.com> wrote in message news:fbb201c3f219$2e99ee50$a401280a@phx.gbl... > Does anyone know if I can run a macro automatically when I > open the spreadsheet ? thanks. ...

Replace All within selection does all sheet in 2002
Is there a way to use the "Replace All" command on a selection of cells and not the whole sheet. I'm sure that this used to be possible on eralier versions of Excel. The "Find All" command give the correct cells in the results box. ...

hidden form
When I use the database window to view the forms, some are missing! I think that somehow they are hidden from the database view, but I don't know how to unhide them. (Yes, I know they are there because they appear at certain points in running the database.) Thanks. -- Bill You don't mention which version of Access you are using. Depending on which, you can check a checkbox somewhere to "show hidden" objects. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post....

(2007) Excel macro blocks Outlook???
Why does a long running macro in Excel 2007 block Outlook??? (No, the macro doesn't access Outlook in any way. It does use ADO to access a database). I've noticed similar things on several occassions... where one Office 2007 App will block another (for no reason). God I hate Office 2007. -- -C. Moya www.cmoya.com Blocks Outlook in what way? From starting up? -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more ----- "C. Moya" <cmm@nospam.com> wrote in mess...

Macros upon opening excel
I have a problem, I have a worksheet that I created and it worked fine and when you opened it it opened normally. I recently had to protect some of the cells so people wouldnt mess with my formulas and now when I open the worksheet it always asks to enable or disable the macros but I do not have any macros made. Is there something I am missing? Thanks in advance. Upload files to see. <angela.gay@gmail.com> ??????:026394a2-3c02-4066-988a-0d69e82a740e@13g2000yql.googlegroups.com... >I have a problem, I have a worksheet that I created and it worked fine > and when you opened it it ...

Posting a Sheet here
Is it permissible to post a worksheet here in this group to ask and illustrate a question/problem? This is a text only newsgroup. Binary attachments are frowned upon by most. If you could explain your problem in plain text, I bet you'll get more potential responses. But you could put the file on the internets and point at that location and maybe you'll find someone who will take the time to download it. But be aware that lots of people won't open workbooks--there's just too much chance that there could be malicious code in it. http://www.savefile.com is one of those (...

macro trouble
hi i have recorded a macro for my workbook to cut paste data however what i have observed is that the procedure is than some how rough when is pasteing and coming back to the last sheet. may i know how to get it done very smoothly without shaken. here is the code Sub Transfer_() ' ' Transfer_ Macro ' ' Range("Form").Select Selection.Copy Sheets("Sales ").Select Range("Sales[[#Headers],[PRODUCT ID ]]").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("Sales[[#Headers],[PRODUCT ID ...

Macro to save file using vlookup...
Here's what I'm trying to do... I want a macro that will take a date value from a cell (say B2 on sheet1) and compare it to a list of closing dates (month's end closing). It needs to then make folders for the year and month (if they don't already exist) and save the file in that folder. The sheet with the closing dates is setup like this: Month Ending Fiscal Year November 11/28/04 2005 December 12/31/04 2005 And so on... Thanks for the help up front! ...

Need help to set-up a formula on Excel work sheet?
I need help on seting-up a formula on "Excel Work Sheet." Example: I need to subtract Cell A14 from Cell A13 and Mutiply the answer by "24" on Cell B14. Try this: =(A13-A14)*24 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "wiskeyjoexxx" <wiskeyjoexxx@discussions.microsoft.com> wrote in message news:8DB42179-1185-4D3B-BA6F-707D5D34F6BC@microsoft.com... I need help on seting-up a formula on "Excel Work Sheet." Examp...

format 10 sheets on same file to look like first one
Is it possible to group worksheets where the format is all exactly the same. Then ungroup to make a change on worksheet without affecting the other worksheets. To group worksheets: keep Ctrl pressed while you click on the sheet tabs If you have many: Click on first tab, keep Shift pressed, click on last tab HTH -- AP "Michael Abraham" <MichaelAbraham@discussions.microsoft.com> a �crit dans le message de news: 0E1E42C0-4A8E-4D7C-BBEB-B292F1274D21@microsoft.com... > Is it possible to group worksheets where the format is all exactly the > same. > Then ungroup to ma...

converting PDF to Excel Sheet?
Hello, I would like to convert a PDF to Excel using a simple linux command line tool. Anyone know if that is possible? The tool coul also be a windows tool, but I am looking for a tool that is completel automated, with no user intevention. The reason is that I have a program that downloads PDFs every hour o so, and I would like to convert that pdf to an excel sheet so i coul grab data off it easily. Or maybe, does anyone know of an easier way to grab tables informatio off PDFS? thank -- Message posted from http://www.ExcelForum.com Take a look at PDF and Excel http://www.mvps...

How do I print pg1 and pg2 (5x8) of my novel on 1 sheet(8.5x11)?
How do I print pg1 and pg2 (5x8) of my novel on 1 sheet(8.5x11)? This is what *I* would do. It doesn't mean it's the only way tho. Print the file with a .pdf driver. (If you don't have Acrobat and prefer not to make that investment, I suggest downloading PrimoPDF from www.primopdf.com.) Open the .pdf file in Adobe Reader. Set it to print multiple pages per sheet. Print it. -- JoAnn Paules MVP Microsoft [Publisher] "Marty" <Marty@discussions.microsoft.com> wrote in message news:6BADFCBB-1931-43DA-AB4A-3E07CB1EDA24@microsoft.com... > How do I print pg1 ...

Macro to change view of sheets
I have a workbook with about 85 sheets happen that we have to change the view from Normal to Page break preview wvery month, I could record a macro (with the recorder), page by page and it works, but could somebody help me to get a condensed macro, because I have to set another macro to changed them back to Normal. Thanks for your help. Fernando One way: Public Sub ToggleViews() Dim wkSht As Worksheet Dim nView As Long Dim sOldActive As String With Application .ScreenUpdating = False .EnableEvents = False End Wi...

Macro to add numbers to create combinations that equal certain amo
Thank you for your assistance. I receive a list of numbers, usually over one hundred items. I need to find out which amounts will add up to a given total. How would I create a macro to do this? Is there already an Excel function to do this that I have not found? It would need to work with currency. Ex: 9, 12, 7, 4, 15, 8, 10, 3 total needed 18 Solution 8+10; 3+15 Lauren, With a hundred different items, there are over 2^100 possible ways to combine them. You would need a supercomputer - or maybe even multiple supercomputers. If you had fewer than 25, you could do it. HTH, Bernie...

Balance sheet
I was using a balance sheet to keep track of my expenses and incoming money. When the page was full I could no longer add any further transactions. How do I insert another page or make it larger? Hi Plowleg, How is your data set up? Can you be a bit more specific. -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/showthread.php?threadid=482550 We are using the template Balance Sheet. There is 5 columns, the date,...

passing values to other sheets
hi i am having the columns in the sheet1 date no company amount 01/01/01 1 a 120 01/02/01 2 a 240 01/03/01 3 b 250 in the sheet2 i like to have the values of the company a with the following columns date,no,amount You would need code to bring across just the rows containing Company a, but you can do a few things to show just Company a results 1) Copy the data to Sheet 2 and filter it (Data>Filter>Auto Filter) 2) Build a pivot ...

Where is the macro?
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel A form field executes a macro upon exiting the field. The macro works as programmed. However, I now want to edit it and cannot find it on the dropdown list of macros. I do not recall if I saved it with the document or in a re-named template (or in Normal.dot). <br><br>Ron You probably saved it as "Private", in which case the only way to find it would be to use Find in the VB Editor to look for the name. It won't appear in any list. Cheers On 29/01/10 1:24 AM, in article 59bb1c2...

How to use Windows command within Excel macros
Hi, I want to select a bunch of text data separated by commas from a website, use Windows copy command to copy and paste it into a worksheet in Notepad and save it as a text file. How can I do that? That's all. In case you want to know further, I then can open the text file within Excel as a comma delimited data and manipulate it as much as I like. Thanks for any help. TIA Tom Hi Tom --From 'Insert' menu select 'Object' --Select 'Word Pad Document' --Select 'Display As Icon' if you want.. --Edit,save etc; as you want -- ...

One Chart, Same Data ranges, different source sheets
Hi All! I have a sheet with numerous charts. The charts are standard, much like a dashboard. The charts are fed from one sheet which is a rollup of summary data from many other sheets. I need to create a similar summary sheet for each of the remaining regions, but only need one sheet with charts. Is there a way to make the SHEET reference for the charts dynamic - say, populated by the user selecting an entry from a drop menu? The desired behavior is as follows: The Chart Sheet show 10 charts reflecting various data for Region A. I want to be able to change all the char...

Anyone developed an Excel sheet for Cost of Quality? #2
...