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

Similar Articles

[PageSpeed] 27

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:

How to export all chartobjects in a sheet to powerpoint (creating a new ppt file) as pictures?
Hi, I've seen some code examples doing this, does anybody have it or know it? Question: I have no control what office version except that it is not -97 but 2000 or later. Are there any things I need to know/think of regarding that? Kind Regards Gunnar, Jon Peltier has some very detailed instructions on his site that might help: http://peltiertech.com/Excel/XL_PPT.html -- Regards, John Mansfield http://www.pdbook.com "Gunnar Johansson" wrote: > Hi, > > I've seen some code examples doing this, does anybody have it or know it? > > Question: I h...

Linking Dialog box content with cells in "regular" sheet
How to link data in Edit box (from Dialog caption) with exact cells in "regulal" Sheets? Or is there another way to link exact content from Dialog sheet to normal sheet? For example, if I have number 200 in Dialog, I need that exact number 200 on another ("regular") sheet to preform calculations with it. thnx ...

filtering and macros #2
thanks Dave, I have tried the populating code as you suggested, it works fine fo one column(only), but I can't adjust it to do more than one column partly because I don't understand what your code is doing, more so a the part below ie .Range("D2").AutoFill _ Destination:=.Range("d2:d" & LastRow) what does "d2:d2 stand for When I run my macro I would like to populate more than one column s I've tried to adjust to no avail, I thought I could do the followin with your code: Option Explicit Sub testme02() Dim LastRow As Long Dim wks As Worksheet S...

how do you to create a spread sheet from scratch
Buy a Dummies book Regards, Peo Sjoblom That's big "piece of string" question that I would only start to answer by asking three questions - What are you trying to do What are your inputs (data) What are your outputs (presentation of data) Regards. Bill Ridgeway Computer Solutions David McRitchie has a list of Excel tutorials that may help you get started: http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials impervious wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

logging macro
Hoping that some one here can help me with a data logging spreadshee that I am trying to set up. Right now I am gathering data from one o our machines that has a Programmable Logic Controller with a networ card in it. I am getting my data, but am having a hard time with m logging and clear log. What happens is, when I press the button "Star Log" my data should be saved in 15 min increments. When I am finishe logging, I want to clear the log and stop it until I am ready to begi agian. I have my macros set up and am currently logging, problem i when I press my "Clear Log" but...

Delete a command button on a protected sheet
How would I set a macro to delete a command button on a protected sheet- I'd wan't to be able to fire it from within another macro using "Call". If that is the way you are firing it, why have it at all? Just have the other macro perform whatever function is assigned to it. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "johnnykunst" <user@msgroups.net/> wrote in message news:#Gdt8x0#KHA.5168@TK2...

Macro warning window
I have a worksheet that used to have macros, and I'd get that warning every time I opened the worksheet saying it could contain viruses and whether to enable or disable the macros. I have since removed all macros from that worksheet, and I have no other macros as standard but am still getting the warning. How do I make it go away? Hi see: http://www.contextures.com/xlfaqMac.html#NoMacros -- Regards Frank Kabel Frankfurt, Germany "Beema" <none@supplied.com> schrieb im Newsbeitrag news:ei2gr$I2EHA.1152@TK2MSFTNGP14.phx.gbl... > I have a worksheet that used to have m...

moving values from sheet to another
I would like to know if it is possible to create a formula on one shee (sheet 2) and copy that resulting value to another sheet (main sheet) So when I make changes to the formula on sheet 2 it will update on th main sheet and be used in another formula on that sheet (main sheet) Is this possible? Thank you. :) : -- makdaddy92 ----------------------------------------------------------------------- makdaddy925's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2938 View this thread: http://www.excelforum.com/showthread.php?threadid=49189 On the main sheet in the ce...

lost assign macro? #2
i am using the control toolbox, but where can i find this call option -- chie ----------------------------------------------------------------------- chief's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=777 View this thread: http://www.excelforum.com/showthread.php?threadid=27149 When you right click the command button you see an option for "Vie Code" the VBA Editor will appear in the sub called "Command1_Click" You will need to type "Call" and then the name of your macro Close the VBE window and then press your command button ...

