worksheet codename direct use vs. use in a object variable

[Werner Rohrmoser]

Hello,

I like to use the codenames of the worksheets in my code, because they
do not change
even if the user decides to rename a workshet name.

When I use the codename directly in my code all methods and properties
of a worksheet object
are available.
When I use a object variable like:
"Set VBDataSheet =
ThisWorkbook.VBProject.VBComponents(Grenzwerte.Range("AktivesDatenblatt").Value)",
which is a vb component I can't use this object like a worksheet
object.

How can I create a worksheet object based on the codename of a
worksheet (name of vbcomponent)?

Thanks.
Werner

 

[Bob Phillips]

Dim XLSheetname As String
Dim VBDataSheet As Worksheet

XLSheetname =
ThisWorkbook.VBProject.VBComponents("Sheet3").Properties("Name")

With ThisWorkbook
Set VBDataSheet = .Worksheets(XLSheetname)
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peter T]

I don't quite follow what you are trying to do, but maybe the following
assists:

Dim ws as worksheet
Set ws = Sheet1
- to reference a sheet in thisworkbook, even if not active, whose codename
is "Sheet1". Best not to do this if there is any possibility of the sheet
getting deleted.

If you do not want to hardcode the Codename, probably a good idea, then you
need to loop sheets, eg

Sub Test()
Dim ws As Worksheet
Dim sCodeName As String
' rename the sheet name (ie tab name) before testing this
sCodeName = "Sheet1"
For Each ws In ThisWorkbook.Worksheets
If ws.CodeName = sCodeName Then
Exit For
End If
Next
If ws Is Nothing Then
MsgBox "codename: " & sCodeName & " not found"
Else
MsgBox ws.CodeName & vbCr & ws.Name
End If
End Sub

Regards,
Peter T

 

[Werner Rohrmoser]

Bob,

GREAT, that's exactly what I need.
In the online help "Properties" is explained very poor.
Do you have a list of arguments available?

Regards
Werner

 

[Jim Cone]

Bob,
Last two days getting a "Server Not Found" trying to access your
website at www.xldynamic.com
Are you still there?
Regards,
Jim Cone



"Bob Phillips"
<[email protected]>
wrote in message
Dim XLSheetname As String
Dim VBDataSheet As Worksheet

XLSheetname =
ThisWorkbook.VBProject.VBComponents("Sheet3").Properties("Name")

With ThisWorkbook
Set VBDataSheet = .Worksheets(XLSheetname)
End With

 

[Dave Peterson]

First, in my simple test, I still had the properties and methods showing up with
code like:

Sheet2.cells(5,1).value = "hi"

But this worked, too:

Dim wks As Worksheet
Set wks = Sheet2
MsgBox wks.Range("a1").Address(external:=True)

====
Sometimes those properties/methods seem to disapper (I don't know the reason!).

But they'll often come back--even if I have to close and reopen excel.

Maybe it'll work for you????

 

[Werner Rohrmoser]

Hi Dave,

yes when I use the name of VBComponent directly like
"Sheet1.cells(1,1).value = 15" then it works fine.

Also when you assign the vbcomponent directly to an object variable
like
"Set wks = Sheet1" and use it like "wks.cells(1,1).value = 15" it
works well.

But if you have the name of the vbcomponent stored in a cell on a
sheet of your spreadsheet
and you'd like to create a worksheet object from the vbcomponent name
like
"Set wks = ThisWorkbook.VBProject.VBComponents("VBComponentName")" you
get a vbcomponent.

I'm not able to create a worksheet object form the vbcomponent name or
codename (strings).

Werner

 

[Peter T]

Curiosity, what's the reason not to do it along the lines I suggested, which
would also avoid any issues with the Trust Access VB projects security
setting.

Regards,
Peter T

 

[Dave Peterson]

I'd use the same kind of code that Peter T used--for the same reasons, too.

 

[Werner Rohrmoser]

Peter,

I'm always looking for a short and direct solution, see Bob's
contribution.
(I couldn't believe that there is no direct way)
Disadvantage is that I could get an issues with the Trust Access VB
projects security setting.

Your solution works fine as well and avoids any trouble with the
security setting,
so I have the choice.

Best Regards
Werner