I have a user form that is populated using an array. The array is based on
one of two columns. If column B is blank, then column A is used, if column B
contains anything at all, then column B is used to populate the ComboBox on a
user form.

It works fine, except that if a change is made in column B, the change isn't
reflected the next time the user form is loaded. How can I enforce immediate
changes such as this?

I assume that you are only hiding the form and not unloading it. A form has
two different events which can be used. Initialize which fires when the form
is first created and activate which is fired whenever we make the form
active. Use the activate event to check the status of column B and load the
combo box appropriately. This should recitfy your problem. Otherwise you
could use the on change event of the sheet and catch changes in Column B.
From here you could access the combo box on the form and reload it. The
problem here is that it will fire fairly regularly and cause the combo box to
reload or check if it needs to be reloaded when it is not necessary.

HTH


"quartz" wrote:

I have a user form that is populated using an array. The array is based on
one of two columns. If column B is blank, then column A is used, if column B
contains anything at all, then column B is used to populate the ComboBox on a
user form.

It works fine, except that if a change is made in column B, the change isn't
reflected the next time the user form is loaded. How can I enforce immediate
changes such as this?

Jim, thanks for your reply.

The user doubleclicks in column "A" of "Time Sheet" to call Userform2.Show.
When the user clicks "OK" or "Cancel" it calls UserForm2.Hide.

This doesn't work. But, my code in the "Time Sheet" class module will not
function with "UserForm2.Activate". How can I get this to work?


"quartz" wrote:

I have a user form that is populated using an array. The array is based on
one of two columns. If column B is blank, then column A is used, if column B
contains anything at all, then column B is used to populate the ComboBox on a
user form.

It works fine, except that if a change is made in column B, the change isn't
reflected the next time the user form is loaded. How can I enforce immediate
changes such as this?

But you have a UserForm2 form. In the VBE there are a number of events
associated with the form. One of them should be active. Place the code into
this event.

This code goes right in the form...

Private Sub UserForm_Activate()
MsgBox "Load the combo Box here..."

End Sub

"quartz" wrote:

Jim, thanks for your reply.

The user doubleclicks in column "A" of "Time Sheet" to call Userform2.Show.
When the user clicks "OK" or "Cancel" it calls UserForm2.Hide.

This doesn't work. But, my code in the "Time Sheet" class module will not
function with "UserForm2.Activate". How can I get this to work?


"quartz" wrote:

I have a user form that is populated using an array. The array is based on
one of two columns. If column B is blank, then column A is used, if column B
contains anything at all, then column B is used to populate the ComboBox on a
user form.

It works fine, except that if a change is made in column B, the change isn't
reflected the next time the user form is loaded. How can I enforce immediate
changes such as this?

Thanks Jim! I think I was mis-reading your OP. I needed to "UnLoad" the form
each time rather than "Hide" it. That seems to have taken care of the issue.

But, I am still not using the "UserForm_Activate" sub. Do you think I still
need to do that to prevent other issues?

"Jim Thomlinson" wrote:

But you have a UserForm2 form. In the VBE there are a number of events
associated with the form. One of them should be active. Place the code into
this event.

This code goes right in the form...

Private Sub UserForm_Activate()
MsgBox "Load the combo Box here..."

End Sub

"quartz" wrote:

Jim, thanks for your reply.

The user doubleclicks in column "A" of "Time Sheet" to call Userform2.Show.
When the user clicks "OK" or "Cancel" it calls UserForm2.Hide.

This doesn't work. But, my code in the "Time Sheet" class module will not
function with "UserForm2.Activate". How can I get this to work?


"quartz" wrote:

I have a user form that is populated using an array. The array is based on
one of two columns. If column B is blank, then column A is used, if column B
contains anything at all, then column B is used to populate the ComboBox on a
user form.

It works fine, except that if a change is made in column B, the change isn't
reflected the next time the user form is loaded. How can I enforce immediate
changes such as this?

Not if you are unloading the form.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
Thanks Jim! I think I was mis-reading your OP. I needed to "UnLoad" the
form
each time rather than "Hide" it. That seems to have taken care of the
issue.

But, I am still not using the "UserForm_Activate" sub. Do you think I
still
need to do that to prevent other issues?

"Jim Thomlinson" wrote:

But you have a UserForm2 form. In the VBE there are a number of events
associated with the form. One of them should be active. Place the code
into
this event.

This code goes right in the form...

Private Sub UserForm_Activate()
MsgBox "Load the combo Box here..."

End Sub

"quartz" wrote:

Jim, thanks for your reply.

The user doubleclicks in column "A" of "Time Sheet" to call
Userform2.Show.
When the user clicks "OK" or "Cancel" it calls UserForm2.Hide.

This doesn't work. But, my code in the "Time Sheet" class module will
not
function with "UserForm2.Activate". How can I get this to work?


"quartz" wrote:

I have a user form that is populated using an array. The array is
based on
one of two columns. If column B is blank, then column A is used, if
column B
contains anything at all, then column B is used to populate the
ComboBox on a
user form.

It works fine, except that if a change is made in column B, the
change isn't
reflected the next time the user form is loaded. How can I enforce
immediate
changes such as this?