hyperlink not working in excel 2007, if worksheet is hide or hidden
I am facing this issue, need someone to share how to get the hyperlink working on excel 2007 if i hide or hidden the worksheet. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) With Sheets("Sheet1") .Visible = True .Select End With End Sub Gord Dibben MS Excel MVP On Tue, 1 Dec 2009 18:48:21 -0800 (PST), skyexcel <angela.chin88@gmail.com> wrote: >I am facing this issue, need someone to share how to get the hyperlink >working on excel 2007 if i hide or hidden the worksheet. Unhide the sheet, do your operatio...

How to execute Macros through Text Links?
Hi There, can anybody help me out is above problem?? I need to execute a macro written in VBA, through Text Hyperlink. I can access a macro through any object, but how could I do this with text links? please guys, it is urgent. -- Sheikh Saadi ...

Command buttons & macros
How do I allocate a macro to a command button? Right clicking a command button which is in design mode brings down a drop down menu but 'assign macro' isn't on option. -- Jock Waddington Jock, Sounds like you have control toolbox button.. Assuming so, when in design mode, double-click the button, and then will open up the worksheet code module, and create a skeleton click event which would be where you add your code. -- HTH RP (remove nothere from the email address if mailing direct) "Jock W" <JockW@discussions.microsoft.com> wrote in message news:E7BA9B9...

summing of numbers between sheets for same column values
I am trying to figure out how to add the number of items placed in a system. Each system has multiple items in it with different yearly quantities. For an example, in sheet1 I have a table with 3 columns A- is the item# (00010001), B- is the system that Item # is placed in (BB001), C- is the quantity of items in that given system (111000). In sheet 2 I would like column A to be each system (BB001-BB100) and column B to be the sum of all of the different items #'s in that system (lets say system BB001 has 3 item #'s 00010001, 00010002 and 00010003, all three of them have a quantity of ...

Protect sheet but allow sorting only
I'm using Excel 2003, and I've tried to allow only sorting on a protected sheet. I've tried by choosing Tools, Protection, Protect Sheet. Next, I've checked the sort check box in the "Allow all users of this worksheet to:" area. The only other check boxes that are checked are the "Select locked cells" and "Select unlocked cells" boxes. When I test the protected worksheet I am still unable to sort. Am I missing something? Thanks! -- Christina "Christina" <Christina@discussions.microsoft.com> wrote in message news:E37A3...

Macro code problem
hi everyone, I have a problem where I have macro code where i want to refer to a range name but excel won't recognise what i want it to do unless i put the cell reference in there. The code is listed below, cell I175 is range named "cash". by simply subsituting "cash" for $I$175 does not work! any ideas? If Target.Address = ("$I$175") Then If Range("cash").Value <> 1 Then Range("debt_terms").Select Selection.EntireRow.Hidden = False Range("cash").Select Else If Range("cash").Value = 1 Then Ran...

viewing sheets
My spreadsheet contains a fill out form, i was wondering if it was possible to save this form as a webpage whereit can be viewed and filled out. Is it also possible to get the background image in the spreadsheet up in the webpage as well. Thanks in advance. --- Message posted from http://www.ExcelForum.com/ Noctos <<Noctos.110vey@excelforum-nospam.com>> wrote in news:Noctos.110vey@excelforum-nospam.com: > My spreadsheet contains a fill out form, i was wondering if it was > possible to save this form as a webpage whereit can be viewed and > filled out. Is it also pos...

How to edit a macro
Hello - I recently upgraded to MS Access 2007. I need to edit one macro and delete another. I can't find where I can do this!! It's as if my macros were lost. Any help is greatly appreciated! -- Sheldon Can't help you there, there should be there, but you have to look around on the different tabs of the Ribbon. P.S. Welcome to the new and improved Microsoft Office with an intutitve approach that enables a user to quickly and easily complete the tasks that he or she is attempting without having to search around in various menu options. "Sheldon&quo...

Some macros not working on shared workbook
Hi all, I'm sharing a workbook but one of the macros isn't working: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "J:J" Dim rng1 As Range Dim rng2 As Range If Target.Cells.Count > 1 Then Exit Sub End If If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Exit Sub End If Set rng1 = Target.EntireRow.Range("A1:J1") With Worksheets("outcomes") Set rng2 = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0) End With On Error GoTo ws_exit: Application.EnableEvents = False If Targe...

