| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Const & RowSource
Hi,
I am trying to set these as public constants so I don't have them retyped all over the place. I use the same form and reset the RowSource depending on what I'm doing. So, I wanted to set them in one place and use them as a variable. This is what I tried Public Const CAMExtLIRowSource As String = "Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _ "(""CAMLineItemsExterior"").Address(external:=True )" then I use it with .rowsource = CAMExtLIRowSource I get an error "Could not set rowsource. Invalid Property Value" Is it possible to do this? -- Thanks for your help. Karen53 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Public Const & RowSource
What you are attempting to do will not work. If I understand what you want
you are trying to set you rowsource to an address (like Sheet1!A1). What you are actually doing is setting it to the string Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _ "(""CAMLineItemsExterior"").Address(external:=True )" What you want to do is to change your const to a regular variable and then set up a procedure to populate that variable when the book is opened. Note that the address function only returns the address and not the sheet. You need to concatenate the sheet name to the address when you populate the variable. -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I am trying to set these as public constants so I don't have them retyped all over the place. I use the same form and reset the RowSource depending on what I'm doing. So, I wanted to set them in one place and use them as a variable. This is what I tried Public Const CAMExtLIRowSource As String = "Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _ "(""CAMLineItemsExterior"").Address(external:=True )" then I use it with .rowsource = CAMExtLIRowSource I get an error "Could not set rowsource. Invalid Property Value" Is it possible to do this? -- Thanks for your help. Karen53 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Public Const & RowSource
Hi Jim,
Thank you for your prompt reply. I am unsure how the sheet name is to be concatenated to the address. Isn't the sheet name already there with the (Replace(Tablespg.Name, "", " ' ' "))? Since I'm using Sheet code names, I was told this translates it into the sheet name. Would this be all I would need? Worksheets(Replace(Tablespg.Name, "", " ' ' ")).Range _ ("CAMLineItemsExterior").Address(external:=True ) -- Thanks for your help. Karen53 "Jim Thomlinson" wrote: What you are attempting to do will not work. If I understand what you want you are trying to set you rowsource to an address (like Sheet1!A1). What you are actually doing is setting it to the string Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _ "(""CAMLineItemsExterior"").Address(external:=True )" What you want to do is to change your const to a regular variable and then set up a procedure to populate that variable when the book is opened. Note that the address function only returns the address and not the sheet. You need to concatenate the sheet name to the address when you populate the variable. -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I am trying to set these as public constants so I don't have them retyped all over the place. I use the same form and reset the RowSource depending on what I'm doing. So, I wanted to set them in one place and use them as a variable. This is what I tried Public Const CAMExtLIRowSource As String = "Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _ "(""CAMLineItemsExterior"").Address(external:=True )" then I use it with .rowsource = CAMExtLIRowSource I get an error "Could not set rowsource. Invalid Property Value" Is it possible to do this? -- Thanks for your help. Karen53 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Public Const & RowSource
Sorry... You are correct. You have external set to true so it will return the
entire address. I guess I just did not read closely enough. -- HTH... Jim Thomlinson "Karen53" wrote: Hi Jim, Thank you for your prompt reply. I am unsure how the sheet name is to be concatenated to the address. Isn't the sheet name already there with the (Replace(Tablespg.Name, "", " ' ' "))? Since I'm using Sheet code names, I was told this translates it into the sheet name. Would this be all I would need? Worksheets(Replace(Tablespg.Name, "", " ' ' ")).Range _ ("CAMLineItemsExterior").Address(external:=True ) -- Thanks for your help. Karen53 "Jim Thomlinson" wrote: What you are attempting to do will not work. If I understand what you want you are trying to set you rowsource to an address (like Sheet1!A1). What you are actually doing is setting it to the string Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _ "(""CAMLineItemsExterior"").Address(external:=True )" What you want to do is to change your const to a regular variable and then set up a procedure to populate that variable when the book is opened. Note that the address function only returns the address and not the sheet. You need to concatenate the sheet name to the address when you populate the variable. -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I am trying to set these as public constants so I don't have them retyped all over the place. I use the same form and reset the RowSource depending on what I'm doing. So, I wanted to set them in one place and use them as a variable. This is what I tried Public Const CAMExtLIRowSource As String = "Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _ "(""CAMLineItemsExterior"").Address(external:=True )" then I use it with .rowsource = CAMExtLIRowSource I get an error "Could not set rowsource. Invalid Property Value" Is it possible to do this? -- Thanks for your help. Karen53 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Public Const & RowSource
Thanks, Jim! I'm off and running, well, at least skipping.
-- Thanks for your help. Karen53 "Jim Thomlinson" wrote: Sorry... You are correct. You have external set to true so it will return the entire address. I guess I just did not read closely enough. -- HTH... Jim Thomlinson "Karen53" wrote: Hi Jim, Thank you for your prompt reply. I am unsure how the sheet name is to be concatenated to the address. Isn't the sheet name already there with the (Replace(Tablespg.Name, "", " ' ' "))? Since I'm using Sheet code names, I was told this translates it into the sheet name. Would this be all I would need? Worksheets(Replace(Tablespg.Name, "", " ' ' ")).Range _ ("CAMLineItemsExterior").Address(external:=True ) -- Thanks for your help. Karen53 "Jim Thomlinson" wrote: What you are attempting to do will not work. If I understand what you want you are trying to set you rowsource to an address (like Sheet1!A1). What you are actually doing is setting it to the string Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _ "(""CAMLineItemsExterior"").Address(external:=True )" What you want to do is to change your const to a regular variable and then set up a procedure to populate that variable when the book is opened. Note that the address function only returns the address and not the sheet. You need to concatenate the sheet name to the address when you populate the variable. -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I am trying to set these as public constants so I don't have them retyped all over the place. I use the same form and reset the RowSource depending on what I'm doing. So, I wanted to set them in one place and use them as a variable. This is what I tried Public Const CAMExtLIRowSource As String = "Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _ "(""CAMLineItemsExterior"").Address(external:=True )" then I use it with .rowsource = CAMExtLIRowSource I get an error "Could not set rowsource. Invalid Property Value" Is it possible to do this? -- Thanks for your help. Karen53 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Const Array | Excel Programming | |||
| Public Const to refer to a range name | Excel Programming | |||
| public const availability? | Excel Programming | |||
| Public Const on a Drive | Excel Programming | |||
| crash changing const to public const | Excel Programming | |||
Linear Mode
