• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to change from tab "Names" to tab numbers?

Eloise T

Eloise T

Active Member
I have the following line of code that allows me to exclude tabs (worksheets) in my workbook as I have need.

If ws.Name <> "Formula Info" And ws.Name <> "Next Tech" Then

How do I change the code to allow me to simply use numbers?

Would it be...?
If ws.Name <> "Sheet1" And ws.Name <> "Sheet5" Then ?
or
If ws.Name <> "!Sheet1" And ws.Name <> "!Sheet5" Then ?
I would experiment but the sheets are so large, it could take hours to spot a change.

Thank you.
 
Use numbers? I assume you mean to use sheet index.

So if you want to exclude Sheet1 & Sheet5 ... i.e. 1st and 5th sheet.
Code: 
    If ws.Index <> 1 Or ws.Index <> 5 Then
      'do something
    End If
 
Hi ,

I would advise against this.

Just as sheet tab names can be changed , so also the position of the sheets themselves can be changed , and even more easily , by a drag of the mouse.

Thus , what was Worksheet #1 in the tabs order , can suddenly be changed to #17 , and no one will be the wiser.

The best would be to use the codenames , since those are the most difficult to change for a normal user of Excel ; changing worksheet tab names is easier , though it involves a conscious effort.

Changing worksheet indices is easiest and so effortless that the change may go unnoticed with probably damaging consequences.

Narayan
 
Chihiro said:
Use numbers? I assume you mean to use sheet index.

So if you want to exclude Sheet1 & Sheet5 ... i.e. 1st and 5th sheet.
Code: 
    If ws.Index <> 1 Or ws.Index <> 5 Then
      'do something
    End If
Click to expand...
Doesn't that need to be And vs. Or?
If ws.Index <> 1 Or ws.Index <> 5 Then
 
NARAYANK991 said:
Hi ,

I would advise against this.

Just as sheet tab names can be changed , so also the position of the sheets themselves can be changed , and even more easily , by a drag of the mouse.

Thus , what was Worksheet #1 in the tabs order , can suddenly be changed to #17 , and no one will be the wiser.

The best would be to use the codenames , since those are the most difficult to change for a normal user of Excel ; changing worksheet tab names is easier , though it involves a conscious effort.

Changing worksheet indices is easiest and so effortless that the change may go unnoticed with probably damaging consequences.

Narayan
Click to expand...
Your warning is well appreciated and noted. I plan to continue using "Names" vs. numbers except on a TEST file where accidents are "OK".
 
NARAYANK991 said:
Hi ,

I would advise against this.

Just as sheet tab names can be changed , so also the position of the sheets themselves can be changed , and even more easily , by a drag of the mouse.

Thus , what was Worksheet #1 in the tabs order , can suddenly be changed to #17 , and no one will be the wiser.

The best would be to use the codenames , since those are the most difficult to change for a normal user of Excel ; changing worksheet tab names is easier , though it involves a conscious effort.

Changing worksheet indices is easiest and so effortless that the change may go unnoticed with probably damaging consequences.

Narayan
Click to expand...
There's no problem with mixing them either, correct?...names and numbers in the same line of code?
 
Last edited:
In plain English...

x <> y AND x <> z will equate to
Neither x <> y or x <> z

x <> y OR x <> z will equate to
Either x <> y or x <> z

In this instance, you want the former.

Also, if you want to use sheet codename as Narayank suggested, you can do something like...
Code: 
    If ws.Name <> Sheet1.Name And ws.Name <> Sheet5.Name Then
        'do something
    End If
In plain English.
 
NARAYANK991 said:
Hi ,

I would advise against this.

Just as sheet tab names can be changed , so also the position of the sheets themselves can be changed , and even more easily , by a drag of the mouse.

Thus , what was Worksheet #1 in the tabs order , can suddenly be changed to #17 , and no one will be the wiser.

The best would be to use the codenames , since those are the most difficult to change for a normal user of Excel ; changing worksheet tab names is easier , though it involves a conscious effort.

Changing worksheet indices is easiest and so effortless that the change may go unnoticed with probably damaging consequences.

Narayan
Click to expand...
Not if you protect workbook for "structure".
 
In Excel menu, go to "Review" tab and there you will find an option "Protect Workbook".

On clicking this, a form will open for password input with couple of check-boxes. If you press "OK" even without typing password then workbook will be protected for structure which will disable many controls on the worksheet.
- people cannot rename the sheet names
- hide / unhide cannot be done
- sheets cannot be moved around

It could be an option if there are multiple users and you don't want them to mess up your template / file.
 
shrivallabha said:
In Excel menu, go to "Review" tab and there you will find an option "Protect Workbook".

On clicking this, a form will open for password input with couple of check-boxes. If you press "OK" even without typing password then workbook will be protected for structure which will disable many controls on the worksheet.
- people cannot rename the sheet names
- hide / unhide cannot be done
- sheets cannot be moved around

It could be an option if there are multiple users and you don't want them to mess up your template / file.
Click to expand...
THANK YOU! Good to know.
 
shrivallabha said:
In Excel menu, go to "Review" tab and there you will find an option "Protect Workbook".

On clicking this, a form will open for password input with couple of check-boxes. If you press "OK" even without typing password then workbook will be protected for structure which will disable many controls on the worksheet.
- people cannot rename the sheet names
- hide / unhide cannot be done
- sheets cannot be moved around

It could be an option if there are multiple users and you don't want them to mess up your template / file.
Click to expand...
I tried it. How do you UNprotect? I didn't put a password in...just clicked ok without a password. I went back figuring the opposite would be true but it stayed "protected." Good thing it was a blank workbook.
 
Eloise T said:
I tried it. How do you UNprotect? I didn't put a password in...just clicked ok without a password. I went back figuring the opposite would be true but it stayed "protected." Good thing it was a blank workbook.
Click to expand...
Hi ,

I clicked on the menu option labelled Protect Workbook ; this has two checkboxes labelled Structure and Windows ; I ensure the Structure checkbox is checked and click OK.

Now , this menu option is highlighted , and remains highlighted.

When I click on it again , if I have not used a password , it immediately unprotects the workbook , and the highlight is no longer present.

Are you finding anything different ?

Narayan
 
NARAYANK991 said:
Hi ,

I clicked on the menu option labelled Protect Workbook ; this has two checkboxes labelled Structure and Windows ; I ensure the Structure checkbox is checked and click OK.

Now , this menu option is highlighted , and remains highlighted.

When I click on it again , if I have not used a password , it immediately unprotects the workbook , and the highlight is no longer present.

Are you finding anything different ?

Narayan
Click to expand...
Yes and no.
I thought I'd have to do more than just click on Protect Workbook to undo it.
I tried to "go back in" and click OK again thinking it would undo it since there was no "UNDO" button.
They made it too simple. :confused:
 
You must log in or register to reply here.
Back
Top