• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Sorting Worksheets in Excel VBE to match order in GUI

Hi,

I would like to be able to automagically re-sort the worksheets in a given workbook as they appear in the VBE so that they match the order in the GUI.

It seems to me that to do this would require changing the 'codename' of the sheets into ascending numerical order?

Two questions:

1) Can that be done in VBA (as it seems like VBA would need to be modifying 'itself', but not really as long as the code is in a standard module?) and if so, how?

2) Is something 'bad' going to happen if this is run? For example, would inter-sheet forumulae go all wonky due to them using the underlying worksheet.codename rather than the worksheet.name?


NB:  I realise that this could cause problems if there were links to the workbook from other sources, but that is no different than changing a worksheet.name in a source workbook if the destination workbook is closed (it won't know, and the links go wrong).  I can live with that caveat.


Thanks,

Alan.
0
Alan
Asked:
Alan
  • 5
  • 5
  • 4
2 Solutions
 
broro183Commented:
hi Alan,

Rather than copying & pasting I'll provide links to some of Chip Pearson's pages which should answer your first question:
http://www.cpearson.com/excel/codemods.htm
see the "Changing The CodeName Of A Worksheet" at the base of http://www.cpearson.com/excel/RenameProblems.aspx
http://www.eggheadcafe.com/software/aspnet/35477857/efficient-use-of-sheet-codenames.aspx

Regarding your second question:
I don't think worksheet formulae will be affected at all but I haven't tested this. In fact, I think the only thing that should be affected is your VBA code. If you refer to worksheet objects in your VBA code using the codename syntax,
'for example
sheet1.range("a1")
'instead of the alternative (possibly more common?) syntax of
worksheets("sheet1").range("a1")

Open in new window


hth
Rob
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
AlanConsultantAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
zorvek (Kevin Jones)ConsultantCommented:
Actually, yes, you are correct! I forgot about that!

Use this macro to do what you want:

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
    For Index = 1 To ThisWorkbook.Sheets.Count
        ThisWorkbook.VBProject.VBComponents.Item(ThisWorkbook.Sheets(Index).CodeName).Name = "Sheet" & Index
    Next Index

End Sub

Kevin
0
 
AlanConsultantAuthor Commented:
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

Open in new window


Thanks,

Alan.
0
 
AlanConsultantAuthor Commented:
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.
0
 
broro183Commented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
broro183Commented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
AlanConsultantAuthor Commented:
Thanks Kevin - I'll use that code instead.

Just a personal (ascetic) preference though :-)

Alan.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
AlanConsultantAuthor Commented:
Yep - Excellent general point.

Thanks again,

Alan.
0
 
broro183Commented:
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now