Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Hiding sheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Hiding sheets
Hello All,

I have numerous sheets and I would like to show or hide
them based on the month. IE. If it is Jan hide all my
other sheets apart from the Jan sheet, this is to stop
people seeing the data.

I got the following code from an MVP from this site. But
I don't know what to do with it. I'm a beginner in Vb so
please bear with me!!

Dim MyMonth As Integer
MyMonth = Month(Now())

Select Case MyMonth

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("Mar").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Case 3 'If Month number is 3 (Mar)
Sheets("Jan").Visible = False
Sheets("Mar").Visible = True
Sheets("Feb").Visible = False
End Select
End Sub

The Expert suggested I put this in the Open workbook
event??? Where is that please?

Thank for reading

Natalie
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Hiding sheets
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and then enter

Private Sub Workbook_Open()

.... your code

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Natalie" wrote in message
...
Hello All,

I have numerous sheets and I would like to show or hide
them based on the month. IE. If it is Jan hide all my
other sheets apart from the Jan sheet, this is to stop
people seeing the data.

I got the following code from an MVP from this site. But
I don't know what to do with it. I'm a beginner in Vb so
please bear with me!!

Dim MyMonth As Integer
MyMonth = Month(Now())

Select Case MyMonth

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("Mar").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Case 3 'If Month number is 3 (Mar)
Sheets("Jan").Visible = False
Sheets("Mar").Visible = True
Sheets("Feb").Visible = False
End Select
End Sub

The Expert suggested I put this in the Open workbook
event??? Where is that please?

Thank for reading

Natalie


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 983
Default Hiding sheets
Here is smoe code to do the hiding thing that you want.

Option Explicit

Private Sub Workbook_Open()
Dim wks As Worksheet
Dim strCurrentMonth As String

strCurrentMonth = MonthString
For Each wks In Worksheets
If wks.Name < strCurrentMonth Then wks.Visible = xlSheetHidden
Next wks
End Sub

Private Function MonthString() As String
Dim strMonth As String
Dim intMonth As Integer

intMonth = Month(Date)

Select Case intMonth
Case 1
strMonth = "Jan"
Case 2
strMonth = "Feb"
Case 3
strMonth = "Mar"
Case 4
strMonth = "Apr"
Case 5
strMonth = "May"
Case 6
strMonth = "Jun"
Case 7
strMonth = "Jul"
Case 8
strMonth = "Aug"
Case 9
strMonth = "Sep"
Case 10
strMonth = "Oct"
Case 11
strMonth = "Nov"
Case 12
strMonth = "Dec"

End Select
MonthString = strMonth
End Function

Paste this into the ThisWorkbook object in the VB Editor. If you want to
modify your own code the Workbook_Open event is found in the ThisWorkbook
object. Click on the Drop down that says General and where it says
Declarations select the event you want...

Try both my code and yours and play with them a bit. You can step through
them using the F8 key...

HTH

"Natalie" wrote:

Hello All,

I have numerous sheets and I would like to show or hide
them based on the month. IE. If it is Jan hide all my
other sheets apart from the Jan sheet, this is to stop
people seeing the data.

I got the following code from an MVP from this site. But
I don't know what to do with it. I'm a beginner in Vb so
please bear with me!!

Dim MyMonth As Integer
MyMonth = Month(Now())

Select Case MyMonth

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("Mar").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Case 3 'If Month number is 3 (Mar)
Sheets("Jan").Visible = False
Sheets("Mar").Visible = True
Sheets("Feb").Visible = False
End Select
End Sub

The Expert suggested I put this in the Open workbook
event??? Where is that please?

Thank for reading

Natalie

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default Hiding sheets
Hey, I think I can help with this!

Open up VB (from Excel) by clicking Alt+F11. You should see a list of your
worksheets on the Project Explorer pane. It's usually named "Project -
VBAProject" and resides on the upper left corner. If you don't see the
Project Explorer go to View--Project Explorer. After the worksheets you
should see a workbook by the name of "ThisWorkbook". Double-click this guy
and paste your code in this window (wrapped around the following code):

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
<Your (or MVPs) code here
End Sub

Hope this helps,
-Fabricio


"Natalie" wrote:

Hello All,

I have numerous sheets and I would like to show or hide
them based on the month. IE. If it is Jan hide all my
other sheets apart from the Jan sheet, this is to stop
people seeing the data.

I got the following code from an MVP from this site. But
I don't know what to do with it. I'm a beginner in Vb so
please bear with me!!

Dim MyMonth As Integer
MyMonth = Month(Now())

