Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to select specified worksheets by codename

  Asked By: Darcy    Date: Mar 11    Category: MS Office    Views: 1681
  

One way to select specified worksheets is: Sheets(Array
("volvo", "dodge", "honda")).Select; however, I wish to use the
sheet's codename, so that if the user changes the tab label
(e.g. "volvo" becomes "Car-Volvo") the VBA will still work. Can
you select multiple sheets by 'codename' that is equivalent to
the 'Sheets (Array)' method that uses the tab label names?

Share: 

 

14 Answers Found

 
Answer #1    Answered By: Madeeha Malik     Answered On: Mar 11

Not sure what you're trying to do .. I'm not aware that sheets  have a
"codename" ... but....

.. You seem to be trying to second guess a user  that can alter a workbook.
Trust me... It's not possible.

Given that...

Assuming that the number of worksheets  isn't going to alter, you can
reference a sheet number in the sheets collection.

Instead of using a literal... "volvo", "dodge", "honda" ... You can use a
number. I'm unsure if this number changes if you just add sheets but I know
it changes if you delete sheets above that number. Say you had a sheet
"volvo" and you referenced it as sheet number 4. If you deleted a sheet
below 4 then this would change it's number. I'm not sure what to. I
personally don't see a *totally* foolproof way around this. Maybe more
knowledgable people will jump in.

I think I'd go for a cell in an unused space with a white font colour so
that the text in it couldn't be seen and loop through the sheets looking at
that cell and testing for its value with a select  case statement. This would
effectively create a "code" for that sheet to test for ... *But* .. the user
could still dump over that cell and then all bets are off.

 
Answer #2    Answered By: Aaminah Khan     Answered On: Mar 11

Actually, the reason to use the "Sheets Code Name" is to get around
the user  altering the workbook. See:

www.ozgrid.com/VBA/excel-vba-sheet-names.htm

 
Answer #3    Answered By: Anne Powell     Answered On: Mar 11

Why not just use the name property of the sheet's codename? For
example, the following sets of code give the same result:

n1 = 0
For Each oSheet In Sheets(Array("A", "C"))
n1 = n1 + oSheet.[A1]
Next oSheet
Debug.Print "n1 = " & n1 & vbCrLf

n2 = 0
For Each oSheet In Sheets(Array(Sheet1.Name, Sheet3.Name))
n2 = n2 + oSheet.[A1]
Next oSheet
Debug.Print "n2 = " & n2 & vbCrLf

 
Answer #4    Answered By: Tate Thompson     Answered On: Mar 11


The web site you referenced nicely explains the difference between
using a worksheets  codename over the label  that a user  assignes to the
tab.

Rather than referencing Sheets("Honda").Select, I would reference
Sheet3.Select This is good...

The problem I'm having is selecting worksheets that are not in any
order (sheet3, sheet7 and sheet8). The command:

Sheets(Array("volvo", "dodge", "honda")).Select

works really well... is there a way I can substitute sheet3 for volvo?

In the meantime, I'll try to work  through the solutions you have
provided - I'm a beginner in VBA.

 
Answer #5    Answered By: Charlie Evans     Answered On: Mar 11

As I said, try using the name property of the codename, as in:

Sheets(Array(Sheet3.Name, Sheet7.Name, Sheet8.Name)).Select

...where Sheet3.Name is "volvo". But if they change the name of the
worksheet, the above formula would pick up the new name.

 
Answer #6    Answered By: Jawna Mohammad     Answered On: Mar 11

FWIW I think that if the user  can change the name of the sheet then the
possibility of them inserting a sheet just where you don't want them to
should be catered for.

I'd still go for a solution that uses some feature / cell that is unique to
each sheet and isn't likely to be altered.

 
Answer #7    Answered By: Venkat Rulez     Answered On: Mar 11

re: "the possibility of them inserting a sheet just where you don't
want them to should be catered for"

despite that at first codenames are
similar to the sheet's tab  names, a sheet keeps its codename unless
programmatically changed (with the likes of:
Workbooks("test2.xls").VBProject.VBComponents("Sheet1").Name =
"AnotherName"
) or changed manually in the VBE using the sheet's properties window.
It'll keep its codename through other sheet insertions and other sheet
deletions. Not to be confused also with a sheet's index number
(Sheets(1), Sheets(3) etc. which happily changes!)

 
Answer #8    Answered By: Minal Nayak     Answered On: Mar 11

I tried this:

Opened a new wkbk
dragged sheet 3 tab  before sheet 2
Went into the VBA Editor

The codenames and names  are now

Sheet1 (Sheet1)
Sheet2 (Sheet3)
Sheet3 (Sheet2)

It looks to me as though the codenames have changed.

 
Answer #9    Answered By: Haru Tanaka     Answered On: Mar 11

When I do the same thing, I get this in the Project
Explorer window. I am running Excel 2002 SP3.

Sheet1 (Sheet1)
Sheet2 (Sheet2)
Sheet3 (Sheet3)
ThisWorkBook

 
Answer #10    Answered By: Jacob Evans     Answered On: Mar 11

Are your sheets  in the order:

Sheet1
Sheet3
Sheet2

This was the effect of the drag operation I did. If they are not you have not
changed anything, have you?

 
Answer #11    Answered By: Chaths Massri     Answered On: Mar 11

I opened a saved version of the
book and got your results. I'm certain I didn't misread it
yesterday, but now I have this.

Sheet1 (Sheet1)
Sheet2 (Sheet3)
Sheet3 (Sheet2)
ThisWorkBook

 
Answer #12    Answered By: Tarron Thompson     Answered On: Mar 11

I just tried it again. I moved sheet1 to the last position.
In order to get the Project Explorer window to change, I had
to close the workbook and open it again. Now I have this listed.

Sheet1 (Sheet3)
Sheet2 (Sheet2)
Sheet3 (Sheet1)
ThisWorkbook

So, either the Project Explorer does not show the secret
code name of sheets  or it does and the advice given in
www.ozgrid.com/VBA/excel-vba-sheet-names.htm is wrong.

 
Answer #13    Answered By: Vid Fischer     Answered On: Mar 11

Yes indeed, I got the same after saving and reopening the workbook!
So it seems I was wrong.. but I tried this: I added a standard code
module, put a tiny sub into into it then tried rearranging the sheets
as before - now the codenames stuck to their respective sheets. I
don't know whether this would still work  with a bit of code in one of
the sheet modules

 
Answer #14    Answered By: Daniel Costa     Answered On: Mar 11

A solution might be to give your sheets  a unique name in the VBA editor, ie
wsVolvo etc.
Then the name displayed for the user  or the altered name don't really matter
any more and the command for selecting a sheet would simply become:
wsVolvo.Select

 
Didn't find what you were looking for? Find more on How to select specified worksheets by codename Or get search suggestion and latest updates.




Tagged: