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 ]

[Damon Ostrander]

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

[Ian Mac]

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

[Mudface]

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.

[Dave Hawley]

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.

[Ian Mac]

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

[Dave Hawley]

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

[Mudface]

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.

[Ian Mac]

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 ]

[Dave Hawley]

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