Select Case MyMonth

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("Mar").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Case 3 'If Month number is 3 (Mar)
Sheets("Jan").Visible = False
Sheets("Mar").Visible = True
Sheets("Feb").Visible = False
End Select
End Sub

The Expert suggested I put this in the Open workbook
event??? Where is that please?

Thank for reading

Natalie

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Hiding sheets
Jim,

You probably need to modify that Open code for subsequent runs, when only
one sheet is showing, and the month changes

Private Sub Workbook_Open()
Dim wks As Worksheet
Dim strCurrentMonth As String

strCurrentMonth = MonthString
For Each wks In Worksheets
If wks.Name = strCurrentMonth Then
wks.Visible = xlSheetVisible
Else
wks.Visible = xlSheetHidden
End If
Next wks
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
Here is smoe code to do the hiding thing that you want.

Option Explicit

Private Sub Workbook_Open()
Dim wks As Worksheet
Dim strCurrentMonth As String

strCurrentMonth = MonthString
For Each wks In Worksheets
If wks.Name < strCurrentMonth Then wks.Visible = xlSheetHidden
Next wks
End Sub

Private Function MonthString() As String
Dim strMonth As String
Dim intMonth As Integer

intMonth = Month(Date)

Select Case intMonth
Case 1
strMonth = "Jan"
Case 2
strMonth = "Feb"
Case 3
strMonth = "Mar"
Case 4
strMonth = "Apr"
Case 5
strMonth = "May"
Case 6
strMonth = "Jun"
Case 7
strMonth = "Jul"
Case 8
strMonth = "Aug"
Case 9
strMonth = "Sep"
Case 10
strMonth = "Oct"
Case 11
strMonth = "Nov"
Case 12
strMonth = "Dec"

End Select
MonthString = strMonth
End Function

Paste this into the ThisWorkbook object in the VB Editor. If you want to
modify your own code the Workbook_Open event is found in the ThisWorkbook
object. Click on the Drop down that says General and where it says
Declarations select the event you want...

Try both my code and yours and play with them a bit. You can step through
them using the F8 key...

HTH

"Natalie" wrote:

Hello All,

I have numerous sheets and I would like to show or hide
them based on the month. IE. If it is Jan hide all my
other sheets apart from the Jan sheet, this is to stop
people seeing the data.

I got the following code from an MVP from this site. But
I don't know what to do with it. I'm a beginner in Vb so
please bear with me!!

Dim MyMonth As Integer
MyMonth = Month(Now())

Select Case MyMonth

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("Mar").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Case 3 'If Month number is 3 (Mar)
Sheets("Jan").Visible = False
Sheets("Mar").Visible = True
Sheets("Feb").Visible = False
End Select
End Sub

The Expert suggested I put this in the Open workbook
event??? Where is that please?

Thank for reading

Natalie



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 983
Default Hiding sheets
Good point... Go with Bob's code... Mine would be unhappy when the month
rolled over... No visisble sheets can be a problem... :)

"Bob Phillips" wrote:

Jim,

You probably need to modify that Open code for subsequent runs, when only
one sheet is showing, and the month changes

Private Sub Workbook_Open()
Dim wks As Worksheet
Dim strCurrentMonth As String

strCurrentMonth = MonthString
For Each wks In Worksheets
If wks.Name = strCurrentMonth Then
wks.Visible = xlSheetVisible
Else
wks.Visible = xlSheetHidden
End If
Next wks
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
Here is smoe code to do the hiding thing that you want.

Option Explicit

Private Sub Workbook_Open()
Dim wks As Worksheet
Dim strCurrentMonth As String

strCurrentMonth = MonthString
For Each wks In Worksheets
If wks.Name < strCurrentMonth Then wks.Visible = xlSheetHidden
Next wks
End Sub

Private Function MonthString() As String
Dim strMonth As String
Dim intMonth As Integer

intMonth = Month(Date)

Select Case intMonth
Case 1
strMonth = "Jan"
Case 2
strMonth = "Feb"
Case 3
strMonth = "Mar"
Case 4
strMonth = "Apr"
Case 5
strMonth = "May"
Case 6
strMonth = "Jun"
Case 7
strMonth = "Jul"
Case 8
strMonth = "Aug"
Case 9
strMonth = "Sep"
Case 10
strMonth = "Oct"
Case 11
strMonth = "Nov"
Case 12
strMonth = "Dec"

End Select
MonthString = strMonth
End Function

