USE OF DROPDOWN in Combobox-a problem

[davidm]

Hi all,

The following code uses the DROPDOWN feature to readily display 25 items of the list.

Private Sub UserForm_Initialize()
With ComboBox1
.RowSource = "a1:f100"
.ColumnCount =6
.listrows=25
.DropDown
End With
End Sub

It works fine except that the list comes detached from the Userform. Can someone supply a cure such that the list will be properly aligned on the Userform?

TIA

David

[Leith Ross]

Hello David,

Can you explain "detached" in this case? Is the drop down simply longer than the form or is something else happening?

Sincerely,
Leith Ross

[Robert Bruce]

Roedd <<davidm>> wedi ysgrifennu:

> Hi all,
>
> The following code uses the DROPDOWN feature to readily display 25
> items of the list.
>
> Private Sub UserForm_Initialize()
> With ComboBox1
> RowSource = "a1:f100"
> ColumnCount =6
> listrows=25
> DropDown
> End With
> End Sub
>
> It works fine except that the list comes detached from the Userform.
> Can someone supply a cure such that the list will be properly aligned
> on the Userform?
>

I suspect that calling the dropdown method before the form is properly
initialised is causing the problem. Try moving the Dropdown method to the
Activate event (the rest of the code can stay where it is).

--
Rob

[davidm]

Thanks Bruce. Using Private Sub UserForm_Activate() in place of the Initialize event did the trick. Not for the first time, I am confounded by the nuances between these two events. Does it seem safer to use the Activate event in all cases?

Ross:

The list occupies the top left-hand corner of the worksheet, completely sheared off the userform. I believe if you run my code (using Initialize event)you will find the same result.


David

[Robert Bruce]

Roedd <<davidm>> wedi ysgrifennu:

> Thanks Bruce.

Rob, actually.

> Using *Private Sub UserForm_Activate()* in place of the
> Initialize event did the trick. Not for the first time, I am
> confounded by the nuances between these two events. Does it seem
> safer to use the Activate event in all cases?

No. these events have two entirely different puposes in the lifecycle of a
form, though this is not always clear if you show your forms in the
'default' manner.

The Initialise event (same as the Load event in VB forms) runs when the form
is first created. This can be via explicitly calling the Load method of the
form:

UserForm1.Load

creating an new object variable of the type of your form:

Dim frmMyForm as UserForm1
Set frmMyForm = New UserForm1

or by showing the form without first loading it (this causes the form to be
automatically loaded before being shown):

UserForm1.Show

The Activate event, in contrast, only fires when the form is shown. So for
the first two examples above it will not fire and for the third example it
will fire immediately after the Initialise event.

However, you should also note that the Activate event will fire again should
you hide (without unloading) and then show your form again. To see this in
action, create a form in an empty workbook, add a listbox and a button (keep
the default names) and then add the following code to the form:

Private Sub CommandButton1_Click()
Me.Hide
Me.Repaint
Application.Wait _
(Now + TimeValue("00:00:01"))
Me.Show
End Sub

Private Sub UserForm_Activate()
Me.ListBox1.AddItem "Activate"
End Sub

Private Sub UserForm_Initialize()
Me.ListBox1.AddItem "Initialize"
End Sub

Now run the form and click the button a few times and you'll see what I
mean.

Finally, add a new standard module to your project and paste in the
following:

Sub test()

Dim frmMyForm As UserForm1
Set frmMyForm = New UserForm1

frmMyForm.Show

End Sub

Place your cursor inside the procedure and hit F8. Note how the form events
occur at completely different points in the calling code.

HTH

Rob