Streamlining VBA code.......Damon - Page 2
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Streamlining VBA code.......Damon

  1. #11
    Guest

    Default

     
    On 2002-03-04 04:19, Dave Hawley wrote:
    Just name the buttons "TeamTotalsFri" etc. Don't confuse the name with the Caption though! To name a button from the Forms toolbar simply select it then type the name in the name box (to left of Formula bar) just as you would a named range.

    Then simply assign ALL your buttons to this one tiny Procedure:

    Sub WhichButton()
    Dim strSheetName As String
    strSheetName = Application.Caller
    Sheets(strSheetName).Visible = Not Sheets(strSheetName).Visible
    End Sub
    Dave,

    I could name the buttons "TeamTotalsFri" etc.
    but it doesn't solve the problem of opening all the other sheets with "*Fri" at the end!

    I don't see how your code handles this?


  2. #12
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again Ian,

    I apologize. I broke an important rule never to post code without checking it out first. Here is what the code should have been (complete with the Next Sh statement you noticed was missing):

    Application.ScreenUpdating = False
    Dim Sh As Object
    For Each Sh In Sheets
    If Right(Sh.Name, 3) = "Fri" Then
    Sh.Visible = True
    End If
    Next Sh
    Sheets("TeamTotalsFri").Select
    Application.ScreenUpdating = True

    Several posters pointed out that changing the Sh variable to a Worksheet would solve the problem with the Dim statement, which is correct. But this will not work if other types of sheets are involved (i.e., Chart sheets). Making the Sh a generic Object allows the code to work for all type of sheets, if that matters.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #13
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Damon,

    Guys any thoughts on the other way round this.

    (see above)

    "Have a good time......all the time"
    Ian Mac

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

    Default

    Looks like I made a few too may assumptions, sorry.

    Your buttons are obviously from the Control toolbox and not the Forms. Try this.

    In a standard module put this:

    Option Compare Text
    Public strSheetName As String

    Sub ToggleSheets()
    Dim wsSheet As Worksheet

    For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.Name Like (strSheetName) Then
    wsSheet.Visible = Not wsSheet.Visible
    End If
    Next wsSheet

    End Sub


    Now, in the Private module of the Worsheet housing your buttons, use some code like:

    Private Sub FriButton_Click()
    strSheetName = "*Fri"
    Run "ToggleSheets"
    End Sub

    Private Sub MonButton_Click()
    strSheetName = "*Mon"
    Run "ToggleSheets"
    End Sub




  5. #15
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Top Quality,

    I'll check it out tomorrow.
    "Have a good time......all the time"
    Ian Mac

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

    Default

      
    Forgot to mention, that unless really needed the CommandButtons from the "Forms" toolbar would be a better choice then you could just name each Fri, Mon, Tues etc and assign them all to this Procedure:

    Sub ToggleSheets()
    Dim wsSheet As Worksheet
    Dim strSheetName As String
    strSheetName = "*" & Application.Caller
    For Each wsSheet In ThisWorkbook.Worksheets
    If wsSheet.Name Like (strSheetName) Then
    wsSheet.Visible = Not wsSheet.Visible
    End If
    Next wsSheet

    End Sub

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
  •  

 

 
DMCA.com