Public Const & RowSource

[Karen53]

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?

 

[Jim Thomlinson]

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.

 

[Karen53]

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)

 

[Jim Thomlinson]

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.

 

[Karen53]

Thanks, Jim! I'm off and running, well, at least skipping.