ListBox items disappear upon Show statement

[Keb]

Hello -

I'm using Excel 2000 VBA to programmatically create a userform
containing a listbox. I can watch the userform as it is created and
filled via the debugger, but as soon as the show line executes, the
listbox entries disappear - other items, such as checkbox labels &
buttons appear fine.

Can someone tell me how to solve this problem, and more importantly,
why it is occuring?

I have one other question also: A width property can be set for an
item such as a command button however that property is neither seen in
the complete_word dropdown list nor in the object browser for the item
- it can be seen in the Properties Window, however. Why ??

Thanks in advance, (Please post any replies, as I cannot access the
email acct easily).

Keb

Here is a simplified version of my code:

Sub ShowListbox()
Dim TempForm
Dim NewListBox As MSForms.ListBox
Dim NewToggleButton As MSForms.CheckBox
Dim i As Integer

Application.VBE.MainWindow.Visible = False
'
' Create the userform
'
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
TempForm.Properties("Width") = 100
'
' Add listbox & populate it.
'
Set NewListBox = TempForm.Designer.Controls.Add("forms.ListBox.1",
, True)

For i = 1 To 10
NewListBox.AddItem "Item " & LTrim(Str(i))
Next
'
' Show the form.
'
VBA.UserForms.Add(TempForm.Name).Show
'
' Delete the form & clean up.
'
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

Set TempForm = Nothing
Set NewListBox = Nothing
End Sub

 

[Keb]

oops - I just realized this is the WORD VBA group, I meant to put this
in the EXCEL VBA forum (It's posted there now). Guess I should have
looked at the location more closely after I searched for
"VBA.beginners" (The Excel group is called "excel.programming", so it
didn't show up even when when I searched for "VBA". Sorry - novice
poster error.

However, if anyone in this group knows the answer, let me know...

Hello -

I'm using Excel 2000 VBA to programmatically create a userform
containing a listbox. I can watch the userform as it is created and
filled via the debugger, but as soon as the show line executes, the
listbox entries disappear - other items, such as checkbox labels &
buttons appear fine.

[ rest of post deleted]

 

[Peter Hewett]

Hi Keb

The reason that the values do not display is that they do not exist when the form is
instantiated! When you do a "Show" on a form if you don't explicitly state specify it
also implicitly does a Load as well. This creates a hidden global variable with the same
name as your user Form that references your defined Form! So the "Show" creates a new
Form and initialises it, so your ListBox control is empty. Since you using the "Visual
Basic for Applications Extensibility 5.x" library you can use this to add a
"UserForm_Initialize" event handler to your dynamic forms code and initialise the ListBox
within that code.

BTW - This is a Word news group

HTH + Cheers - Peter


(e-mail address removed) (Keb), said:

 

[Keb]

Thanks Peter for making me smarter.

BTW: I was down under in 1990 and enjoyed tromping around the Kiwi
bush - beautiful scenery & great beer - Don't know how ya stand it.
Hope to return someday...

 

[Peter Hewett]

Hi Keb

Glad to be able to help. Yep - NZ's a beautiful place to live - it got my vote!

Cheers - Peter

(e-mail address removed) (Keb), said:

Thanks Peter for making me smarter.

BTW: I was down under in 1990 and enjoyed tromping around the Kiwi
bush - beautiful scenery & great beer - Don't know how ya stand it.
Hope to return someday...

HTH + Cheers - Peter