Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: VBA Moving Sheets (and Hiding/Unhiding)

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have to quick questions. At the end of one of my macros I need to move three sheets to the very front of the line and scroll to see those tabs. The sheets in order are "Macro", "Data", and "MyChart". I've been using:

    Sheets("Macro").Select
    Sheets("Macro").Move before:=Sheets(1)
    ...

    but I get a runtime 1004 error. Any ideas?

    My second question is if there is a way to hide/unhide sheets in VBA. I have about 35 sheets in my workbook. I would like to hide them all, but the macros need them to not be hidden, so can I just have them appear for the macro to use? Thanks for the help.

  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi. Please Try.


    Sub WorkSheetsShow()
    Dim sh As Worksheet
    For Each sh In Worksheets
    sh.Visible = xlSheetVisible
    Next
    End Sub

    Sub WorkSheetsHidden()
    Dim sh As Worksheet, arr
    arr = Array("Macro", "Data", "Chart") 'Change Name of Sheet you don't want hide.
    For Each sh In Worksheets
    If IsError(Application.Match(sh.Name, arr, 0)) Then
    sh.Visible = xlSheetHidden
    End If
    Next
    End Sub

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are there other moves in your code, because that works when i use it. As for hide and unhide, yes you can do it from VBA:

    Sheets("Macro").Visible = False

    and

    Sheets("Macro").Visible = True to unhide it




  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The moves I have are:

    Sheets("Macro").Select
    Sheets("Macro").Move before:=Sheets(1)
    Sheets("MyChart").Select
    Sheets("MyChart").Move before:=Sheets(2)
    Sheets("Data").Select
    Sheets("Data").Move before:=Sheets(3)

    I'm really not sure why I'm having problems with this. Are there other factors that could complicate a simple move?

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I figured it out. I had several combo-boxes (from Control Toolbox) on the Macro sheet. If the last activity on that sheet was choosing something from a combo-box, the move command hit a runtime error. If I simply inserted any cell selection before the move, it worked flawlessly. Interesting...

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    The code you have should work, but make sure you do not have any sort of protection applied (Workbook in particular). The code could be shortened to:

    Code:
    Sub MoveSheets()
    
    Sheets("Macro").Move before:=Sheets(1)
    Sheets("MyChart").Move before:=Sheets(2)
    Sheets("Data").Move before:=Sheets(3)
    
    End Sub
    Which brings me to my next point. You should not need to unhide sheets to run a macro in VBA. In fact it is very rare you need to Select or Activate an Object to change its Properties or run Methods.

    In regards to your sheet hide/unhide code try this:

    Code:
    Sub ToggleHide()
    Dim wsSheet As Worksheet
    
        For Each wsSheet In ActiveWorkbook.Worksheets
            wsSheet.Visible (Not wsSheet.Visible)
        Next wsSheet
    
    End Sub

    One very important point here is that you CANNOT hide all sheets in a Workbook, there must be at least one visible.




Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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