Streamlining VBA code.......Damon
Streamlining VBA code.......Damon
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

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

    Default

     
    All,

    I'm looking for a way to compact the following code:

    Private Sub ShowFriday_Click()

    Application.ScreenUpdating = False
    Sheets("TeamTotalsFri").Visible = True
    Sheets("James Third Fri").Visible = True
    Sheets("Eleanor Bell Fri").Visible = True
    Sheets("Alan Coombs Fri").Visible = True
    Sheets("Andrew Hedley Fri").Visible = True
    Sheets("Pete Walton Fri").Visible = True
    Sheets("David Holliday Fri").Visible = True
    Sheets("David Thornton Fri").Visible = True
    Sheets("TeamTotalsFri").Select
    Application.ScreenUpdating = True

    End Sub

    I have the same code for sheets mon - sun, currently I have 7 Buttons ShowMonday - ShowSunday.

    I'd like to have 1 code which will unhide any sheets depanding on which button is pressed?

    I also need the same thing for:

    Private Sub CloseFriday_Click()
    Application.ScreenUpdating = False
    Sheets("TeamTotalsFri").Visible = False
    Sheets("James Third Fri").Visible = False
    Sheets("Eleanor Bell Fri").Visible = False
    Sheets("Alan Coombs Fri").Visible = False
    Sheets("Andrew Hedley Fri").Visible = False
    Sheets("Pete Walton Fri").Visible = False
    Sheets("David Holliday Fri").Visible = False
    Sheets("David Thornton Fri").Visible = False
    Sheets("WeeklySummarySheet").Select
    Application.ScreenUpdating = True
    End Sub

    Which maybe easier as the button for these codes are on sheets named TeamTotalsFri etc.

    Any pointers,

    Thanks

    Ian Mac

    [ This Message was edited by: Ian Mac on 2002-03-04 03:04 ]

  2. #2
    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 Ian Mac,

    If the idea is to unhide all sheets whose names end with "Fri" then

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



    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. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-02 13:26, Damon Ostrander wrote:
    Hi Ian Mac,

    If the idea is to unhide all sheets whose names end with "Fri" then

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

    the you provided looks alright to me (but what do I know). I keep getting a:

    User-defined type not defined

    error, at the

    Dim Sh as Sheet

    line I changed the sheet to sheets and it goes past that line to

    If Right(Sh.Name, 3) = "Fri" Then

    and get a

    Method or data member not found error.

    it doesn't like the Name part,

    Any suggestions?

    Many thanks

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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Don't want to butt in but I think you could just change the Sheet to Worksheet or Object-

    Dim sh as Worksheet

    should be OK.

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

    Default

    Hi Ian

    Call each of your buttons the same name as the Worksheet they hide/Unhide then assign them all to this code:

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

    This will toggle the visibility of the sheet in question.



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

    Default

    On 2002-03-04 03:14, Mudface wrote:
    Don't want to butt in but I think you could just change the Sheet to Worksheet or Object-

    Dim sh as Worksheet

    should be OK.
    Not butting in at all....
    Worksheet does the job but I now have to add a next somewhere, this something I've never used.

    Many thanks


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

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

    Default

    BTW, if you do use the other helpers code try:

    Option Compare Text
    Sub AnotherWay()
    Dim Sh As Worksheet
    Application.ScreenUpdating = False

    For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name Like ("* Fri") Then
    Sh.Visible = True
    End If
    Sheets("TeamTotalsFri").Select
    Application.ScreenUpdating = True

    End Sub

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hehe, a surfeit of ways to do it

    Just add the Next after the End If statement. You don't need to use Next sh, but it's a good habit to get into, as it makes your code more readable.

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

    Default

    On 2002-03-04 03:17, Dave Hawley wrote:
    Hi Ian

    Call each of your buttons the same name as the Worksheet they hide/Unhide then assign them all to this code:

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

    This will toggle the visibility of the sheet in question.


    Thanks for all the input guys,

    I'm currently using Damon's code amended by Mudface (Thank-you both), although I do like the I'd of using one code for all, the problem being that there are a lot of sheets ending in the day (see original post) and I can't see how the above code would handle lots of sheets.

    I have re-named all the buttons to Mon - Fri rather than the ShowMonday etc. I had.
    How can I use either code to achieve this??

    Again thank-you so far, Fantastic work out there.



    [ This Message was edited by: Ian Mac on 2002-03-04 04:13 ]

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

    Default

      
    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

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