Easy Syntax question: referring to worksheet by VBA name
 

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

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Easy Syntax question: referring to worksheet by VBA name
 

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

--
Regards,
Tom Ogilvy

"KR" wrote in message
...
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

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Easy Syntax question: referring to worksheet by VBA name
 

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 procedu

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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"KR" wrote in message
...
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

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are
my
own.