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?