Userform CommandButton/Listbox

[evans_matthew]

This is my first time working with Userforms. I am trying to create a userform with a listbox where I can select multiple items in a list, and then submit them to generate a string including those results.

1. I have the listbox doing what I want it to do but I want it to dynamically adjust its size depending on the number of items and I can't seem to make it do that. I've attempted to adjust the height property by a multiplier on the item count but no success. It just stays the static size it was when I created it and puts a scroll bar when its too small to show all the items. How do I work around this?

2. I added a commandbutton to the same userform to act as my submit button but the command button will not show up. It doesn't seem to matter where I put it. I have attempted to force visible=true using VBA to no avail. I have moved it to all various places on the form.

Does anyone have any ideas I could try on these two items?

Thank you

 

[Norie]

1 What code have you tried for that part?

2 Is the command button visible in design mode?

 

[evans_matthew]

1 What code have you tried for that part?

UserForm.ListBox1.Height = 200 * listitemcount
My theory was making the height 200* the list of items would make it about the right size but no effect on the size of the listbox whatsoever.

2 Is the command button visible in design mode?

Yes it is - but not in execution mode.

 

[Norie]

Where are you putting the code to resize the listbox and where/how are you setting the value of the variable listitemcount?

 

[evans_matthew]

Where are you putting the code to resize the listbox and where/how are you setting the value of the variable listitemcount?

The entire code in the sub I created, which is in a separate module, as a test macro is :

Dim listitems(20)
listitemcount = 0

For x = 1 To Sheets("Contacts").Rows.Count
If Sheets("Contacts").Cells(x, 4) = "Valid" Then
listitemcount = listitemcount + 1
listitems(listitemcount) = Sheets("Contacts").Cells(x, 1)
End If
Next x

For p = 1 To listitemcount
PartForm.ListBox1.AddItem (listitems(p))
Next p

PartForm.ListBox1.MultiSelect = fmMultiSelectMulti
PartForm.ListBox1.Height = 200 * listittemcount
PartForm.SubmitButton.Top = PartForm.ListBox1.Top + PartForm.ListBox1.Height + 20
PartForm.SubmitButton.Visible = True

PartForm.Show

 

[Norie]

In that code the value of listittemcount will always be zero.

However the value of the variable named listitemcount will reflect the no of items in the listbox.

So, it could be that the problem is a typo.