UserForm Drop-down Menu: Selective Starting & End Point

[Ottsel]

Hi everyone,

Attempting to have my down-drop list show dates that are currently not hidden, but only dates that have data within them. Each week a new line is entered and that new date is populated, but having to start from the VERY beginning can be a hassle - even more so, since you can't wheel the menu and have to drag it.

Start of list: Hidden rows + 15, since the data begins on row 15.

End of list: Taking not blank rows - Hidden rows - blank rows will give me the point of where the list should end

Currently rows 15-692 are hidden and the date within C693 is 01/03/20.

The last row is 819 with the date 06/03/22. 06/10/22 will be the next input, but there's no reason to list it until data is entered into D820.

Not sure if its possible, but if there is a way to use your mouse wheel instead of having to drag it I'd love to read up on how to implement that into this and possible future drop-down userform menus.

With that said: I've taken my amateur VBA knowledge (which is ranking it too high in my opinion) and done the following:

Sub DropDownCombo1()
Dim ws As Worksheet
Dim rHidden, x As Long
Dim rng As Range
Dim rBlank, nBlank As Integer
Dim bStart(), bEnd() As Integer

'Obtain # of hidden rows
Set ws = Sheets("Current")
rHidden = 0
For x = 1 To ws.Range("c14").CurrentRegion.Rows.Count
    If ws.Cells(x, 1).EntireRow.Hidden = True Then
        rHidden = rHidden + 1
    End If
Next x
'Obtain # of rows blank from D14:D1085
rBlank = Application.WorksheetFunction.CountBlank(ws.Range("D14:D1085"))
'Obtain # of rows NOT blank from D14:1085
nBlank = Worksheets("Current").Range("C15:C1085").Cells.SpecialCells(xlCellTypeConstants).Count
'now to pull it all together... somehow
ReDim bStart(rHidden + 15)
ReDim bEnd(nBlank - rHidden - rBlank)
UserForm.ComboBox1.AddItem

End Sub

 

[Micron]

Not sure I understand all of what's going on. On one hand, seems that the combo box is a value list populated by a range instead of being the range itself. Perhaps because of 'hidden' values are in the range. Regardless, maybe what could work for you is on the combo dropbutton_click event, start the list at some index point that you pick. Perhaps pick the row as listcount - listrows or something similar? e.g.

Private Sub ComboBox1_DropButtonClick()
With Me.ComboBox1
     .ListIndex = .ListCount - .ListRows + 1
End With

End Sub

Could drop the +1. The idea there being that the last row in the displayed list would be the last row item. Untested for a value list but works for a range so I don't see why it wouldn't work for a value list.

 

[Ottsel]

Have C15 through C1100 I have a long range of dates, but once a week data is input into that date. Currently it backtracks years, but at times we like to look back and compare by unhiding those rows.

VBA is still a learning curve for me, so my code might come off, well confusing as it is still for me. I gave what you posted a quick shot, but couldn't figure out how to add it or have it be the solution to my issue above.

 

[Micron]

Possibly seeing some data and the desired result would help someone to help you. It's usually better than describing.
A quick search on how to use mouse wheel in a combo box produced this, among others:

Mouse wheel scroll through ComboBox dropdown.

Hi, I'm using Excel 2016 64-bit and have a ComboBox with a dropdown that contains 150+ lines. I'm trying to set it up so that I can scroll through the lines with the mouse wheel.

www.mrexcel.com

 

[Ottsel]

Its data from Random lengths that I record. Need a drop down list of the dates with actual data currently. Each Friday I input the newest info that's released. I list all the weeks during the year to avoid having to see what the next friday will be. Here's a screenshot if that helps.

On a side note: thank you for sharing that link! I'm new to MrExcel, so I didn't think to check the forums for the scroll feature. Appreciate it!