Hello folks.
I've been trying to use the combobox trick (found on the Contextures website) to get a dropdown list convert to a combobox upon doubleclick (so I can get tweakable font settings, so the font in the drop down list isn't tiny when the page is zoomed out).
I can get it to work perfectly when the data being populated into the list is a named range of a specific static set of cells (eg, named range STAFF set to cells A2:A50 on sheet 'Staff_List"). However, I'm using a dynamic named range (where the range to show in the list changes depending upon the value in the cell in the first column).
The code is below. I have some msgbox's in it to help with checking the code. The first half of the IF statement checks to see if the cell that contains a name of a staff member has been double clicked, and if so loads the list of names from the STAFF named range on the 'staff_list' worksheet. This part works just fine - loads the list properly in the combobox that replaces the standard dropdown.
To test the second half I'm just trying to get it to work in one cell to start with (c5). The data validation works fine on this cell if I single click - the source is set to a named range (=TypeChoice), which I defined in the name manager as a long nested conditional that references several other named ranges (NC2, LEAVE2, etc) located within some hidden rows on the primary worksheet, Sheet1. So, the data validation source for the cell is "=TypeChoice", and TypeChoice is defined as:
=IF(Sheet1!$A5="NC",NC2,IF(Sheet1!$A5="LEAVE",LEAVE2,IF(LEFT(Sheet1!$A5,1)="1",TYPE1,IF(LEFT(Sheet1!$A5,1)="2",TYPE2,IF(LEFT(Sheet1!$A5,1)="3",TYPE3,"neither")))))
When cell C5 is double clicked, my msgbox tells me that the str variable has properly been set to "TypeChoice" (in the same way the msgbox under the 1st half of the IF statement tells me the str variable is set to STAFF), but the combobox contains nothing (just a single blank value). I don't know if its because the TypeChoice list is dynamic and not a very specific single range (like the STAFF case)>
I'm hoping someone can help out!
Thanks in advance,
Corman
<code>
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
If ActiveCell = Range("NAME") Then
MsgBox "name cell clicked twice"
Set ws = ActiveSheet
Set wsList = Sheets("Staff_List")
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Cancel = True
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
MsgBox str
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If
ElseIf ActiveCell = Range("C5") Then
MsgBox "c5 clicked twice"
Set ws = ActiveSheet
Set wsList = Sheets("Sheet1")
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
Cancel = True
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
MsgBox str
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 = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If
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
Application.EnableEvents = False
Application.ScreenUpdating = False
If Application.CutCopyMode Then
'allows copying and pasting on the worksheet
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
errHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
End Sub
</code>
I've been trying to use the combobox trick (found on the Contextures website) to get a dropdown list convert to a combobox upon doubleclick (so I can get tweakable font settings, so the font in the drop down list isn't tiny when the page is zoomed out).
I can get it to work perfectly when the data being populated into the list is a named range of a specific static set of cells (eg, named range STAFF set to cells A2:A50 on sheet 'Staff_List"). However, I'm using a dynamic named range (where the range to show in the list changes depending upon the value in the cell in the first column).
The code is below. I have some msgbox's in it to help with checking the code. The first half of the IF statement checks to see if the cell that contains a name of a staff member has been double clicked, and if so loads the list of names from the STAFF named range on the 'staff_list' worksheet. This part works just fine - loads the list properly in the combobox that replaces the standard dropdown.
To test the second half I'm just trying to get it to work in one cell to start with (c5). The data validation works fine on this cell if I single click - the source is set to a named range (=TypeChoice), which I defined in the name manager as a long nested conditional that references several other named ranges (NC2, LEAVE2, etc) located within some hidden rows on the primary worksheet, Sheet1. So, the data validation source for the cell is "=TypeChoice", and TypeChoice is defined as:
=IF(Sheet1!$A5="NC",NC2,IF(Sheet1!$A5="LEAVE",LEAVE2,IF(LEFT(Sheet1!$A5,1)="1",TYPE1,IF(LEFT(Sheet1!$A5,1)="2",TYPE2,IF(LEFT(Sheet1!$A5,1)="3",TYPE3,"neither")))))
When cell C5 is double clicked, my msgbox tells me that the str variable has properly been set to "TypeChoice" (in the same way the msgbox under the 1st half of the IF statement tells me the str variable is set to STAFF), but the combobox contains nothing (just a single blank value). I don't know if its because the TypeChoice list is dynamic and not a very specific single range (like the STAFF case)>
I'm hoping someone can help out!
Thanks in advance,
Corman
<code>
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
If ActiveCell = Range("NAME") Then
MsgBox "name cell clicked twice"
Set ws = ActiveSheet
Set wsList = Sheets("Staff_List")
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Cancel = True
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
MsgBox str
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If
ElseIf ActiveCell = Range("C5") Then
MsgBox "c5 clicked twice"
Set ws = ActiveSheet
Set wsList = Sheets("Sheet1")
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
Cancel = True
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
MsgBox str
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 = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If
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
Application.EnableEvents = False
Application.ScreenUpdating = False
If Application.CutCopyMode Then
'allows copying and pasting on the worksheet
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
errHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
End Sub
</code>
