Changing the font size of a data validation list

[carcharoth2554]

Hi guys,

Win 7 64bit Excel 2010

I have grapppled with with one for a while now and through direction ended up finding a nifty piece of code that overlays a combobox onto your validation list when double clicked; very useful. When I went to integrate this code into a second worksheet I found that the combobox fails ("TempCombo" still appears but loads no data options) when the data validation list is based on data in another worksheet. I really do not want to load all of the options directly into the validation list as I like to keep my data dynamic and easy to use for my colleagues. Could somebody please take a shot at solving this? I think it is something simple on the top level effecting visibility but can't for the life of me get my head around it.

Thanks!!

Referenced code: Excel Data Validation Combo box

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
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 + 5
      .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
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

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

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

 

[carcharoth2554]

and now just to makes things more fun I tried an edit to move all my reference data into some hidden cells on the same worksheet as the code as a temporary measure just so the sheet can be used. When I implemented the code it immediately hit back with a 'Run-time error '91': Object variable or With block variable not set'.

Not a clue what it is complaining about as it essentially should be working in the exact same way as when i originally started on the code

Help please!!

 

[RhodEvans]

Not sure if this will help or not, but have you tried using a defined name for your validation list. I use them all the time as it allows you to have all your validation lists on the same worksheet (I usually create a hidden sheet called lists where I keep all of them)
The formula below allows you to add stuff to your validation list without changing anything:

=Lists!$C$2:INDEX(Lists!$C:$C,COUNTA(Lists!$C:$C))

and then you can put whatever you have called the defined name in as the formula for your validation list e.g. =Namelist

not sure if this will help with your issue, but it does allow you do use validation from other sheets.


Hope it helps

Rhod

 

[carcharoth2554]

Not sure if this will help or not, but have you tried using a defined name for your validation list. I use them all the time as it allows you to have all your validation lists on the same worksheet (I usually create a hidden sheet called lists where I keep all of them)
The formula below allows you to add stuff to your validation list without changing anything:

=Lists!$C$2:INDEX(Lists!$C:$C,COUNTA(Lists!$C:$C))

and then you can put whatever you have called the defined name in as the formula for your validation list e.g. =Namelist

not sure if this will help with your issue, but it does allow you do use validation from other sheets.


Hope it helps

Rhod

Hi Rhod,

Thanks for the response but the issue is actually with the VBA. I have always quite happily used validation data from another worksheet but it is the combobox that appears to fail to pickup the validation data from another worksheet. I moved the data to the same worksheet as the validation in an attempt to placate the combobox...this in turn cause more problems.

The intent here was simply to increase the font size of the validation list itself but is steadily turning into a nightmare!

Thanks.