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


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=558308

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=558308

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

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


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=558308

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