Populating a list in a userform

[GovNewbi]

When my userforms activate I have two listboxes in each that are being populated. The problem I am having is that a user may activate the form more than once and the listbox populates each time giving me duplicates. Where can I put the .AddItem code so that my listboxs are only being populated once?

 

[SkipVought]

Hi,

You could either DELETE/Add each time the UF is activated or test the Count property of the control.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

... or populate the list with 'Initialize' event (UserForm_Initialize).

combo

 

[Andrzejek]

I found that the safest way to populate a list box (or combo box) is to [tt].Clear[/tt] it before populating.

Have fun.

---- Andy

 

[fumei]

I totally agree with Andy. The best way is to have a .Clear before the .AddItem(s). That way if ever the list needs refreshing (or the userform is initialized/activated again) only the list you want gets there.

Dim Yadda() As String
Dim j as Long
Yadda() = Split("whatever,this,that,hohum",",")
ComboBox1.Clear
For j = 0 To Ubound(Yadda())
     ComboBox1.Additem Yadda(j)
Next
 ' if you want the first one visible)
ComboBox1.ListIndex = 0

Gerry

 

[GovNewbi]

Thanks guys it worked great... I have one more question kind of related to this.

When the list box opens the second time the items that were selected the first time are still selected. Is there a way to have it not select any when it opens or to just highlight the top one or something like that.

 

[combo]

Set the Listindex property to -1.

Gerry, do you know the way to initialise object twice?

combo

 

[fumei]

Gerry, do you know the way to initialise object twice?

Define your tems. Initialize what object? The userform? Why would you do that? I am not clear on what you are asking.

BTW: not always, but generally, I separate out populating procedures and call them. That way if I require a repopulation, I do not need to go through Userform_Initialize(). So instead of:

Sub Userform1_Initialize()
Dim j as Long
   Yadda() = Split("whatever,this,that,hohum",",")
   ListBox1.Clear
   ListBox1.List = Yadda
End Sub

I would use:

Sub Pop_List1()
Dim j As Long
Dim Yadda() As String
   Yadda() = Split("whatever,this,that,hohum", ",")
   ListBox1.Clear
   ListBox1.List = Yadda
End Sub

Private Sub UserForm_Initialize()
Call Pop_List1
End Sub
[/vba]

Gerry

 

[combo]

The userform object does not exist (in vbe one can see only the visual class designer) until you create it explicitly or implicitly:

Sub ImplicitUserForm1()
UserForm1.Show
End Sub

Sub ExplicitUserForm1()
Dim uf As UserForm1
Set uf = New UserForm1
uf.Show
End Sub

The 'Initialize' event is rised once when executing one of the above procedures, so as long as there is no need to refresh the list, a list filled in one of above calls will start until destroying the form.

combo

 

[fumei]

That is true, so what is your question again?

Plus, I suspect (but do not know) the OP has the population insrtuctions in Userform_Activate....not Initialize.

I have seen this before. I have no idea why people do that, but it appears that they think Activate - Initialize.

Which of course they are not.

If you Hide the userform, then show it again, Activate fires again. Initialize does not.

All this is again a good reason to put the actual instructions for population into a separate procedure and Call it as required. AND - as stated - use a .Clear.

I am not sure of your point re: the two procedures. In both cases Initialize fires. Activate as well.

The key is: " user may activate the form more than once"

How, precisely, are they doing that.

When the FIRST TIME a userform is called:

Initialize fires
Activate fires

If it is hidden (.Hide), and then brought back using .Show

Activate fires. Initialize does not.

Thus if the populating instructions are in Activate (and again I have seen this done...why I do not know) then...the populating instructions are repeated.

If the populating instructions are indeed in Activate then either add a .Clear...or move the populating instructions to Initialize.



Gerry