Macro Help 2

[GeoCan]

I use a sheet that must be updated every two weeks. The macro copies the sheet, clears the data ranges, calculates the next pay period dates and renames the sheet to Time Sheet (2).
The macro works great for my needs though it probably is not as efficient as it could be but I'm only now trying to learn code.

I have it generate a Text Box on original sheet to remind users to rename the sheet tab to the date range of the period.

It works great UNLESS I add any other text box during the two week period, which happens very frequently.

So my question is two-fold:

1. Is there a way to read cells A6 & A20 and rename the sheet accordingly; i.e. A6 = 29 Mar and A20 = 9 APR, sheet name would be 29 Mar - 9 Apr.

2. If not, how would I assign the box (or it could be a button even) a name so that I do not get errors if other text boxes/buttons have been added?

This is the portion of code that frustrates me on those occasions. {The "delbox" macro selects Text Box 15 and deletes it.}

Set myDocument = Worksheets(2)
myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, _
100, 100, 200, 50) _
.TextFrame.Characters.Text = "Don't Forget to Rename Time Sheet (2) to past pay period dates! Click Here to delete this box and goto your new Time Sheet when you are finished."
Sheets("Time Sheet (2)").Select
ActiveSheet.Shapes("Text Box 15").Select
Selection.OnAction = "delbox"


Many thanks in Advance!

 

[Gavona]

Something like:

worksheets(2).name=range("a6").value & " - " & range("A20").value

(you might need to use the Text function if the cells are not text but dates)

will the sheet always be Worksheet(2) ?

Thanks,

Gavin

 

[GeoCan]

Thank You.

Yes, it will always be sheet 2.

I'm trying to find help on the Text function now since your guess was correct, the cells are date format rather than text. I tried it and it will work great once I convert the dates to text.

 

[byundt]

You don't need to change your worksheet if you use the Format statement in VBA:

Worksheets(2).Name=Format([A6],"d mmm") & " - " & Format([A20],"d mmm")

Brad

 

[GeoCan]

Excellent, Thank you Gavin & Brad!!

Now I'll try to learn a bit more so I can to "clean up" a couple of other tasks I know I'm doing "the long way.