PDA

View Full Version : [SOLVED] ComboBox: Possible to change Popout direction?



Stargazer
09-10-2013, 04:12 AM
Hiya,

This feel like almost the most ridiculous question, but it has really flummoxed me...

I'm building a simple UI on a userform in Excel2007. The Userform sits at the bottom of the screen so that the user can input data into it and still have the data visible above it.

Near the bottom edge fo the userform is a ComboBox. The list is 8 items long, but I only see the top 3 because the list expands out of the boundaries of the application/screen.

Is there a toggle or something I can do to make the list expand upwards instead of downwards if the screen boundaries do not permit the entire list to be seen? Or is this a hole in Excel and the userform needs to be redesigned to keep ComboBoxes higher up?

Many thanks in advance,

Rob.

Kenneth Hobs
09-10-2013, 09:36 AM
It adjusts as needed for me. Try posting an example.

Press
09-10-2013, 01:16 PM
Hi

You could stretch the Form:


Private Sub UserForm_Initialize()

With Me
.ScrollBars = fmScrollBarsVertical
.ScrollHeight = .InsideHeight * 2
End With

End Sub

Stargazer
09-11-2013, 01:00 AM
Hi,

@Kenneth: If the dropdowns aren't running out of the monitor boundaries then it could possibly be an environmental variable that changes the behavior. Admittedly, I could have tested a bit more to see if it was something to do with the dual-montior setup I use.

@Press: A larger form or a form with scrollbars, while it would do the job is outside of the direction I'd want to go. Some of the people I code for around here need things to be obvious and simple. For many of them, scolling to something is too much effort... I know, I know... It sounds like I work with monkeys. Sad but true.


On the plus-side, I may have found something that solves it for me. I went into a clicking frenzy on the object properties and found that if the 'ListStyle' is changed from the default value of 'frmListStylePlain' to 'frmListstyleOption', the behaviour of the pop-out menu changes to act like one would expect, whatever the environmental variable that was preventing it from doing it before.

The only snag is that it puts an ugly radial-bullet-point in from of the items in the list, but I can live with that if it means the brainless bunch using it can see everything in one go.

Thanks for the replies,

Regards,

Rob.

SamT
09-11-2013, 05:27 AM
Rob,

Try declaring two constants DefaultTop and SelectedTop. While the ComboBox has the focus, set its .Top = SelectedTop.

I do something similar with DefaultHeight and SelectedHeight to keep them sized like Textboxes when not in actual use.