Dropdown in a ribbon: pass a value to a cell

[JohnBi]

Hi,
how can I get a selected value from a dropdown placed in a customize ribbon to a cell?
Basically I would like to use the following macro associated/on action to a dropdown placed in the ribbon, but I am not able to write the value in cell H6:

Private Sub ComboBox1_Change()
Dim lo As ListObject

Set lo = Sheet13.ListObjects(1)
lo.AutoFilter.ShowAllData

Application.ScreenUpdating = False
ActiveSheet.ListObjects("Tbl_InOut").Range.AutoFilter Field:=2, Criteria1:=[H6] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub

Any help is appreciated. Thanks in advance and Regards
John

 

[GlennUK]

Can you explain how you did this : "from a dropdown placed in a customize ribbon" ... like, how did you add the dropdown to a customized ribbon, exactly?

 

[JohnBi]

Hi Glenn,
this are the codes Xml + Vba:

<group id="Searchgroup" label="SEARCH">
<dropDown
id="TypeDropDown"
label="Search"
sizeString="WWWWWWWW"
supertip="Search"
image="Filter"
getItemCount="Toolbar_EmpTypeCount"
getItemLabel="Toolbar_EmpTypeNames"
getSelectedItemIndex ="Toolbar_EmpTypeGetDefaut"
onAction="Toolbar_EmpTypeSelect"/>
</group>

''''SEARCH DROP DOWN
Sub Toolbar_EmpTypeCount(control As IRibbonControl, ByRef ReturnedVal)
ReturnedVal = Sheet4.Range("C2").End(xlDown).Row - 1 'Total Employee Types
End Sub

Sub Toolbar_EmpTypeNames(control As IRibbonControl, index As Integer, ByRef ReturnedVal)
ReturnedVal = Sheet4.Range("C" & index + 2).Value 'Employee types
End Sub

Sub Toolbar_EmpTypeSelect(control As IRibbonControl, id As String, index As Integer)
Clear_All_Filters_Table4
Sheet13.Range("G6").Value = index + 1 'Set Selected #

Dim lo As ListObject
'Set reference to the first Table on the sheet
Set lo = Search13.ListObjects(1)
'Clear All Filters for entire Table
lo.AutoFilter.ShowAllData
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Tbl_InOut").Range.AutoFilter Field:=2, Criteria1:=[H6] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub

Sub Toolbar_EmpTypeGetDefaut(control As IRibbonControl, ByRef DefaultItem)
DefaultItem = Sheet4.Range("C2").Value 'Set Default Type
End Sub

In the meantime, with a Vlookup in H6, (based on the value of G6) workaround I have been able to find a possible solutiion, that seems to work.
However, I am wondering if is possible to get in H6 the value of the dropdown, instead the numbers (1 for the first, 2 for the second etc) to avoid the Vlookp formula.

Thanks again for your help. Regards
John

 

[GlennUK]

Would a listbox give the functionality you want, rather than a dropdown? Am guessing here ... it's been years since I've messed around with the ribbon.

 

[JohnBi]

HI Glenn,
a listbox could be interesting. Let me try and I'll come back if I need more help.
In the meantime thanks for your suggestion. Regard
John