Microsoft Office Forums - View Single Post - [Solved] Adding ComboBox to be displayed from anywhere in the WORKBOOK

Shnitzel · 01-05-2017, 02:06 PM

Hello!
i'm very new to VBA for excel, and currently trying to implement a combo box in my workbook.

i have a big Worksheet which includes many worksheets, one of them has a list of guides' names, which i selected there ("A2:A80") and named it "Guides_names".
i also added a vba macro (in module) which can and is activated from anywhere in the workbook, to display the list:

Code:

   With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Guides_names"
    .IgnoreBlank = True
    .InCellDropdown = False
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
  End With

and it works well: it implements the list in any cell i choose and opens it, and the selection is then displayed in the cell.
But: 1. Font of the names in the lise are very small; 2. There is no auto-refill.

So i decided to use it and hide the validation list, and to add Combo box.
I found lots of examples and did manage to add a nice combo box
into one of the sheets and it perfectly opens while double-clicking any cell i click inside the sheet but when i implement similar code in a module:
i can't automatically open the list (the problem is with the Me.TempCombo.DropDown: it doesn't know the ME), and after selecting a value from the list the combo box keep being displayed, and when i try to activate it from another sheet it won't work at all...
My main question is if there is any way to implement a combo box that can be activated from any cell in the workbook,
and how can combobox_lostFocus and other sheets function can be implemented outside, in the workbook.
here is my current code... basiclly based on Debra Dalgleish code.
Thank you for any help or suggestion!

Code:

 
Sub Select_Guide()

Dim str As String
Dim cboTemp As OLEObject
Dim rngSelectedRange As Range
Dim ws As Worksheet
Set ws = ActiveSheet

' Here i use the original code:
 With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Guides_Names"
    .IgnoreBlank = True
    .InCellDropdown = False
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
 End With

  Set rngSelectedRange = Range(ActiveCell, ActiveCell.Offset(numRows, numCols))

  Set cboTemp = Worksheets("dbdb").OLEObjects("TempCombo")

  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = "Guides_Names"
    .LinkedCell = ""
    .Visible = False
  End With
  On Error GoTo errHandler

  If rngSelectedRange.Validation.Type = 3 Then
    'if the cell contains a data validation list
     Application.EnableEvents = False
    'get the data validation formula
    str = rngSelectedRange.Validation.Formula1
    str = Right(str, Len(str) - 1)

    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = rngSelectedRange.Left
      .Top = rngSelectedRange.Top
      .Width = rngSelectedRange.Width + 5
      .Height = rngSelectedRange.Height + 5
      .ListFillRange = str
      .LinkedCell = rngSelectedRange.Address
    End With
    cboTemp.Activate

    'open the drop down list automatically
    ' Me.TempCombo.DropDown - worked only when called by double_click function
    TempCombo.DropDown  ' This doesn't really opens the combo box,  also cboTemp.DropDown doesn't
  End If

  errHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub