Re: Sheet name ref in cell B2 and then!

[Jay]

Guys, what I'd like to do is a two part process:

1.) Show the current sheet name in cell B2 of that sheet. I have the sheets
named as: Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc

Then

2.) When the work book is opened, to open it to the current/active month
(Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc), and keep the
other similarly named month sheets, minus the review sheet hidden. This, I
know will require some coding, maybe not, I'm not sure. Given the nature of
this application, however, would the second (question #2) part be possible
also?

Looking forward to your reply....

Thanks

[[email protected]]

1) As far as I know there isn't a way to get the sheet name without
using VBA, using VBA though you could put some code in the workbook
sheet activate event, something like:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("B2").Value = ActiveSheet.Name
End Sub

What this will do is everytime a sheet is selected the sheet name will
be put into Range B2.

2) You would have to put some code in the workbook open event something
like:

Private Sub Workbook_Open()
Dim xSheet As Worksheet
Dim tmpDate As String
tmpDate = Format(Date, "mmm-yy")
Application.enableevents = false
For Each xSheet In ThisWorkbook.Sheets
If xSheet.Name <> tmpDate Then
xSheet.Visible = xlHidden
End If
Next xSheet
Application.EnableEvents = true
Range("B2").value = Activesheet.name
End Sub

This would get the system date and format it to the first three letters
of the month followed by the year so at the minute it would return
Aug-06, the rest of the code will cycle through all of your sheets
hiding any sheets that do not match this name. The precursor to this
approach would be that you would have to be very consistent when naming
your worksheets, secondly you would have to make sure that the sheet
exists before the month ie the worksheet Sep-06 is created before
September. Either that or you would need some error handling which in
this instance I would suggest anyway.

I wasn't sure if you wanted your review sheet visible or not, if you
want it visible just change this "If xSheet.Name <> tmpDate Then" line
to:

If xSheet.Name <> tmpDate and xSheet <> "Review Sheet" Then

James

Jay wrote:

> Guys, what I'd like to do is a two part process:
>
> 1.) Show the current sheet name in cell B2 of that sheet. I have the sheets
> named as: Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc
>
> Then
>
> 2.) When the work book is opened, to open it to the current/active month
> (Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc), and keep the
> other similarly named month sheets, minus the review sheet hidden. This, I
> know will require some coding, maybe not, I'm not sure. Given the nature of
> this application, however, would the second (question #2) part be possible
> also?
>
> Looking forward to your reply....
>
> Thanks

[Dave Peterson]

#1. http://contextures.com/xlfaqFun.html#SheetName
(From Debra Dalgleish's site)

#2. You'll need a macro. But your worksheet names are non-standard. Did you
want to abbreviate the month name or not? I'm assuming that you abbreviated to
3 characters.

Option Explicit
Sub auto_Open()
Dim wks As Worksheet
Dim RevWks As Worksheet
Dim myName As String

Set RevWks = ThisWorkbook.Worksheets("Review")

RevWks.Visible = xlSheetVisible

For Each wks In ThisWorkbook.Worksheets
If wks.Name = RevWks.Name Then
'skip it
Else
wks.Visible = xlSheetHidden
End If
Next wks

myName = Format(Date, "mmm yyyy") ' "mmmm yyyy" maybe????

On Error Resume Next
ThisWorkbook.Worksheets(myName).Visible = xlSheetVisible
If Err.Number <> 0 Then
MsgBox "Missing worksheet named: " & myName _
& vbLf & "Please contact Jay!"
Err.Clear
End If
On Error GoTo 0

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Jay wrote:
>
> Guys, what I'd like to do is a two part process:
>
> 1.) Show the current sheet name in cell B2 of that sheet. I have the sheets
> named as: Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc
>
> Then
>
> 2.) When the work book is opened, to open it to the current/active month
> (Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc), and keep the
> other similarly named month sheets, minus the review sheet hidden. This, I
> know will require some coding, maybe not, I'm not sure. Given the nature of
> this application, however, would the second (question #2) part be possible
> also?
>
> Looking forward to your reply....
>
> Thanks

--

Dave Peterson

[Jay]

Dave:

I've tried this one (the second part) and it failed. I've looked at the link
you suggested on Macro's. I'm still lost, a bit, I think! Should I drop this
section of code in the Macro section VB editor?

Thanks,



"Dave Peterson" wrote:

> #1. http://contextures.com/xlfaqFun.html#SheetName
> (From Debra Dalgleish's site)
>
> #2. You'll need a macro. But your worksheet names are non-standard. Did you
> want to abbreviate the month name or not? I'm assuming that you abbreviated to
> 3 characters.
>
> Option Explicit
> Sub auto_Open()
> Dim wks As Worksheet
> Dim RevWks As Worksheet
> Dim myName As String
>
> Set RevWks = ThisWorkbook.Worksheets("Review")
>
> RevWks.Visible = xlSheetVisible
>
> For Each wks In ThisWorkbook.Worksheets
> If wks.Name = RevWks.Name Then
> 'skip it
> Else
> wks.Visible = xlSheetHidden
> End If
> Next wks
>
> myName = Format(Date, "mmm yyyy") ' "mmmm yyyy" maybe????
>
> On Error Resume Next
> ThisWorkbook.Worksheets(myName).Visible = xlSheetVisible
> If Err.Number <> 0 Then
> MsgBox "Missing worksheet named: " & myName _
> & vbLf & "Please contact Jay!"
> Err.Clear
> End If
> On Error GoTo 0
>
> End Sub
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Jay wrote:
> >
> > Guys, what I'd like to do is a two part process:
> >
> > 1.) Show the current sheet name in cell B2 of that sheet. I have the sheets
> > named as: Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc
> >
> > Then
> >
> > 2.) When the work book is opened, to open it to the current/active month
> > (Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc), and keep the
> > other similarly named month sheets, minus the review sheet hidden. This, I
> > know will require some coding, maybe not, I'm not sure. Given the nature of
> > this application, however, would the second (question #2) part be possible
> > also?
> >
> > Looking forward to your reply....
> >
> > Thanks
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I don't know what you mean by failed...

But this may help get you started:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Make sure macros are enabled when you open the workbook, too.

Jay wrote:
>
> Dave:
>
> I've tried this one (the second part) and it failed. I've looked at the link
> you suggested on Macro's. I'm still lost, a bit, I think! Should I drop this
> section of code in the Macro section VB editor?
>
> Thanks,
>
> "Dave Peterson" wrote:
>
> > #1. http://contextures.com/xlfaqFun.html#SheetName
> > (From Debra Dalgleish's site)
> >
> > #2. You'll need a macro. But your worksheet names are non-standard. Did you
> > want to abbreviate the month name or not? I'm assuming that you abbreviated to
> > 3 characters.
> >
> > Option Explicit
> > Sub auto_Open()
> > Dim wks As Worksheet
> > Dim RevWks As Worksheet
> > Dim myName As String
> >
> > Set RevWks = ThisWorkbook.Worksheets("Review")
> >
> > RevWks.Visible = xlSheetVisible
> >
> > For Each wks In ThisWorkbook.Worksheets
> > If wks.Name = RevWks.Name Then
> > 'skip it
> > Else
> > wks.Visible = xlSheetHidden
> > End If
> > Next wks
> >
> > myName = Format(Date, "mmm yyyy") ' "mmmm yyyy" maybe????
> >
> > On Error Resume Next
> > ThisWorkbook.Worksheets(myName).Visible = xlSheetVisible
> > If Err.Number <> 0 Then
> > MsgBox "Missing worksheet named: " & myName _
> > & vbLf & "Please contact Jay!"
> > Err.Clear
> > End If
> > On Error GoTo 0
> >
> > End Sub
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > Jay wrote:
> > >
> > > Guys, what I'd like to do is a two part process:
> > >
> > > 1.) Show the current sheet name in cell B2 of that sheet. I have the sheets
> > > named as: Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc
> > >
> > > Then
> > >
> > > 2.) When the work book is opened, to open it to the current/active month
> > > (Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc), and keep the
> > > other similarly named month sheets, minus the review sheet hidden. This, I
> > > know will require some coding, maybe not, I'm not sure. Given the nature of
> > > this application, however, would the second (question #2) part be possible
> > > also?
> > >
> > > Looking forward to your reply....
> > >
> > > Thanks
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Jay]

