Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet name ref in cell B2 and then!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet name ref in cell B2 and then!
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet name ref in cell B2 and then!
#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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet name ref in cell B2 and then!
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet name ref in cell B2 and then!
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet name ref in cell B2 and then!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copying data from a cell on sheet to a diff cell/sheet | Excel Worksheet Functions | |||
copy a sum in cell on sheet 1 (not formula) to cell on sheet 2 | Excel Worksheet Functions | |||
copy a sum in cell on sheet 1 (not formula) to cell on sheet 2 | Excel Worksheet Functions | |||
Excell:Move from any Cell Sheet 1 to any cell Sheet 2 etc. | Excel Worksheet Functions | |||
Excel VBA (?!)-refer to a cell on Sheet to left of X, based on criteria on Sheet X | Excel Programming |