Streamlining VBA code.......Damon

[Ian Mac]

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

 

[Guest]

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?

 

[Damon Ostrander]

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.

 

[Ian Mac]

Thanks Damon,

Guys any thoughts on the other way round this.

(see above)

 

[Dave Hawley]

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

 

[Ian Mac]

Top Quality,

I'll check it out tomorrow.

 

[Dave Hawley]

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