Dim XLSheetname As String
Dim VBDataSheet As Worksheet

XLSheetname =
ThisWorkbook.VBProject.VBComponents("Sheet3").Prop erties("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)



"Werner Rohrmoser" wrote in message
...
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.Ran ge("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