Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing hidden Worksheets (Excel 2003)

    I'm working on a project that uses a number of 'work' worksheets to produce a final sheet visible to the user. These are protected, so that users can't easily change them, but I also wanted to hide them to keep things tidy and avoid temptation.

    Unfortunately, if I hide them, the macro fails, presumably because it can't access the hidden sheets. I'm accessing them by name, by the way (Sheets("Sheetname").Select) if that makes any difference.

    I'd be grateful for any suggestions.

    Mike

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    The problem is not accessing the worksheets by name, it is the use of .Select that is failing if the sheet is hidden.

    Can you rewrite your code to make use of the data on the hidden sheets without selecting them?

    StuartR

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    If you show us your code, we can help rewriting it to avoid selecting the sheets.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    If you're working with numerous hidden sheets, you could declare an object variable like:
    Dim ws As Worksheet

    then keep setting it to whatever is needed using:
    Set ws = ThisWorkbook.Sheets("Sheetname")

    For good practice, at the end of the procedure:
    Set ws = Nothing

    You might also get better isolation from the user interface by making your "working" sheets VeryHidden.
    You can do this in the VBE Properties window by setting Visible to 2, or in code using:
    ws.Visible = xlSheetVeryHidden

    Alan

  5. #5
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    Thanks for all the replies.

    I'm going to the sheets for a number of reasons. One example of how I use .Select would be to get the next sequential number for the user document that's being created:-

    Sheets("Setup").Select
    Range("NextNumber").Select
    RequestNo = ActiveCell.Value + 1
    Request$ = Format(RequestNo, 0)
    ActiveCell.Value = RequestNo
    Sheets("Request Setup").Select (back to another sheet to use the number)

    I'm not just using data from these sheets, but clearing cells, sorting and copying and pasting between sheets, so I can't do it without actually 'going' to the sheets.

    It sounds as though Alan's suggestion will do the trick (thanks, Alan), but I'd be interested in any alternatives.

    Mike

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    That code can be done much more efficiently without selecting anything, and it shoud work with the sheet hidden:

    <pre> Worksheets("Setup").Range("NextNumber").Value = Worksheets("Setup").Range("NextNumber").Value + 1
    Request$ = Format(Worksheets("Setup").Range("NextNumber").Val ue, "0")
    </pre>

    Legare Coleman

  7. #7
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    Thanks, Legare.

    I understand the logic of that, and it's obviously a lot simpler.

    I've reached my modest level in VBA pretty much by trial and error when I needed to get a job done and I'm obviously thinking in simple steps (probably a legacy from the old Excel macros ), rather than looking at the big picture.

    Your tip could change my life.

    Thanks again,

    Mike

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    It is almost never necessary to select in order to accomplish something with vba (there are a few things that can't be done any other way), and it is always better and faster not to do the select.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    Clearing, sorting and copying can also be done without selecting anything.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    I've firmly got the message now, and I'm writing all code without Selecting. Everything works fine so far, except one thing.

    I am doing a Find, based on the contents of a cell in the 'Detail' sheet:-

    GetSalesArea$ = Format(Worksheets("Detail").Range("SalesArea").Val ue, 0)
    Worksheets("CustDetail").Range("A2:A2500").Find(Wh at:=GetSalesArea$, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    If I run this from the 'Detail' sheet, it fails with a Type Mismatch message. Hovering on the variable shows that it has correctly picked up the data from the cell, and I can even edit the formula to use a constant, but it still fails. If I run it from the 'CustDetail' sheet, it works fine.

    If I change the code to:-

    Sheets("CustDetail").Select
    Range("A2:A2500").Select
    Selection.Find ..

    It also works every time.

    Is this a peculiarity of Find, or am I missing something?

    Thanks again, everyone, for all your time.

    Mike

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    The problem is probably the use of ActiveCell. This always applies to the active worksheet. If you want to find something on a worksheet that is not active, After:=ActiveCell will cause problems. You can omit the After:=... part entirely, or specify a cell explicitly: After:=Worksheets("CustDetail").Range("A2").

  12. #12
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    Thanks, Hans.

    I modified the code as you suggested, so it now reads:-

    Worksheets("CustDetail").Range("A2:A2500").Find(Wh at:=GetSalesArea$, After:=Worksheets("CustDetail").Range("A2"), _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    That avoided the Type Mismatch error, but now I'm getting a 'Activate method of Range class failed' error. Again, this happens only if I run it from another sheet. (and also happens if I omit the After: parameter completely).

    Mike

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    If CustDetail is not active, you can't activate or select a range in it. As remarked higher up in this thread, most code works more efficiently if you don't select or activate ranges. The Find method returns a Range object that you can use for further processing:

    Dim rng As Range
    Set rng = Worksheets("CustDetail").Range("A2:A2500").Find(Wh at:=GetSalesArea$, After:=Worksheets("CustDetail").Range("A2"), _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    ' do something with rng here

  14. #14
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    That worked a treat, thanks.

    But it led me on to discover another thing that doesn't seem to like the sheet not being active. The line is:-

    Worksheets("CustDetail").Range(Cells(StartRowNo, PeriodNo + 6), Cells(EndRowNo, PeriodNo + 6)).Copy

    I'm using Cells here because, as you can see, the range is decided by the content of some variables, but again it only works if the sheet is selected.

    I hope I'm not pushing my luck here - stopping my old habit of selecting things is a pretty steep learning curve and I'm really very grateful for all the help I've received.

    Mike

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing hidden Worksheets (Excel 2003)

    Does this work better:

    <pre> With Worksheets("CustDetail")
    Range(.Cells(StartRowNo, PeriodNo + 6), .Cells(endrowno, PeriodNo + 6)).Copy
    End With
    </pre>

    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •