PDA

View Full Version : Control Box Data Validation won't change font size



allthingstec
06-14-2018, 04:59 PM
Hi. Hopefully someone can assist me...
The code pasted below allows me to click on a sell that has data validation....then control box appears enabling me to use validation drop down list that is also controlled in vba.
the purpose for me doing this is so I can change font size in drop down list....everything works fine except in combo box properties I change font & listrow but it has no effect on the box.
I use Activex (Microsoft Professional Plus 2010. I pasted code below.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler


If Target.Count > 1 Then GoTo exitHandler


Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If


On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If


exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler
End Sub

p45cal
06-16-2018, 02:02 AM
Seems to work here OK; adjusting those properties stick.
Make sure you have no other event code affecting this combobox, any code which starts with the likes of Private Sub TempCombo_~ , which might be resetting the font/Listrows.
If there's still a problem (and ActiveX controls on a sheet are known to be idiosyncratic sometimes) then you should be able to reset Listrows, Font and font size within your current code:
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
.Object.ListRows = 10 '<<<< added.
.Object.FontName = "Comic Sans MS" '<<<< added.
.Object.FontSize = 10 '<<<< added.
End With

allthingstec
06-18-2018, 11:04 AM
Sorry for the delayed response.
I appreciate your assistance, but I haven't had the opportunity to get back to that issue to even try it.
When I do......I will surely let you know. Thanks again.