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.

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.

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.