VBA form with VBA combo box, Not showing a Scroll Bar

OneChief

New Member
Joined
May 5, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hello everyone.
Glad to be out of lock down :)

I have taken on someone else's project which is all over the place in regards to code, spreadsheets, functionality, etc. etc. Not one thing is done the same way and has no continuity.

I will do my best to show a few of the features and functionality to get to the end game of solving my problem.
This is my problem - No Scroll Bar and list is way too long showing all products.
1627431446259.png

The program starts like this with all combo/selection boxes are not visible and are validated according to what has been selected.
The "Door Material" boxes work fine and have scroll bars accordingly.
1627431648466.png

1627431675192.png

When it comes to the "Panel selection" none of these boxes have a scroll bar. Most of them don't need one, but there are some instances where it is required as shown in first image.
1627431799977.png


In VBA - The form looks like this;
1627432350394.png

As you can see the Comboboxes i need to look at are hidden somewhere, I cant find them. But I can select them when I highlight the "Frame2"
This is the Combobox of my concern (Cb3P2)
1627432536692.png

In the Code, there is so much going on. Some controls and items are controlled in the code some are controlled in spreadsheets.
Most of these ComboBoxes are controlled in the code.

What should I be searching for in the code to fault find where i can make these changes?

thank you

Side note: Novice in VBA
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,296
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I believe ColumnCount of 1 = no scroll bar, try increasing that value.
 

OneChief

New Member
Joined
May 5, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
I believe ColumnCount of 1 = no scroll bar, try increasing that value.
G'day mate.
I believe the ColumnCount is to do with what column is to be shown in the box.
Like FName. LName.
If I wanted the Last Name shown in box I would put a 2

Thank you for the suggestion though. I tried it anyway :)
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,162
Office Version
  1. 2010
Platform
  1. Windows
The controlling property for that is "list rows" which has a default value of 8 and that is what's showing in design mode.
I'd look at the loading of the list for that combo and see if list rows is being set to something different at the same time.
Perhaps the UserForm_Initialize sub.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,162
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Is it reasonable to think that no news is good news and the situation is rectified now ?
 

OneChief

New Member
Joined
May 5, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
The controlling property for that is "list rows" which has a default value of 8 and that is what's showing in design mode.
I'd look at the loading of the list for that combo and see if list rows is being set to something different at the same time.
Perhaps the UserForm_Initialize sub.
I have searched thru all the code in regards to above.
Unfortunately no result.

Thank you
 

OneChief

New Member
Joined
May 5, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is it reasonable to think that no news is good news and the situation is rectified now ?
G'Day mate.

Still unresolved.
 

OneChief

New Member
Joined
May 5, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
I'll put some code which I think is relevant to the functions I am looking for.
(I know this is very difficult and I am not hopeful of a solution given you guys and gals are not in front of the PC.)

Private Function Fill_Panel_Droplist(lvdrop As ComboBox, lvstart As String, lvWidth As Integer)
On Error GoTo Resumerror:
Dim lvlabel As String
If lvstart > "" Then
If lvdrop.ListCount > 0 Then
Do While lvdrop.ListCount > 0
lvdrop.RemoveItem (0)
Loop
End If
Sheet4.Range(lvstart).Select
Do While ActiveCell.Value > ""
If ActiveCell.Offset(0, 3).Value = "Y" Then
lvdrop.AddItem (ActiveCell.Offset(0, 0).Value)
End If
ActiveCell.Offset(1, 0).Select 'next cell down
Loop
End If
On Error Resume Next
Exit Function
Resumerror:
SaveBackup
MsgBox ("Error has occured in SELECT STYLE, Backup saved")
On Error Resume Next

End Function

The "SelectedPanelID = 3" is what I am looking for and the combo box that corresponds to it. (Strange thing is selected ID - 10 gives me a scroll bar.)
Another section of code -

Controls.Item("Label39").Top = 180
Controls.Item("Label39").Caption = "Select Panel Colour"
'Controls.Item("Cb3P").ListWidth = Sheet4.Range("CU21").Value
'Controls.Item("Cb3P").ListRows = Sheet4.Range("CS21").Value
Controls.Item("Cb3P").Enabled = True
Controls.Item("Cb3P").Left = 7
Controls.Item("Cb3P").Top = 192
Range("DG32").FormulaR1C1 = "=R[-5]C"

Controls.Item("Cb3P2").Enabled = False
Else
If Sheet4.Range("K4").Value > 1 Then
If SelectedPanelGroup = "Polytec" Then
lvMaterialId = Return_MaterialID(Controls.Item("Cb3P2").Text, Sheet4.Range("BL119").Value) 'door material
Range(Range("BP119")).Value = lvMaterialId
ElseIf SelectedPanelID = 3 Then
lvMaterialId = Return_MaterialID(Controls.Item("Cb3P2").Text, Sheet4.Range("BL118").Value) 'door material
Range(Range("BP118")).Value = lvMaterialId
ElseIf SelectedPanelID = 5 Then
lvMaterialId = Return_MaterialID(Controls.Item("Cb3P2").Text, Sheet4.Range("BL118").Value) 'door material
Range(Range("BP118")).Value = lvMaterialId
ElseIf SelectedPanelID = 10 Then
lvMaterialId = Return_MaterialID(Controls.Item("Cb3P2").Text, Sheet4.Range("BL118").Value) 'door material
Range(Range("BP118")).Value = lvMaterialId
End If
'lvMaterialId = Return_MaterialID(Controls.Item("Cb3P2").Text, Sheet4.Range("BL118").Value) 'door material
'Range(Range("BP118")).Value = lvMaterialId
Else
If Sheet4.Range("CS80").Value > "" Then
Sheet4.Range(Sheet4.Range("CS80").Value).Value = Controls.Item("Cb3P2").Value
End If
End If
Controls.Item("Cb3P2").Enabled = False
AddInfo_Panels


I'm not sure if this helps with searching for things to help.

But thanks anyway.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,162
Office Version
  1. 2010
Platform
  1. Windows
With what you've shown, the commented out
VBA Code:
'Controls.Item("Cb3P").ListRows = Sheet4.Range("CS21").Value
would make me think the number of rows to show in the Cb3P2 dropdown would be set the same way.
I would search for ListRows within the Current Project and see if anything shows up for Cb3P2
If you don't find anything I then have no idea, sorry.
 

Forum statistics

Threads
1,141,060
Messages
5,704,039
Members
421,323
Latest member
Exidous

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