Cause userform to update?

[Guest]

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?

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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?

 

[Tom Ogilvy]

Not if you are unloading the form.