Thanks, Dave, that too worked. I appreciate all the assistance! You're great!

"Dave Peterson" wrote:

> I don't know what you mean by failed...
>
> But this may help get you started:
>
> Open your workbook.
> Hit alt-f11 to get to the VBE (where macros/UDF's live)
> hit ctrl-R to view the project explorer
> Find your workbook.
> should look like: VBAProject (yourfilename.xls)
>
> right click on the project name
> Insert, then Module
> You should see the code window pop up on the right hand side
>
> Paste the code in there.
>
> Make sure macros are enabled when you open the workbook, too.
>
> Jay wrote:
> >
> > Dave:
> >
> > I've tried this one (the second part) and it failed. I've looked at the link
> > you suggested on Macro's. I'm still lost, a bit, I think! Should I drop this
> > section of code in the Macro section VB editor?
> >
> > Thanks,
> >
> > "Dave Peterson" wrote:
> >
> > > #1. http://contextures.com/xlfaqFun.html#SheetName
> > > (From Debra Dalgleish's site)
> > >
> > > #2. You'll need a macro. But your worksheet names are non-standard. Did you
> > > want to abbreviate the month name or not? I'm assuming that you abbreviated to
> > > 3 characters.
> > >
> > > Option Explicit
> > > Sub auto_Open()
> > > Dim wks As Worksheet
> > > Dim RevWks As Worksheet
> > > Dim myName As String
> > >
> > > Set RevWks = ThisWorkbook.Worksheets("Review")
> > >
> > > RevWks.Visible = xlSheetVisible
> > >
> > > For Each wks In ThisWorkbook.Worksheets
> > > If wks.Name = RevWks.Name Then
> > > 'skip it
> > > Else
> > > wks.Visible = xlSheetHidden
> > > End If
> > > Next wks
> > >
> > > myName = Format(Date, "mmm yyyy") ' "mmmm yyyy" maybe????
> > >
> > > On Error Resume Next
> > > ThisWorkbook.Worksheets(myName).Visible = xlSheetVisible
> > > If Err.Number <> 0 Then
> > > MsgBox "Missing worksheet named: " & myName _
> > > & vbLf & "Please contact Jay!"
> > > Err.Clear
> > > End If
> > > On Error GoTo 0
> > >
> > > End Sub
> > >
> > > If you're new to macros, you may want to read David McRitchie's intro at:
> > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >
> > > Jay wrote:
> > > >
> > > > Guys, what I'd like to do is a two part process:
> > > >
> > > > 1.) Show the current sheet name in cell B2 of that sheet. I have the sheets
> > > > named as: Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc
> > > >
> > > > Then
> > > >
> > > > 2.) When the work book is opened, to open it to the current/active month
> > > > (Jan 2006, Feb 2006, March 2006, April 2006, etc, etc, etc), and keep the
> > > > other similarly named month sheets, minus the review sheet hidden. This, I
> > > > know will require some coding, maybe not, I'm not sure. Given the nature of
> > > > this application, however, would the second (question #2) part be possible
> > > > also?
> > > >
> > > > Looking forward to your reply....
> > > >
> > > > Thanks
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>