Reset user form combo box to top record

[chef]

Hi

I have a combo box ( inserted from Developer and User form control, not active x) which is filled dependent on list of depts in column T. It's a dependent combo box and populated based on department chosen and can have 4 records or 50
The issue is that the combo box can show a lot of blanks and user has to scroll up to the top of combo box to get to first record again ( which many dont know they can do and just assume nothing in combo box)


I tried this offset as I though this would resolve but this still highlights blank rows
=OFFSET(Look!$T$1,1,0,COUNTA(Look!$T:$T)-1,1)

Not really sure what I'm doing wrong and I'm just trying to populate combo box with only records shown in column T and not have any blanks in the combo box.
How can I have a user choose from drop down combo and have reset so when another unit chosen the dept combo box is reset to top record?

hope this makes sense and any help greatly appreciated

regards

 

[James006]

Hi,

You could create a Name range using your formula ...

and in the Format Control of your ComboBox, in the Input Range box, place the newly created Name range

 

[chef]

thats what I did and why I thought would negate blanks where appropriate.
Still seems to display blank records and have users scroll to top.

Not major issue, but not all aware of and just trying to make it neater to default to first record in combo box each time

 

[James006]

A couple of remarks :
1. Form Controls are usually less reliable and less flexible than their ActiveX counterparts ....
2. Should you need to reset to the top your dropdown object :

Sub ResetFormDropDown()
Dim dd As DropDown
Set dd = ActiveSheet.Shapes("Drop Down 1").OLEFormat.Object
dd.ListIndex = 1
End Sub

 

[chef]

thats really helpful and appreciate your time to resolve.
Got me thinking and I think i will also have a go at using active X instead. I have always just defaulted to form controls
regards

 

[James006]

Glad to hear this is helping you out