Sorting Worksheets in Excel VBE to match order in GUI

Rather than change the code names of your worksheets so they match the order of the tab names, change the code names of the worksheets to match the tab names so you know which sheet is which just by looking at the list. For example, assuming I have three sheets with tab names:

   Main
   Table
   Lists

I will make my sheets' code names:

   wksMain
   wksTable
   wksLists

They won't be in the same order as the tab names (unless you sort the tabs alphabetically), but you will most certainly know which sheet is which just by looking at the list.

Consider this: assuming the sheets have code names like Sheet1, Sheet2, Sheet3, etc. such that they are in the same order, how do you know that the tab name Customer is Sheet 6 or Sheet7? You would have to know that Customer is the 6th or 7th tab. Not much of a help from my perspective.

Kevin

Rob:  Thanks for that - I am looking at Chip's site now.


Kevin:  Maybe your VBE looks different, but I see the worksheet.name in the VBE already.  It shows after the worksheet.codename in brackets like this:

Sheet1 (Portfolio)
Sheet10 (Additions)
Sheet2 (Inputs)

If I change the worksheet.codename as you suggest won't it just look like this:

wksPortfolio (Portfolio)
wksAdditions (Additions)
wksInputs (Inputs)


Am I missing something there?

Thanks,

Alan.

Hi Kevin,

That's perfect!

Just for reference and anyone that follows, I actually used your code without the second loop as I (personally) prefer the indexing like this.

 

Public Sub RenameSheetCodeNames()

Dim Index As Long


    For Index = 1 To ThisWorkbook.Sheets.Count
        
        ThisWorkbook.VBProject.VBComponents.Item(ThisWorkbook.Sheets(Index).CodeName).Name = "Sheet" & 1000 + Index
    
    Next Index
    

End Sub

Select allOpen in new window

Thanks,

Alan.

Gave 100 to Rob for great reference as I learnt something there too, and Kevin's code was a spot-on solution.

Thanks to you both,

Alan.

Nice one Kevin,

Short & sweet :-)

Personally, I can get nervous about people copying code snippets willy-nilly with very little understanding, so I would change "Dim Index as long" to something else such as "Dim wsIndex as long" because "Index" is already a defined property for numerous things (a technical term ;-)) in VBA.

Rob

That's cool. But the code won't work a second time around because you will get name conflicts. So you really want:

    For Index = 1 To ThisWorkbook.Sheets.Count
        ThisWorkbook.VBProject.VBComponents.Item(ThisWorkbook.Sheets(Index).CodeName).Name = "Sheet" & 10000000 + Index
    Next Index
    For Index = 1 To ThisWorkbook.Sheets.Count
        ThisWorkbook.VBProject.VBComponents.Item(ThisWorkbook.Sheets(Index).CodeName).Name = "Sheet" & 1000 + Index
    Next Index

Kevin

Thanks for the points Alan :-)

Chip's site is a gold mine of information so I'd recommend visiting other pages (eg his Page Index) when you feel like some light reading!

Rob

Rob,

Your point is certainly valid. But if I worried about it to that degree I would be less productive. Consider that the term Index is not a VBA or VB reserved word, it's only a property in some collection-based classes. And I would rather use Index versus i which a lot of my C coding friends use. I'm pretty bad through. I routinely use variable names like Worksheet, Workbook, Cell, and so on. Search for my code and you will often see stuff like:

Dim Worksheet As Worksheet
For Each Worksheet In ThisWorkbook.Worksheets
...

This works because Worksheet is a class (type) and not a pre-instantiated object. But it's still kinda loosey-goosey.

Haven't gotten into trouble yet ;-)

Kevin

Thanks Kevin - I'll use that code instead.

Just a personal (ascetic) preference though :-)

Alan.

Of course. But it's important to understand why there are two loops. You can't just rename a sequence of objects without first moving all the objects out of the way so there are no name collisions. It doesn't matter what the end result is, but it does matter of the code breaks because it tries to name an object to a new name that is already being used.

Kevin

Yep - Excellent general point.

Thanks again,

Alan.

Kevin,

"Your point is certainly valid. But if I worried about it to that degree I would be less productive."

LOL!
I wish that were true for me too, but at the moment I'm spending about an hour a day teaching workmates better coding practices (via the Immediate & Locals Pane) so they don't get themselves in such trouble when they go crazy on a copy & paste/drag & drop coding spree!
Don't get me wrong, I like teaching but... I do feel that if I succeed in getting some general points across, I could spend more time at my own desk (being productive ;-)) instead of looking over my colleagues' shoulders as they try to debug borrowed code without properly understanding it :(

*chuckle*
I am guilty as charged for using "i" & *sigh* I have to admit it's for pretty much the same reason - I'm guilty of "borrowing code" & I guess some of my historical borrowings were written by C coders too!

You may not have got into trouble yet (& I don't think I have either?) but I'll keep pushing my colleagues because I have enough challenge verbalising the issues I spot on the VBE even when there are reasonably clearly (?) named variables like "xlApp" or "AccApp". Let alone if the variable names were exact matches...

Rob