VBA Code to change sheet names 1

[Restitus]

I have created a work schedule that has to change worksheets names according to the shift working, basically a 7 day shift showing the respective shift, in proper day order; Days, Evenings, & Split Shift.

This is the code that I have put together, (see below), my problem is that I can't get the code to work properly from a buttom if that buttoms are clicked out of order. How do I work the "If...Then...Else" into my code so as not to create an error if the buttoms are clicked out of order.

Sub DayShift()
Range("E6").Select
ActiveCell.FormulaR1C1 = "Days"
Sheets("TueS").Select
Sheets("TueS").Name = "FriD"
Sheets("WedS").Name = "SatD"
Sheets("ThuS").Name = "SunD"
Sheets("FriS").Name = "MonD"
Sheets("SatS").Name = "TueD"
Sheets("SunS").Name = "WedD"
Sheets("MonS").Name = "ThuD"
Sheets("FriD").Select
Range("E6").Select
End Sub
Sub EveningShift()
Range("E6").Select
ActiveCell.FormulaR1C1 = "Evenings"
Sheets("FriD").Select
Sheets("FriD").Name = "ThuE"
Sheets("SatD").Name = "FriE"
Sheets("SunD").Name = "SatE"
Sheets("MonD").Name = "SunE"
Sheets("TueD").Name = "MonE"
Sheets("WedD").Name = "TueE"
Sheets("ThuD").Name = "WedE"
Sheets("ThuE").Select
Range("E6").Select
End Sub
Sub SplitShift()
Range("E6").Select
ActiveCell.FormulaR1C1 = "Split Shift"
Sheets("ThuE").Select
Sheets("ThuE").Name = "TueS"
Sheets("FriE").Name = "WedS"
Sheets("SatE").Name = "ThuS"
Sheets("SunE").Name = "FriS"
Sheets("MonE").Name = "SatS"
Sheets("TueE").Name = "SunS"
Sheets("WedE").Name = "MonS"
Sheets("TueS").Select
Range("E6").Select
End Sub

 

[TonyJollans]

Hi Restitus,

I don't understand what you are doing - or how it works at all - which routine needs to go first for it to work? Can you give a bit more detail about what you want - renaming sheets may not be the best way to go about it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SkipVought]

Hi,

Each worksheet has a Name and a CodeName. The Name is what you see on the sheet tab. The CodeName can only be changed via Code or the VB Editor.

So change your code to reference the CodeName.Name property.

Let's assume that your code names are
[tt]
Day1
Day2
...
Day7
[/tt]
then your code is

   Day1.Name = "TueD"
...

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[Restitus]

Tony
What I am trying to do is change the worksheet tab names according to the shift that is selected and show the proper start day (i.e., Fri, Thu, Tue) without generating a error.

What I have put together works if ran in the proper order, but if I run it out of order I get a Run-Time 9 (out of script) error.

How do I change the worksheet names (not its CodeName) to a particular order without generating a error.

 

[SkipVought]

I gave you the way to change the Tab Name.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}