Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting Data Validation Programmatically | Excel Programming | |||
Setting Solver Reference-programmatically | Excel Programming | |||
Setting HotKey Programmatically | Excel Programming | |||
WhatsThisHelp in VBA - setting the help file path programmatically | Excel Programming | |||
Setting Printer Options Programmatically | Excel Programming |