Listbox not refreshing using RowSource

[d_banduk]

I have used this site to learn about writing macros from scratch and have found it fantastic but the for the first time I'm having a problem for which I can find no answer which probably means its me being dumb!

I have a userform with a listbox that can be run from any of several dozen worksheets. It should load its contents from a range on the active sheet using the RowSource property. It works the first time but if I then run the userform from another sheet it still contains the data from the first sheet and doesn't update with the new data from the new sheet.

The form is run from a macro assigned to a button on the sheet using UserForm2.Show and closed again using UserForm2.Hide assigned to the cancel button.

The source range is AN4:AN8

Any help would be greatly appreciated and bring an end to alot of head scratching.

 

[HalfAce]

Hello d_banduk,
I don't know if there is a reason your Uform is only being hidden instead of unloaded, but that is what's causing your issue. The ListBox has already been loaded with the active sheet's data so when you hide it & call it on a different sheet, it doesn't need to be re-loaded because it never got unloaded.
First thing to try is the line you use to dismiss the UForm.
Try replacing UserForm2.Hide with Unload UserForm2.

This may not be suitable as there may be other data that you actually want to retain in the UForm. If that's the case then we can go into making it reload the listbox source when you need it.

Hope it helps.

 

[Norie]

How exactly are you setting RowSource?

Are you specifying the correct sheet name when trying to refer to the range you want?

If you don't then VBA is going to use the range on whatever it considers the active worksheet.

 

[d_banduk]

I am using the box in the properties window on the left of the code. I've just put in the range as above without a sheet reference as I want the listbox to load from the active sheet (would this always be the sheet in view?)

 

[d_banduk]

HalfAce,

Sorry, overlooked your post initially. Thank you very much works a dream.
I am extremely grateful and that's another tool in the chest.

Cheers

 

[HalfAce]

Most welcome, glad it helped.

I want the listbox to load from the active sheet (would this always be the sheet in view?)

Well, yes... most of the time. You can 'activate' another sheet without actually seeing it (mostly this is just done with code in the middle of a macro) but for your intent & purpose this time, the answer would be yes, that row source would always bring up the range of the sheet you're looking at.

that's another tool in the chest.

If by this you don't mean you've been stabbed with a screwdriver (again...) then to that end here's another idea for you.

You could leave it the way you had it (hiding the UForm instead of unloading it) and use the UserForm_Activate event to re-load the ListBox RowSource every time you show the hidden UForm.
If you put this in the UserForm code module, it would have also solved your problem.

Private Sub UserForm_Activate()
Me.ListBox1.RowSource = "A1:A10" '(Amend to your real row source.)
End Sub

This will make it re-load the row source every time the userform gets shown, whether or not the user has changed sheets. It will always reflect the values in the sheet they're looking at.

That help at all?