ActiveX Combo missing Slider, XL ver 2010

[OccasionalGeek]

I'm using Excel 2010. I'm also a first-time poster on this forum, so I may amend or post clarification, later, if needed. And I welcome comments on forum protocol or requirements.

I use VBA code extensively in the application. There is a "trigger" (on the down arrow of the Combo) to a subroutine that repopulates the fill list range and resizes the range depending on the number of items found (it contains directory listings for specified file names). That routine resizes/creates the fill list range name each time it runs.

When I create an ActiveX Combo linked to a cell on the same sheet and using a range for the "Fill List" that is on the same sheet, it works fine. Sliders appear on the drop down list no problem.

However, when I "save>close>open" the workbook (an .xlsm file, and macros are enabled), the sliders aren't there, even if the range contains more items than can fit on the size of the drop down list. (I'm not sure it is the close/open process that causes the problem - haven't uniquely identified the exact action that causes the slider to disappear.)

If I re-create the dropdown, it works fine, again, but then loses the slider again at some point I can't clearly identify, yet.

I've Googled "Excel Combo missing slider" and all sorts of related key words, but no joy on finding anyone reporting the same problem.

Anyone have similar experience, or can you offer a possible suggestion? I've experimented with most of the remotely connected properties of the Combo object, but can't find a solution.

Thanks.

 

[AlphaFrog]

Can you post your code that; "repopulates the fill list range and resizes the range depending on the number of items found"? Also, can you show\describe the "trigger"?

If we can replicate what you are doing, maybe we can determine the cause.

 

[OccasionalGeek]

Thanks for the quick reply.

"Trigger" code (on the worksheet containing the Combo).

Private Sub cbxSourceFileName_DropButt*******()
CreateFileList
End Sub
​

And the "CreateFileList" routine:

Sub CreateFileList()
Dim vCtr As Variant
Dim rTargetCell As Range

Application.ScreenUpdating = True 'DEBUG Make FALSE for release

ActiveSheet.Unprotect

ActiveSheet.Range("A3:A100").Clear

Set rTargetCell = Range("A3")
vCtr = Dir(ThisWorkbook.Path & "\*.xls*")
Do While True
If vCtr = "" Then
If rTargetCell.Address = "A3" Then
MsgBox "There are no Report Card files in the directory."
End If
Exit Do
Else
Select Case True
Case Right(vCtr, 1) = "m"
'Do nothing - invalid file name (Macro File)
Case Left(vCtr, 17) = "ReportCardSummary"
'Do nothing - invalid file name (Summary File)
Case Else
rTargetCell = vCtr
Set rTargetCell = rTargetCell.Offset(1, 0)
End Select
vCtr = Dir
End If
Loop



With ThisWorkbook
.Names.Add Name:="rng_ListFillRange", RefersTo:=ActiveSheet.Range("A3").CurrentRegion
.Names("rng_ListFillRange").Comment = ""
End With

With ActiveSheet
.Sort.SortFields.Clear
.Sort.SortFields. _
Add Key:=Range("A3"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With .Sort
.SetRange Range("rng_ListFillRange")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

Bail:

ActiveSheet.Protect

Application.ScreenUpdating = True

End Sub
​

The HTML doesn't correctly show the indenting, but the code lines are accurate. If there is a better way to show correctly formatted code, I'd appreciate the info.

Thanks for the help.

 

[AlphaFrog]

If there is a better way to show correctly formatted code, I'd appreciate the info.

See my signature block below.

 

[AlphaFrog]

However, when I "save>close>open" the workbook (an .xlsm file, and macros are enabled), the sliders aren't there, even if the range contains more items than can fit on the size of the drop down list. (I'm not sure it is the close/open process that causes the problem - haven't uniquely identified the exact action that causes the slider to disappear.)

When you open the file and the dropdown list slider is missing, is the Named Range "rng_ListFillRange" correct? Does it correctly define the data list?

I notice in your CreateFileList code, you reference the ActiveSheet and not a specific sheet name. Perhaps you are creating a file list from the wrong sheet. Is that possible in your situation.

 

[OccasionalGeek]

Yes, I've checked the list range name, and it is correct. The list populates (the macros runs) with "Workbook_Open" in addition to being triggered by the drop down "...onDblClick", and I checked it before and after the routine ran when opening the workbook.

Good thought about "ActiveSheet", but no, it's always going to be the correct sheet. There is only one sheet in the workbook, and that sheet HAS to be active to activate the "...OnDoubleclick" macro. It is also the sheet active when the workbook opens.

It is very strange. If you like, I could send you the entire workbook with the main routine disabled and missing to protect confidential client information. Disabling the main routine will not affect the routines used in this problem, at all.

 

[OccasionalGeek]

See my signature block below.

Oh. Doh.

Thanks - And I just downloaded the HTML editor, also. And will use

 instead of  [indent]. 

This looks like a great forum. I look forward to getting integrated and more familiar with it. 

O-Geek

 

[AlphaFrog]

It is also the sheet active when the workbook opens.

How do you know for sure. I can think of a couple of ways where that might go wrong. I really suggest you add a specific sheet reference before each range reference in your code. It's good programming practice and it eliminates the possibility of that being a problem now or for something else in the future.


Another suggestion is to perhaps add to your code a line that sets the number of visible rows in the combobox. That may coerce it to show the slider. Just a stab in the dark.

Example:

[color=darkblue]With[/color] Sheets("Sheet1").ComboBox1
    .ListFillRange = "rng_ListFillRange"
    .ListRows = 8
[color=darkblue]End[/color] [color=darkblue]With[/color]

 

[OccasionalGeek]

Thanks, AlphaFrog.

I did test your "coercing" suggestion, and it worked! It's one of those "who knows" kind of things that shouldn't have happened anyway, but it did the trick.

Meanwhile, I'd already re-written (and simplified!) the approach so I'm happier with it, AND it works even without the added code - but I'm adding it anyway to preclude another "unexpected" occurrence.

And - I appreciate the value of your suggestion to specify the sheet, and understand it.

A genuinely helpful forum, and your help was terrific. Thanks.

OccasionalGeek

 

[AlphaFrog]

You're welcome and thanks for the feedback.