Paste this into the ThisWorkbook object in the VB Editor. If you want to
modify your own code the Workbook_Open event is found in the ThisWorkbook
object. Click on the Drop down that says General and where it says
Declarations select the event you want...

Try both my code and yours and play with them a bit. You can step through
them using the F8 key...

HTH

"Natalie" wrote:

Hello All,

I have numerous sheets and I would like to show or hide
them based on the month. IE. If it is Jan hide all my
other sheets apart from the Jan sheet, this is to stop
people seeing the data.

I got the following code from an MVP from this site. But
I don't know what to do with it. I'm a beginner in Vb so
please bear with me!!

Dim MyMonth As Integer
MyMonth = Month(Now())

Select Case MyMonth

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("Mar").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Case 3 'If Month number is 3 (Mar)
Sheets("Jan").Visible = False
Sheets("Mar").Visible = True
Sheets("Feb").Visible = False
End Select
End Sub

The Expert suggested I put this in the Open workbook
event??? Where is that please?

Thank for reading

Natalie




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default Hiding sheets
And one more...

Option Explicit
Sub auto_open()

Dim iCtr As Long
Dim CurMonthName As String

CurMonthName = Format(Date, "mmm")

On Error Resume Next 'just in case of naming problems
'show the current month
Worksheets(CurMonthName).Visible = True
For iCtr = 1 To Worksheets.Count
If LCase(Worksheets(iCtr).Name) = LCase(CurMonthName) Then
'do nothing--it's already shown
Else
Worksheets(iCtr).Visible = False
End If
Next iCtr
On Error GoTo 0

End Sub

I think you'll find it's better to show the one worksheet first. Then hide all
the others.

If you get unlucky, you maybe hiding a worksheet that was the only visible
sheet.

If that happens, then your code will break, since there always has to be at
least one visible sheet in your workbook.

Are you any relation to JudithJubilee? She asked a similar question recently.

Natalie wrote:

Hello All,

I have numerous sheets and I would like to show or hide
them based on the month. IE. If it is Jan hide all my
other sheets apart from the Jan sheet, this is to stop
people seeing the data.

I got the following code from an MVP from this site. But
I don't know what to do with it. I'm a beginner in Vb so
please bear with me!!

Dim MyMonth As Integer
MyMonth = Month(Now())

Select Case MyMonth

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("Mar").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Case 3 'If Month number is 3 (Mar)
Sheets("Jan").Visible = False
Sheets("Mar").Visible = True
Sheets("Feb").Visible = False
End Select
End Sub

The Expert suggested I put this in the Open workbook
event??? Where is that please?

Thank for reading

Natalie

--

Dave Peterson
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Hiding sheets
Thankyou all for your help. I have it working now!!!

Natalie

-----Original Message-----
Hey, I think I can help with this!

Open up VB (from Excel) by clicking Alt+F11. You should
see a list of your
worksheets on the Project Explorer pane. It's usually
named "Project -
VBAProject" and resides on the upper left corner. If
you don't see the
Project Explorer go to View--Project Explorer. After
the worksheets you
should see a workbook by the name of "ThisWorkbook".
Double-click this guy
and paste your code in this window (wrapped around the
following code):

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
<Your (or MVPs) code here
End Sub

Hope this helps,
-Fabricio


"Natalie" wrote:

Hello All,

I have numerous sheets and I would like to show or
hide
them based on the month. IE. If it is Jan hide all my
other sheets apart from the Jan sheet, this is to stop
people seeing the data.

I got the following code from an MVP from this site.
But
I don't know what to do with it. I'm a beginner in Vb
so
please bear with me!!

Dim MyMonth As Integer
MyMonth = Month(Now())

Select Case MyMonth

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("Mar").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Case 3 'If Month number is 3 (Mar)
Sheets("Jan").Visible = False
Sheets("Mar").Visible = True
Sheets("Feb").Visible = False
End Select
End Sub

The Expert suggested I put this in the Open workbook
event??? Where is that please?

Thank for reading

Natalie

.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
hiding zero values on all sheets & by default on new sheets WiFiMike2006 Excel Worksheet Functions 4 January 19th 07 08:13 PM
Hiding sheets phil2006 Excel Discussion (Misc queries) 4 August 15th 06 07:20 PM
Locking Sheets / Hiding Sheets Lee Harris Excel Worksheet Functions 4 November 29th 05 07:21 AM
Hiding Sheets Rain Excel Worksheet Functions 10 January 18th 05 08:11 PM
Hiding Sheets Eric[_14_] Excel Programming 4 November 26th 03 06:38 PM


All times are GMT +1. The time now is 05:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement