For one of my user-forms, I have a combo-box. The rowsource property
contains a formula using OFFSET function that refers to existing areas in the
existing workbook.

When the form is displayed, the ROWSOURCE works correctly, and the drop-down
is correctly created. Neverthless, if the cells (that are part of the
ROWSOURCE formula) change, the valid values for the combo-box are not
refreshed.

Is there a way to easily refresh the contents programatically?

reassign the rowsource.

--
Regards,
Tom Ogilvy


"DKS" wrote in message
...
For one of my user-forms, I have a combo-box. The rowsource property
contains a formula using OFFSET function that refers to existing areas in
the
existing workbook.

When the form is displayed, the ROWSOURCE works correctly, and the
drop-down
is correctly created. Neverthless, if the cells (that are part of the
ROWSOURCE formula) change, the valid values for the combo-box are not
refreshed.

Is there a way to easily refresh the contents programatically?

By reassigning the rowsource do you mean that within the VBA module I
introduce statement to set the value of the combobox? If that is the case
then I had already tried doing it via the VBA module, but given the fact that
I use OFFSET function of .xls and since that is not available as a function
via VBA, I was blocked. That was the reason I asked the question here.

"Tom Ogilvy" wrote:

reassign the rowsource.

--
Regards,
Tom Ogilvy


"DKS" wrote in message
...
For one of my user-forms, I have a combo-box. The rowsource property
contains a formula using OFFSET function that refers to existing areas in
the
existing workbook.

When the form is displayed, the ROWSOURCE works correctly, and the
drop-down
is correctly created. Neverthless, if the cells (that are part of the
ROWSOURCE formula) change, the valid values for the combo-box are not
refreshed.

Is there a way to easily refresh the contents programatically?

Given that you can't use the Offset function directly to assign to the
rowsource, the assumption is that you created a defined name with
Insert=name=Define and then assigned that defined name to the rowsource.

rowsource MyList in the property window as an example where the
defined name is list.

then in code

Activesheet.Listbox1.RowSource = "List1:

in code would reset it and pick up your new values.

--
Regards,
Tom Ogilvy

"DKS" wrote in message
...
By reassigning the rowsource do you mean that within the VBA module I
introduce statement to set the value of the combobox? If that is the case
then I had already tried doing it via the VBA module, but given the fact
that
I use OFFSET function of .xls and since that is not available as a
function
via VBA, I was blocked. That was the reason I asked the question here.

"Tom Ogilvy" wrote:

reassign the rowsource.

--
Regards,
Tom Ogilvy


"DKS" wrote in message
...
For one of my user-forms, I have a combo-box. The rowsource property
contains a formula using OFFSET function that refers to existing areas
in
the
existing workbook.

When the form is displayed, the ROWSOURCE works correctly, and the
drop-down
is correctly created. Neverthless, if the cells (that are part of the
ROWSOURCE formula) change, the valid values for the combo-box are not
refreshed.

Is there a way to easily refresh the contents programatically?

In the property window for the combobox, .xls allows me to use OFFSET
function. That is how I got the correct values.

But within a VBA code I cannot use OFFSET function (or at least I did not
find a way to use it).

My OFFSET function refers to one other cell that is updated by the form.
Thus, what happens is that the form updates a cell. And when that cell is
updated, I expect the OFFSET function to deliver different results and thus
different "values" behind my combo-box. However, the ROWSOURCE value is
assigned when the form is displayed, and it never gets re-evaluated (or
refreshed).




"Tom Ogilvy" wrote:

Given that you can't use the Offset function directly to assign to the
rowsource, the assumption is that you created a defined name with
Insert=name=Define and then assigned that defined name to the rowsource.

rowsource MyList in the property window as an example where the
defined name is list.

then in code

Activesheet.Listbox1.RowSource = "List1:

in code would reset it and pick up your new values.

--
Regards,
Tom Ogilvy

"DKS" wrote in message
...
By reassigning the rowsource do you mean that within the VBA module I
introduce statement to set the value of the combobox? If that is the case
then I had already tried doing it via the VBA module, but given the fact
that
I use OFFSET function of .xls and since that is not available as a
function
via VBA, I was blocked. That was the reason I asked the question here.

"Tom Ogilvy" wrote:

reassign the rowsource.

--
Regards,
Tom Ogilvy


"DKS" wrote in message
...
For one of my user-forms, I have a combo-box. The rowsource property
contains a formula using OFFSET function that refers to existing areas
in
the
existing workbook.

When the form is displayed, the ROWSOURCE works correctly, and the
drop-down
is correctly created. Neverthless, if the cells (that are part of the
ROWSOURCE formula) change, the valid values for the combo-box are not
refreshed.

Is there a way to easily refresh the contents programatically?

Well, that is a new one on me.

anyway, are you using a formula like this:

offset(Sheet1!$A$1,0,0,countA(sheet1!$A:$A),1)

--
Regards,
Tom Ogilvy

"DKS" wrote in message
...
In the property window for the combobox, .xls allows me to use OFFSET
function. That is how I got the correct values.

But within a VBA code I cannot use OFFSET function (or at least I did not
find a way to use it).

My OFFSET function refers to one other cell that is updated by the form.
Thus, what happens is that the form updates a cell. And when that cell is
updated, I expect the OFFSET function to deliver different results and
thus
different "values" behind my combo-box. However, the ROWSOURCE value is
assigned when the form is displayed, and it never gets re-evaluated (or
refreshed).




"Tom Ogilvy" wrote:

Given that you can't use the Offset function directly to assign to the
rowsource, the assumption is that you created a defined name with
Insert=name=Define and then assigned that defined name to the
rowsource.

rowsource MyList in the property window as an example where
the
defined name is list.

then in code

Activesheet.Listbox1.RowSource = "List1:

in code would reset it and pick up your new values.

--
Regards,
Tom Ogilvy

"DKS" wrote in message
...
By reassigning the rowsource do you mean that within the VBA module I
introduce statement to set the value of the combobox? If that is the
case
then I had already tried doing it via the VBA module, but given the
fact
that
I use OFFSET function of .xls and since that is not available as a
function
via VBA, I was blocked. That was the reason I asked the question here.

"Tom Ogilvy" wrote:

reassign the rowsource.

--
Regards,
Tom Ogilvy


"DKS" wrote in message
...
For one of my user-forms, I have a combo-box. The rowsource
property
contains a formula using OFFSET function that refers to existing
areas
in
the
existing workbook.

When the form is displayed, the ROWSOURCE works correctly, and the
drop-down
is correctly created. Neverthless, if the cells (that are part of
the
ROWSOURCE formula) change, the valid values for the combo-box are
not
refreshed.

Is there a way to easily refresh the contents programatically?

Your understanding of the situation is almost correct. With the added
complexity that the cell Sheet1!$A$1 (as referred to by in your formula) is
in the CONTROLSOURCE property of another field on the form, let us say
REGION.

Thus my idea was that depending on what the user fills on the form REGION on
screen, it is reflected in the spreadsheet SHEET1. And based on this change
in the spreadsheet, I expected the ROWSOURCE property to get changed
dynamically. Which of course is not happening because ROWSOURCE is set when
the form is loaded.

For info: the exact formula that I am using for ROWSOURCE property is as
follows:
PLEASE NOTE that in my formula I am referring to $F$1 and not $A$1 for the
variable part of the rowsource.

OFFSET(Sheet1!$A$1,MATCH(Sheet1!$F$1,Sheet1!$A:$A, 0)-1,3,COUNTIF(Sheet1!$A:$A,Sheet1!$F$1),1)

where cell F1 is in the CONTROLSOURCE property of Region.

Hope all the above starts to make sense to you.


"Tom Ogilvy" wrote:

Well, that is a new one on me.

anyway, are you using a formula like this:

offset(Sheet1!$A$1,0,0,countA(sheet1!$A:$A),1)

--
Regards,
Tom Ogilvy

"DKS" wrote in message
...
In the property window for the combobox, .xls allows me to use OFFSET
function. That is how I got the correct values.

But within a VBA code I cannot use OFFSET function (or at least I did not
find a way to use it).

My OFFSET function refers to one other cell that is updated by the form.
Thus, what happens is that the form updates a cell. And when that cell is
updated, I expect the OFFSET function to deliver different results and
thus
different "values" behind my combo-box. However, the ROWSOURCE value is
assigned when the form is displayed, and it never gets re-evaluated (or
refreshed).




"Tom Ogilvy" wrote:

Given that you can't use the Offset function directly to assign to the
rowsource, the assumption is that you created a defined name with
Insert=name=Define and then assigned that defined name to the
rowsource.

rowsource MyList in the property window as an example where
the
defined name is list.

then in code

Activesheet.Listbox1.RowSource = "List1:

in code would reset it and pick up your new values.

--
Regards,
Tom Ogilvy

"DKS" wrote in message
...
By reassigning the rowsource do you mean that within the VBA module I
introduce statement to set the value of the combobox? If that is the
case
then I had already tried doing it via the VBA module, but given the
fact
that
I use OFFSET function of .xls and since that is not available as a
function
via VBA, I was blocked. That was the reason I asked the question here.

"Tom Ogilvy" wrote:

reassign the rowsource.

--
Regards,
Tom Ogilvy


"DKS" wrote in message
...
For one of my user-forms, I have a combo-box. The rowsource
property
contains a formula using OFFSET function that refers to existing
areas
in
the
existing workbook.

When the form is displayed, the ROWSOURCE works correctly, and the
drop-down
is correctly created. Neverthless, if the cells (that are part of
the
ROWSOURCE formula) change, the valid values for the combo-box are
not
refreshed.

Is there a way to easily refresh the contents programatically?

I think you are going to just have to reassign the rowsource to get it to
update.

--
Regards,
Tom Ogilvy


"DKS" wrote in message
...
Your understanding of the situation is almost correct. With the added
complexity that the cell Sheet1!$A$1 (as referred to by in your formula)
is
in the CONTROLSOURCE property of another field on the form, let us say
REGION.

Thus my idea was that depending on what the user fills on the form REGION
on
screen, it is reflected in the spreadsheet SHEET1. And based on this
change
in the spreadsheet, I expected the ROWSOURCE property to get changed
dynamically. Which of course is not happening because ROWSOURCE is set
when
the form is loaded.

For info: the exact formula that I am using for ROWSOURCE property is as
follows:
PLEASE NOTE that in my formula I am referring to $F$1 and not $A$1 for the
variable part of the rowsource.

OFFSET(Sheet1!$A$1,MATCH(Sheet1!$F$1,Sheet1!$A:$A, 0)-1,3,COUNTIF(Sheet1!$A:$A,Sheet1!$F$1),1)

where cell F1 is in the CONTROLSOURCE property of Region.

Hope all the above starts to make sense to you.


"Tom Ogilvy" wrote:

Well, that is a new one on me.

anyway, are you using a formula like this:

offset(Sheet1!$A$1,0,0,countA(sheet1!$A:$A),1)

--
Regards,
Tom Ogilvy

"DKS" wrote in message
...
In the property window for the combobox, .xls allows me to use OFFSET
function. That is how I got the correct values.

But within a VBA code I cannot use OFFSET function (or at least I did
not
find a way to use it).

My OFFSET function refers to one other cell that is updated by the
form.
Thus, what happens is that the form updates a cell. And when that cell
is
updated, I expect the OFFSET function to deliver different results and
thus
different "values" behind my combo-box. However, the ROWSOURCE value
is
assigned when the form is displayed, and it never gets re-evaluated (or
refreshed).




"Tom Ogilvy" wrote:

Given that you can't use the Offset function directly to assign to the
rowsource, the assumption is that you created a defined name with
Insert=name=Define and then assigned that defined name to the
rowsource.

rowsource MyList in the property window as an example
where
the
defined name is list.

then in code

Activesheet.Listbox1.RowSource = "List1:

in code would reset it and pick up your new values.

--
Regards,
Tom Ogilvy

"DKS" wrote in message
...
By reassigning the rowsource do you mean that within the VBA module
I
introduce statement to set the value of the combobox? If that is
the
case
then I had already tried doing it via the VBA module, but given the
fact
that
I use OFFSET function of .xls and since that is not available as a
function
via VBA, I was blocked. That was the reason I asked the question
here.

"Tom Ogilvy" wrote:

reassign the rowsource.

--
Regards,
Tom Ogilvy


"DKS" wrote in message
...
For one of my user-forms, I have a combo-box. The rowsource
property
contains a formula using OFFSET function that refers to existing
areas
in
the
existing workbook.

When the form is displayed, the ROWSOURCE works correctly, and
the
drop-down
is correctly created. Neverthless, if the cells (that are part
of
the
ROWSOURCE formula) change, the valid values for the combo-box are
not
refreshed.

Is there a way to easily refresh the contents programatically?