Listbox in Userform "loses" the rowsource at second opening

[Littlecube]

Hello,

I am really new with programming and this is my first job with VBA, I hope somebody can help me, because I cannot find solution for some days.. :/


In my excel are more sheets and each has 1 table objects
- after opening the workbook, the macro set of its visible to false and show UserForm1

In UserForm1 are more commandbutton to open other userforms
- every userforms has got 1 listbox, what is selected in the listbox will be a content in one of the Textbox in UserForm1. The contents in UserForm1 will be a part of a record.
- I set the RowSources of listboxes with the name of tables in the property window

In UserForm1 is a Save commandbutton to make a record from textboxes contents and make safety copy of the whole file.

The problem:

- After the UserForm1 show up, I click one of the commandbutton to show a sub UserForm to select a line.
- I can chose what I want and it will placed in Userform1 properly.

BUT, when I open again the sub UserForm, then the Listbox is empty. This problem is happaning by every sub Userform.

Surprise: After I click on the "Save" command button, all the sub Userform working perfectly, the Listboxes always have their Rowsource any time I open them.


I cannot find, what is the difference between the first opening and after the save process "opening" (there is no code in save process, to close or open Userform1).


Thank you in advance for your helping porpuse!

 

[NdNoviceHlp]

Hi Littlecube and Welcome to the Board! It's difficult to be a helping porpuse when U haven't posted any of the relevant code. It depends on whether U unload or hide Userform1 and what and where your code is to load your listbox. Maybe a bit more info is needed. HTH. Dave

 

[Littlecube]

Hi Dave,

Thank you for your respone!
I use this code to open the Userform1 and I write it in a Module. It is called from workbook_Open()

Sub open_wb()


    On Error Resume Next
    Application.ScreenUpdating = False


If Windows(thiswb.Name).Visible = True Then
    Windows(thiswb.Name).Visible = False
End If


    Uf_1.Show (vbModeless)


End Sub

In the UserForm1, I use this for all sub Userform:

Private Sub Cb_spl_Click()


SparePartList.Show vbModal


End Sub

To close a sub Userform I use an other cmb on that whit this code:

Private Sub CommandButton1_Click()


Unload Me


End Sub

 

[NdNoviceHlp]

So U just load your listbox with rowsource as entered in the listbox properties box? U could try Unload Userform1 instead of Unload Me. I'm still not clear on what's happening. Dave

 

[Littlecube]

Thank you Dave!

This was the key: "It depends on whether U unload or hide Userform1.."
I used unload, but with hide command it works like I want to.
Oh my god.. 3 days from my life

Actually, I still not understand, why it has worked with unload command after I used the save macro in it, but now I am satisfied with the result. I think it will be a long journey with VBA :D

 

[NdNoviceHlp]

Unload Me unloads all memory for Userform1. Unload Userform1 doesn't unload the rowsource memory. Hide Userform1 retains all values and has no effect on memory and the initialize and activation codes do not fire. I could be wrong with this summary and will appreciate all additional info from others. Anyways, I'm glad U got it sorted out. In the future, for better assistance, I would suggest having someone U know read over your question to make sure they can clearly understand it. Usually, the most important part of a VBA fix is where your data is, what U want to do with it and where do U want the results to end up. So ensuring that U clearly state theses items in terms of forms, controls, sheet, column and row then a fix isn't usually that far away. Anyways, thank you for posting your outcome. Dave