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