Excel Macro: insert formula into first blank cell in column & auto
Okay, I have tried searching the group as well as google but so far I'm coming up short. I'm able to put together a macro which would autofill a totally blank column down to the bottom of the data, however, I'm not sure how to make a macro that finds the first blank cell in a partially populated column, enters a formula and then fills it to the bottom. My spreadsheet is set up in the following way (this is just an example, there are more columns which the formula uses): Column A Column B Column C Client Data Y Office Clien...

trying to get sheet 1 to recognize and move student names to pg 2
Sheet 1 contains students names in column A in ascending order, columns b thru k are columns which grading numbers of either 1 point or 2 points, column l is the total points of colums b thru k. Sheet 2 contains 5 columns , column a is 17 to 20 points, column b is 13 to 16 points, column c is 9 to 12 points, 5 to 8 points, column d is 5 to 8 points and column e is 0 to 4 points. I need sheet 1 column L to recognize sheet 2 and move the students name from column A in that row to the graph on sheet 2 according to the point system Is this one-time operation? You have only 5 c...

Excel VBA Macro Catch 22
Hi Folks, This has got me tearing my hair out. [What's left of it....;-) ] I have an Excel macro that copies data from one workbook to another 'history' one. The relevant bit of the code is:- ' Open Standard Weekly Book & Copy Weekly Sheet Workbooks("whcasht.xls").Worksheets("Weekly Sheet").Activate ActiveSheet.Cells.Select Application.CutCopyMode = False Selection.Copy ' Open Backup Workbook & Paste Weekly Sheet Workbooks(FileDateX).Worksheets("Sheet1").Activate ActiveSheet.Paste This works every time...

How to select a recorded Macro from a Custom POS button
I am using a touchscreen and have the custom keyboard laid out and would like to add some quick links to recorded macro's through the Custom POS button. I have found the way to do it for standard buttons but not for those created by myself. -- Thank you for the help!! Richard Look here: http://homepage.mac.com/tomncora/FileSharing10.html Download the README file from the AltKeys directory. READ IT. Download as much or as little as you need to make it work. I've been using it for nearly two years - works great. Tom -- Stop fishing for e-mail "RikPage" wrote: >...

How can I save a single Sheet with range not the workbook?
I've been playing around for some time and just can not figure out how to save a single sheet with a range of A1:I6 to a file. Is it even possable? Here is what I've used so far. It works great if I needed the entire woorkbook. I'm a real newbie so I don't know what to look for. Public Sub SaveAsA1() Dim Location, ThisFile As String, ThisSheet As String ThisFile = Sheets("Save").Range("A1:I6").Select If ThisFile = "" Then Exit Sub ThisFile = Location + ThisSheet ActiveSheet.SaveAs Filename:="C:\Excel\A1\A1-" & Format(Time, &q...

How can I show sheet names in different colours
Using Excel 2000. In a workbook of many sheets i want to make the Sheet names different colours to highlight each name and also to reference a particular sheet by using te colours elsewhere Hi you can't. Excel 2002 or higher is required for this feature -- Regards Frank Kabel Frankfurt, Germany "dabblerdave" <dabblerdave@discussions.microsoft.com> schrieb im Newsbeitrag news:E82E521E-6FBC-48FB-A2C4-63F5B099B291@microsoft.com... > Using Excel 2000. In a workbook of many sheets i want to make the Sheet names > different colours to highlight each name and also to re...

macro related
i have a worksheet of Good receipts in which i have to do ageing of various items in following manner - 90 days,90 to 180 days, 180 to 365 days, above 365 days. Then i have to do sorting & thereafter subtotal. i have to prepare this report on monthly basis......i can set the macro but main problem is that no of rows in a worksheet are not same, sometimes it may be less or it may be more. any solution to this??? Why not use a formula in another column =IF(A1>TODAY()-90,1,IF(A1>TODAY()-180,2,IF(A1>TODAY()-365,3,4))) and then drop it into a pivot table for analysis using th...