2

I am working on a project that requires me to select worksheets depending on the value of a position in an array. I have populated desArr() (of type String) with values and desArr(0) contains the name of the sheet I want to reference. In order to avoid the problem with users changing the tab names and messing up the code, I am trying to reference the CodeName of the worksheet.

Usually, I would be able to do this:

ThisWorkbook.Worksheets(Import.Name)

where Import is specified under the (Name) property in VB Editor. However, I would now like to reference that name variably, as in input depending on the value of desArr(0). I tried doing the following but to no avail:

ThisWorkbook.Worksheets(desArr(0).Name)

ThisWorkbook.Worksheets(desArr(0) & ".Name")

Does anyone have any idea on how to solve this problem?

Improve this question
3
  • ThisWorkbook.Worksheets(desArr(0))? Commented Jun 6, 2014 at 15:40
  • Thanks for your response! But that would just reference the tab name of the worksheet. So if one of my tabs in the workbook was "export" and desArr(0) contains "export" then it would work. What I wanted to achieve was to call the Name property of the worksheet which may or may not be the same as the tab name. Any ideas? Commented Jun 6, 2014 at 15:43
  • 1
    Maybe it would be easier to collect the worksheet objects instead of their names? If you had a worksheet object in the array instead of the name, you could call desArr(0).Name Note that if you have set your worksheet appropriately, there is no need for the ThisWorkbook.WorkSheets part. Commented Jun 6, 2014 at 16:30

1 Answer 1

Reset to default
2

Just use:

Import

That's the way to reference a sheet object without needing to know its Name property. Just use its variable/object name. The above is equivalent to, but much less convoluted than, 

ThisWorkbook.Worksheets(Import.Name)

What you refer as the "CodeName" and the (Name) "property" isn't a property. It's just the name of the object variable. From what I can infer from your question, your sheet object is called Import so just use that.

EDIT:

It's hard to understand what you are asking; to clarify a bit:

If I understand correctly, you have a sheet called "Import", and you want to be able to guard against this scenario:

enter image description here

Ok, some user decided it was a good idea to rename the sheet. Note that this changed the sheet object's Name property's value to "User-input crap". The Name property and the thing written in the tab are always the same; they're the same thing; they're linked one-to-one.

However, the sheet object itself is still called Import, or whatever it was earlier. (By default it is Sheet1, but maybe you changed that in the Properties window.) See:

enter image description here Regardless of user-input crap, I can still say, for instance:

Import.Cells(1, 1).Value = "frog"

I can also say this, which is uselessly complicated but perfectly equivalent:

ThisWorkbook.Worksheets(Import.Name).Cells(1, 1).Value = "frog"

I could also say this:

ThisWorkbook.Worksheets("User-input crap").Cells(1, 1).Value = "frog"

but for that I'd have to know what the user typed in the sheet tab, which I don't.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thanks for the response! But I would like to reference the Name property because my code will fail if someone changes the worksheet tab names. With the Name property the user can't change it readily without first changing it via the VB editor. Also, I know the Name property for each worksheet, but I just don't know the syntax when the name is not typed in but is stored in an array desArr(0). Any thoughts?
The above should answer your question unambiguously.
Just to clarify, that would be what is posted in the tab name and can be changed right? So in this example, Import would be the name of the tab, but not necessarily the Name property of the sheet?
Import is just an example of a sheet name I could use, but ultimately that sheet name should be what is stored in desArr(0), but that's not the same as Sheets("Import"). Import in this case would be what appears on the tab name, and what I think you're suggesting, but if the user were to change Import to say Export, then Sheets("Import") would return an error.
That will never work. Forget about your array. You know your sheet is Import, and it will never change, so why store that in an array? Just use Import as a litteral.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.