Easy Syntax question: referring to worksheet by VBA name

[KR]

I've renamed my worksheets in the VBE properties window ("Apple", "Pear",
"Melon"); in the Excel user view, they still show up as "sheet1", sheet2",
and "sheet3".

Now in my VBA I need to refer to a range on a selected sheet. If I hardcode
it, I can use:
Apple.Range("B4:F17").Select

but I'm pulling in the appropriate sheet name as a text string, "Apple" or
"Pear" or "Melon".

How do I use this dynamically? I'm sure it is something close to:

Dim oWrkSht as Worksheet

Set oWrkSht = "Apple"

oWrkSht.Range("B4:F17").Select

but I get a type mismatch, because the sheet can't be set to a string...

I'm sure there is an easy way to do this...

Thanks,
Keith

 

[Tom Ogilvy]

Dim oWrkSht as Worksheet

set oWrkSht = apple

or use it directly.

apple.Range("A1").Value


do demo, from the immediate window:

set wksht = apple
? wksht.Name
Sheet1
? wksht.CodeName
apple

 

[KR]

Tom-
Thank you for your reply; please let me clarify- I may not be clearly
expressing (or even understanding) what I need;

I'm using Randy Birch's code to get the current user's Login ID. In my case,
that is "Keith1" which is acquired via the call to another procedure:

sUsername = Trim(GetThreadUserName())
where the procedure returns a string via; Private Function
GetThreadUserName() As String

I've renamed my worksheet codename to Keith1 to match my username (and the
other sheets to the usernames of my officemates). Now I need to select the
right sheet, based on the username of whoever is logged in, e.g. the string
LoginID.

My guess is that rather than your suggested syntax

set oWrkSht = apple

what I have is the equivalent of

set oWrkSht = "Apple"

because I'm using
set oWrkSht = sUserName

which throws a compile error (type mismatch).

I'm sure there is a better approach, preferably one that doesn't involve
looping through all the sheets to check each one to see if the codename
matches the string.

I appreciate any addition insight you and others might provide...
Thanks,
Keith

 

[Tom Ogilvy]

You can't use the code name as a variable. I gave you what you can do. Now
you must loop.

I'm sure there is a better approach,

The approach you are pursuing will not work.