please help with year update and inserting worksheet name

[littleme]

Pleeeease help!

Been racking my brain but am quite frankly getting tired of all of this. Have an account report to do. Got help before how to choose month from drop down and make the rest of the report of three years update itself so that the months automatically come int he right order. brilliant.

Now need a similar function for the year... The user fills in when the account was opened and chooses the right month from the drop down. Would now like the year to appear next to the month, and augment one year each time Jan comes around. the entire report is for three years.

Still looking for an answer/ explanation how to enter the worksheet name into a cell, (so that users only need to change the name on the little tab.. chances are theyll forget to write it in in two places....) help

nina

 

[Joe Was]

Re: please help with year update and inserting worksheet nam

This is sheet module code to name the sheet whatever is entered into cell: "B3." Is will be less confusing to the user than it will be renaming the Tab!

You can label the cell on the sheet something like: Add Your Name Here: ==>
then in the next cell is where the value is written.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sheet Module code!
Dim myAddress As String

myAddress = "$B$3"

If Target.Address = myAddress And Range("B3").Value <> "" Then
Range("B3").Select
ActiveSheet.Name = Range("B3").Value
End If
End Sub

As to your year problem: I do not know your sheets, data or code so I can only give you instructions on one way to do it yourself.

Trap the year with the "Year" sheet function or load "Date" into that function in code. Load a variable with that year value then use that value to change the value of the month columns [If all columns are present on your sheet] or do an if test or Select Case to load the proper month with that year or Year + 1...

 

[Te4t0n]

Re: please help with year update and inserting worksheet nam

So do you want the user to enter the name of the sheet in to a cell and then that cell value to equal the sheets name ?

We will need the month code to help you modify it to work for years

 

[littleme]

Re: please help with year update and inserting worksheet nam

The code works fine Joe Was, except it wont let me go back and change what I wrote in B3... with a slight delay the cursor jumps to another cell... and when i drag a copy of the sheet (which has to be an option) the code doesnt want to work... Gonna do some more work and see... macs are a bit tricky....

Im not sure what you mean by month code but in each cell below the initial cell with drop down it looks like this:
=offset('months!'!$A$1;MOD(Match(B5;'months!'!$A$1:$A$12;0);12);0))

Only says Jan, Feb, Mar, April.... and so forth, so no actual date. User fills in account opening in cell C2, completely independent of the drop down and month population...

hope that helps...

nina

 

[Joe Was]

Re: please help with year update and inserting worksheet nam

Try this other event:

Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet Module code!
Dim myAddress As String

myAddress = "$B$3"

If Target.Address = myAddress And Range("B3").Value <> "" Then
Range("B3").Select
ActiveSheet.Name = Range("B3").Value
End If
End Sub

Also; The code will bypass if the cell value is blank, so the new event above will not change the Sheet name to blank and the old code will let you delete the cell value and then re-enter, but does not take effect untill re-selected and entered. I think you will find the new event works better!

To get the Sheet Re-Name to work on a copy of the sheet you will need to make the code "ThisWorkbook" Events not sheet events and make it work with the Active sheet!

 

[littleme]

Re: please help with year update and inserting worksheet nam

I could almost kiss you!! Worked perfectly until I put it in the ThisWorkbook... then it didnt work at all... on the other the other code that I have there doesnt work either.... But it did work when i just left it at the worksheet... When i made a copy of the sheet, it worked as well so... Will it not work in the long run like that or?

When you say Active Sheet you mean ActivateSheet or? Im not too good friends with VBA so if you have the patience, feel free to treat me like a 5-year old.... =)

Would also be grateful if you could elaborate on what you said on the year-update problem....

 

[Joe Was]

Re: please help with year update and inserting worksheet nam

The code as posted goes into a Standard Sheet Module, like: Sheet1.

To modify the code to work when the sheet is copied then you need to pick an event that is available from the "ThisWorkbook" module. And, paste just the code I posted, not the event, into your new event. The list of available events is in a dropdown at the top of the VB editor.

Note: You can pick the event or events that cover your application best if the code fails for your event we may need to add a generic Sheet Selector like: ActiveSheet to the offending code?

 

[littleme]

Re: please help with year update and inserting worksheet nam

perfect! just needed a meal and some sleep to understand what you had written =) thank you...

Could really use some help on the year update problem though... please=S

nina

 

[Joe Was]

Re: please help with year update and inserting worksheet nam

I do not now your Code, Sheets, formats or data so:

This sheet function will return the current dates year:

=YEAR(NOW())

in this case when you add this to your sheet it gives: 2005

And sheet event code that flags a column of data by coloring the cell to the left of that dates cell, where dates are with in ten days of today works like this:

Private Sub Worksheet_Activate()
'Sheet Module code!

For Each C In Worksheets("Sheet1").Range("G7:G22")

If (C.Value >= Now() - 11 Or C.Value <= Now()) Then
C.Offset(0, -1).Interior.ColorIndex = 3
End If

If (Not C.Value >= Now() - 11 Or Not C.Value <= Now()) Then
C.Offset(0, -1).Interior.ColorIndex = xlNone
End If
Next C
End Sub

You could modify this code to add the year to a label, like:

myMonth = vMonth & ", " & myYear

To adjust this for a span of years your months will need to be flagged somehow as to which year that month belongs, like: FirstYearMonth, SecondYearMonth and ThirdYearMonth. This way the code can assign the proper year to the proper month. Or, your months will "all" need to be accounted, serially for each 12 month group, so a simple loop can update them automatically.