ActiveX Combo missing Slider, XL ver 2010

OccasionalGeek

New Member
Joined
Mar 1, 2012
Messages
12
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437
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

New Member
Joined
Mar 1, 2012
Messages
12
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437

ADVERTISEMENT

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.
 
Last edited:

OccasionalGeek

New Member
Joined
Mar 1, 2012
Messages
12
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

New Member
Joined
Mar 1, 2012
Messages
12

ADVERTISEMENT

See my signature block below.

Oh. Doh.

Thanks - And I just downloaded the HTML editor, also. And will use
Code:
 instead of  [indent]. 

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

O-Geek
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437
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:
Code:
[color=darkblue]With[/color] Sheets("Sheet1").ComboBox1
    .ListFillRange = "rng_ListFillRange"
    .ListRows = 8
[color=darkblue]End[/color] [color=darkblue]With[/color]
 

OccasionalGeek

New Member
Joined
Mar 1, 2012
Messages
12
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,203
Members
425,267
Latest member
bishopc22

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
Top