Reset user form combo box to top record

chef

Well-known Member
Joined
Jul 10, 2002
Messages
610
Office Version
  1. 365
  2. 2016
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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
 
Upvote 0
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 :
VBA Code:
Sub ResetFormDropDown()
Dim dd As DropDown
Set dd = ActiveSheet.Shapes("Drop Down 1").OLEFormat.Object
dd.ListIndex = 1
End Sub
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top