Refreshing ROWSOURCE

[Guest]

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?

 

[Tom Ogilvy]

reassign the rowsource.

 

[Guest]

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]

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.

 

[Guest]

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]

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)

 

[Guest]

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]

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