Streamlining VBA code.......Damon

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top