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

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

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

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

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