Setting a Worksheets Codename to a variable programmatically
 

Ok Guys I have a little bit of a problem. I have this line of code:

ActiveWorkbook.VBProject.VBComponents(wsNameold).N ame = "Worksheet1"

and this suprisingly enough changes the workbooks codename to
Worksheet1 based on a variable I define earlier in the code. Now I
want this to set the codename to the value in a cell on the sheet
namely "A1" . I've tried replacing the "Worksheet1" with
Range("A1").value and .text also I've set the value of that range to a
Variable (wsName) which was defined as a string and through that in
there all of which return the error:

"Run-Time error '50132': Application-defined or object-defined error"

Just wondering if I'm trying to do the impossible here or I'm missing
something obvious. Here is the full code in the sub:


Private Sub btnAdd_Click()
Dim wsName As String
Dim wks As Worksheet
Dim wsNameold As String

Let wsNameold = Me.CodeName
Let wsName = Range("a1").Text
Let Me.Name = wsName

ActiveWorkbook.VBProject.VBComponents(wsNameold).N ame =
Range("A1").Value

End Sub

it all works except for the last line. Thanks for your help in advance.

-Turk

Setting a Worksheets Codename to a variable programmatically
 

try

Sheets("Sheet1").Name = Sheets("Sheet1").Cells(1, 1)



"Turk" skrev:

Ok Guys I have a little bit of a problem. I have this line of code:

ActiveWorkbook.VBProject.VBComponents(wsNameold).N ame = "Worksheet1"

and this suprisingly enough changes the workbooks codename to
Worksheet1 based on a variable I define earlier in the code. Now I
want this to set the codename to the value in a cell on the sheet
namely "A1" . I've tried replacing the "Worksheet1" with
Range("A1").value and .text also I've set the value of that range to a
Variable (wsName) which was defined as a string and through that in
there all of which return the error:

"Run-Time error '50132': Application-defined or object-defined error"

Just wondering if I'm trying to do the impossible here or I'm missing
something obvious. Here is the full code in the sub:


Private Sub btnAdd_Click()
Dim wsName As String
Dim wks As Worksheet
Dim wsNameold As String

Let wsNameold = Me.CodeName
Let wsName = Range("a1").Text
Let Me.Name = wsName

ActiveWorkbook.VBProject.VBComponents(wsNameold).N ame =
Range("A1").Value

End Sub

it all works except for the last line. Thanks for your help in advance.

-Turk

Setting a Worksheets Codename to a variable programmatically
 

Are you sure A1 contains a valid name for a sheet object (codename & module
name), which is even more restricted than worksheet names.

Regards,
Peter T



"Turk" wrote in message
oups.com...
Ok Guys I have a little bit of a problem. I have this line of code:

ActiveWorkbook.VBProject.VBComponents(wsNameold).N ame = "Worksheet1"

and this suprisingly enough changes the workbooks codename to
Worksheet1 based on a variable I define earlier in the code. Now I
want this to set the codename to the value in a cell on the sheet
namely "A1" . I've tried replacing the "Worksheet1" with
Range("A1").value and .text also I've set the value of that range to a
Variable (wsName) which was defined as a string and through that in
there all of which return the error:

"Run-Time error '50132': Application-defined or object-defined error"

Just wondering if I'm trying to do the impossible here or I'm missing
something obvious. Here is the full code in the sub:


Private Sub btnAdd_Click()
Dim wsName As String
Dim wks As Worksheet
Dim wsNameold As String

Let wsNameold = Me.CodeName
Let wsName = Range("a1").Text
Let Me.Name = wsName

ActiveWorkbook.VBProject.VBComponents(wsNameold).N ame =
Range("A1").Value

End Sub

it all works except for the last line. Thanks for your help in advance.

-Turk

Setting a Worksheets Codename to a variable programmatically
 

HA man o man that was the easiest fix ever. Thanks I should have
changed it around. It had a space in it which is fine for the name but
not CodeName. Thank you very much!

p.s. I feel kinda stupid now.

Peter T wrote:
Are you sure A1 contains a valid name for a sheet object (codename & module
name), which is even more restricted than worksheet names.

Regards,
Peter T



"Turk" wrote in message
oups.com...
Ok Guys I have a little bit of a problem. I have this line of code:

ActiveWorkbook.VBProject.VBComponents(wsNameold).N ame = "Worksheet1"

and this suprisingly enough changes the workbooks codename to
Worksheet1 based on a variable I define earlier in the code. Now I
want this to set the codename to the value in a cell on the sheet
namely "A1" . I've tried replacing the "Worksheet1" with
Range("A1").value and .text also I've set the value of that range to a
Variable (wsName) which was defined as a string and through that in
there all of which return the error:

"Run-Time error '50132': Application-defined or object-defined error"

Just wondering if I'm trying to do the impossible here or I'm missing
something obvious. Here is the full code in the sub:


Private Sub btnAdd_Click()
Dim wsName As String
Dim wks As Worksheet
Dim wsNameold As String

Let wsNameold = Me.CodeName
Let wsName = Range("a1").Text
Let Me.Name = wsName

ActiveWorkbook.VBProject.VBComponents(wsNameold).N ame =
Range("A1").Value

End Sub

it all works except for the last line. Thanks for your help in advance.

-Turk