Free Microsoft Excel 2013 Quick Reference

ActiveX Combo box tutorials

I'm wondering if there are any ActiveX combo box tutorials with a list of what the commands mean. I've googled things and seen the microsoft explination, but i was looking for more information than that.

I've tried messing with these for a few days to get it to do what I want. I've posted to multiple boards with no response.

for example, I went my backstyle to be transparent and I want it to remain transparent after I make a selection and move off of the combobox.

I thought maybe the VBA lostfocus option would do that if i set the backstyle to transparent through that, but it hasn't worked.

so, I've decide that maybe I don't really know what excel means when it says 'focus'.

That amoung other questions.

Any suggestions on where i can go? Thank you.


Post your answer or comment

comments powered by Disqus
I'm attempting to apply a macro (that worked on a form control combo box ) to an activex combo box. However, I am getting a type 13 mismatch error and I'm not sure how to get the combo box to work correctly. Any help would be greatly appreciated. Here is the macro code for the hide rows that I'm trying to apply to the Activex control:


	VB:
	
 HideRows() 
    Dim Cse As Integer 
     
    Cse = Sheets("Calculations").Range("b175") 
    Application.ScreenUpdating = 0 
    Sheets(8).Activate 
    With Sheets(2) 
        Select Case Cse 
             
        Case "2" 
            Rows("159:165").EntireRow.Hidden = False 
            Rows("98:104").EntireRow.Hidden = False 
        Case "3" 
            Rows("159:165").EntireRow.Hidden = False 
            Rows("163:165").EntireRow.Hidden = True 
            Rows("98:104").EntireRow.Hidden = False 
            Rows("102:104").EntireRow.Hidden = True 
        Case "3" 
            Rows("159:165").EntireRow.Hidden = False 
            Rows("165:165").EntireRow.Hidden = True 
            Rows("98:104").EntireRow.Hidden = False 
            Rows("104:104").EntireRow.Hidden = True 
        Case "4" 
            Rows("159:165").EntireRow.Hidden = False 
            Rows("164:165").EntireRow.Hidden = True 
            Rows("98:104").EntireRow.Hidden = False 
            Rows("103:104").EntireRow.Hidden = True 
        Case "5" 
            Rows("159:165").EntireRow.Hidden = False 
            Rows("163:165").EntireRow.Hidden = True 
            Rows("98:104").EntireRow.Hidden = False 
            Rows("102:104").EntireRow.Hidden = True 
        Case "6" 
            Rows("159:165").EntireRow.Hidden = False 
            Rows("164:165").EntireRow.Hidden = True 
            Rows("98:104").EntireRow.Hidden = False 
             
        End Select 
    End With 
    Sheets(2).Activate 
    Application.ScreenUpdating = 1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Even when I changed the "" to text to reflect the index value, it still errored out.

How do I format the text in an ActiveX combo box? I would like to increase the font.

Hello all,

I have an ActiveX combo box which has four drop-down items; one of the items was misspecified and I simply need to change the text - the combo box functions perfectly otherwise.

Now, I was able to modify the text in design view - the problem is, once I left the design view, my drop-down items remained unchanged.

If anyone has any pointers on this, that would be great!

Thanks in advance,

Alex

I'm building an application that uses a large number of activex combo boxes. These boxes are getting loaded from specific fields in an associated database.

I know ActiveX combo boxes can be buggy; I'm wondering if anyone has found a way around the text appearance inconsistencies seen in these controls? e.g., sometimes the text is smaller, sometimes it's spaced wider, sometimes it appears at the top of the combo box sometimes in the center.

I put a screen shot of what I'm talking about in a word doc along with a control properties screen shot, All controls have the same properties except for the text property.

Hi all!

My first post in this forum - hope I'm abiding by the rules..

I have an ActiveX Combo Box with two options - FISH and CHIPS.
The linked cell is A2.

There are two numbers in cells A4 and A5 - e.g. 0.1234 and 5678.

In cell A7 is:
I want the format of cell A7 to be either e.g. 12.34% or 5,678, depending on which combo box selection is picked. 

I tried this using conditional formatting on A7, e.g.
 
RULE: =$A$2="FISH"    (formatted as %, 2dps)
RULE2: =$A$2<>"FISH" (formatted as number with thousand sep)
Now, when you change the combo from FISH to CHIPS, it changes the format. So far, so good. When you switch back to FISH, it does NOT change the format back again! If you select the cell and press enter, it will do - but it doesn't notice without manual intervention.

Any ideas? Obviously a simplified version of my actual use, but if I can make this simple version work, I'll roll out to the actual client piece!

Have tried a bit of VBA, e.g.
Private Sub ComboBox1_Change()
    Application.CalculateFullRebuild
End Sub                                                        

(does nothing)

Private Sub ComboBox1_Change()
    Worksheets("Sheet1").Range("A7").Refresh
End Sub

(run time error 438)
Tether end reached. Help please!

Thanks very much,

Adam

Hello, Been reading up on my problem and cannot find an answer.

- I have name ranges on a sheet named "Data"
- As an example one of the ranges is named "Ship_Name"
- On another sheet named "User_Entry" I have an ActiveX combo box named "Ship_Name"Combi" pointing to the named range stated abover.

Problem:
I would like the user to be able to type in a value to the combo box if the desired selection is not there, then when the combo box looses focus, i would like a prompt to ask the user if he'd like to add the entry to the list for future use.

Here is my current code:
Private Sub Ship_Name_Combi_LostFocus()

Dim Reply As Long
Dim New_Text As String
Const quote As String = """"
Dim Test_Var As Range

On Error Resume Next

New_Text = Ship_Name_Combi.Text
Test_Var = Sheets("Data").Range("Ship_Name").Cells("Ship_Name").Rows.Count + 1
    If New_Text = "" Then Exit Sub
        If WorksheetFunction.CountIf(Combi_Rng, New_Text) = 0 Then
            Reply = MsgBox("Add " & quote & New_Text & quote & " to list for future
use?", vbYesNo + vbQuestion)
            If Reply = vbYes Then
                Sheets("Data").Range("Ship_Name").Cells("Ship_Name").Rows.Count 1 = New_Text
            End If
        End If
End Sub
In the code above, everything works except the addition of the entry to the named list.

In addition, on a related problem, once I get this working, I'd like to convert it to a function because I will have several ActiveX Combo Box's on the User_Input sheet.
So far the function code would look like this:

Private Sub Ship_Name_Combi_LostFocus()

Call Append_List(Ship_Name_Combi.Text, Sheets("Data").Range("Ship_Name"))

End Sub

Sub Append_List(Combi_Val As String, Combi_Rng As Range)

Dim Reply As Long
Const quote As String = """"

On Error Resume Next

    If Combi_Val = "" Then Exit Sub
        If WorksheetFunction.CountIf(Combi_Rng, Combi_Val) = 0 Then
            Reply = MsgBox("Add " & quote & Combi_Val & quote & " to list for future
use?", vbYesNo + vbQuestion)
            If Reply = vbYes Then
                Sheets("Data").Range("Combi_Rng").Cells("Combi_Rng").Rows.Count 1, 1 =
Combi_Val
            End If
        End If
      
End Sub
Anyone that can help would be greatly appreciated.

Thank you!

Hello All,

I've searched the internet all day for a solution to this but I cannot find an answer. If anyone knows the proper syntax to do this I would be greatfull. In any case this is what I am trying to do conceptually. Keep in mind I am not using a form control but an ActiveX combo box control in excel 2007.

I want to code some vba for excel. Specifically I have several combo boxes in a spreadsheet that I want to populate with the same items lets say:

Item1
Item2
Item3

I want this to populate when the workbook opens. So far no problem. I attach the following in the Workbook_Activate routine and it works fine:

Sheet1.ComboBox1.AddItem "Item1"
Sheet1.ComboBox1.AddItem "Item2"
Sheet1.ComboBox1.AddItem "Item3"

Now I want to populate several (many really) combo boxes on this sheet with the same items.  Obviously this would work:

Sheet1.ComboBox1.AddItem "Item1"
Sheet1.ComboBox1.AddItem "Item2"
Sheet1.ComboBox1.AddItem "Item3"
Sheet1.ComboBox2.AddItem "Item1"
Sheet1.ComboBox2.AddItem "Item2"
Sheet1.ComboBox2.AddItem "Item3"

Just as clear this is hundreds of lines of code that can be done away with with a simple loop.  I can’t get a loop to
work.  In theory something like this is what I want to do.

Dim XLoopVar as Integer
For XLoopVar = 1 to 50
                Sheet1.ComboBox(XLoopVar).Additem “Item1”
Next XLoopVar
But this doesn’t work. The ComboBox(XLoopVar) portion is obviously wrong. But I can’t seem to find the correct syntax to do this anywhere. I find some VB examples but they don’t seem to work with the VBA in Excel and the forms examples don't seem to have the coresponding additem method with the activex control. Has anyone attempted this before and know of a way to do this?

Thanks,
Eric

I have my ActiveX combobox BackStyle set for transparent, and the ShoDropButtonWhen set to ...WhenFocus.

I click on the combo box and change the selected value. When I click out of the combobox, it does not go back to transparent and the drop button stays on the worksheet. Am I missing something?

Additionally, is there a macro or setting which will allow me to Tab and/or Enter out of a combo box, either to a blank cell or another combo box (or tab into a combo box for that matter).

Thanks!

How do you enter text directly into a combo box, without having to link it to a cell?:o

Hi,

I'm working on a forecasting model that is to be used by other people.

I've added two active x combo box controls, titled cboViewStartYear and cboViewEndYear.

Based on the range that the user chooses, I am trying to set up a macro that will go through the entire workbook and hide all columns that contain information outside of this range. I also am trying to set it up so that if a user changes the Start Year, the dropdown for the End Year automatically updates such that they cannot choose an End Year before the Start Year.

Unfortunately, this is where I am running into some problems. I am running a code for the cboViewStartYear_Change event and the cboViewEndYear_Change event. Both events call a sub routine entitled "ResizeViewRange" which goes through each worksheet and hides or unhides the columns based on the cbo value.

This is the code for the cboViewStartYear_Change event:

Code:
     Private Sub cboViewStartYear_Change()
Dim x As Integer
Dim y As Integer
Dim z As Integer
    x = cboViewStartYear.Value
    y = cboViewEndYear.Value
    z = x

        With cboViewEndYear
            .Clear
            Do While z < 2061
                .AddItem z
                z = z + 1
            Loop
            
            If x > y Then
                .Value = "2060"
            Else
                .Value = y
            End If
        End With
        
        ResizeViewRange

End Sub
This is the code for the cboViewEndYear_Change event:

Code:
 Private Sub cboViewEndYear_Change()
    ResizeViewRange
End Sub
The problem I run into occurs when running cboViewStartYear_Change.

When I get to:
With cboViewEndYear
.Clear

it triggers the cboViewEndYear_Change event. However, after the contents are cleared, cboviewendyear does not have a value and an error is returned.

Any thoughts on a fix or work around for this issue?

Thanks a lot,
Matt

..and I STILL can't get it to perform the action that I want. Here it is:

A simple ActiveX Combo Box control is placed in col A of a blank sheet. ListFillRange is a col of values elsewhere in the sheet, say Apples, Pears and Oranges in a range named ITEMS.
ACTION:
The Combo Box only is visible in COL A and when the user moves to any cell in COL A the droipdown list drops down(ComboBox1.dropdown)

PROBLEM TO SOLVE
When the user single clicks on any item OR presses ENTER,
1. that value is placed in the cell the Combo Box is in,
2. today's Date is typed into COL C,
3. the Word "Autodeb" is typed into COL D,
4. amd the Active Cell moves to COL E waiting for the user to enter a value.

When a value is entered in COL E and ENTER is pressed, the ActiveCell moves
1 row below in COL A and the Combo Box reappears and the drop down list drops down
waiting for the user to enter a second value.

The code I've written to this point does not work for every one of these actions all the time. It may perform this action for the first row only but then does not repeat the action for the second, third or any other subsequent rows. It must "start over", when completing the sequence of events each time, and I don't know how to complete the code.

Can anyone help me, MVP's or otherwise. I know it's possible for this to work, I just know it.

Thanks,
frustrated chazrab
CR

I put an ActiveX Combo Box (ComboBox1) control in cell A1. Cells A1:A100 in Column A contain a Data Validation list(named DESC). The ListFillRange in the Properties = DESC. So the Combo Box gets its values from the Data Validation list in these cells.

All I want to do is have the Combo Box appear every time a user clicks once in any cell in Column A, put the clicked value in the cell move the active cell to Column B of the same row and disappear every time the user moves out of column A. This should be very simple to do, but I'm having trouble with telling Excel to place the clicked value in the cell, move to the next column on the same row and disappear.

Could you please help me with this?

Thanks for ALL your help

chazrab
CR

I have some ActiveX combo boxes, and the text is not vertically centered in the boxes. Instead, it's low enough to chop off most of the descenders of letters that have them, such as g and y.

Can anything be done about this?

I am looking for an example.. Where a combo box has the data of an database (Excel File). So when I click one of the value from combo box it should fetech the required data from the databas (Another Excel) and show in the excel cell say from B5 to b30... Is this possible? If so how do i go about showing it?

PS: I am using two excel files. One the database and second GUI file.

Awaiting your response!

Thanks!

Excel 2003 on Windows XP.

I'm creating Control Toolbox combo boxes programmatically on Sheet1 (not on a userform) and also trying to set up a class to handle events from them. The combo boxes are being created okay, but the event handler isn't triggered when I click an item in one of the combo boxes.

Sheet1 contains 2 command buttons (CommandButton1 and CommandButton2).

Clicking CommandButton1 creates the combo boxes and should also assign the generic event handler to each combo box, but it doesn't. I have to click CommandButton2 to assign the event handlers separately and then everything works as expected.

Code:
'===== Sheet1 code =====

Option Explicit

Const numComboBoxes = 3
Dim ComboBoxes(numComboBoxes - 1) As New clsComboBox

Public Sub Create_ComboBoxes()

Dim objCB As OLEObject
Dim i As Integer
Dim arrData() As Variant

arrData = Array("AAA", "BBB", "CCC")

'Delete existing combo boxes

For Each objCB In ActiveSheet.OLEObjects
    If TypeName(objCB.Object) = "ComboBox" Then
        objCB.Delete
    End If
Next

For i = 1 To numComboBoxes

    ActiveSheet.Cells(1, i * 2 - 1).Select

    'Create a Control Toolbox (ActiveX) combo box

    Set objCB = Application.ActiveSheet.OLEObjects.Add( _
        ClassType:="Forms.ComboBox.1", _
        Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=50, Height:=20)

    objCB.Object.List = arrData
    objCB.Name = "myComboBox" & CStr(i)

    'Add combo box to array of class event handlers

    Set ComboBoxes(i - 1).clsComboBox = objCB.Object

Next

End Sub


Sub Setup_Event_Handlers()

Dim objCB As OLEObject
Dim i As Integer

i = 0
For Each objCB In ActiveSheet.OLEObjects
    If TypeOf objCB.Object Is MSForms.ComboBox Then
        Set ComboBoxes(i).clsComboBox = objCB.Object
        i = i + 1
    End If
Next

End Sub


Private Sub CommandButton1_Click()
    Create_ComboBoxes
End Sub


Private Sub CommandButton2_Click()
    Setup_Event_Handlers
End Sub
Code:
'===== Class module called clsComboBox =====

Option Explicit

Public WithEvents clsComboBox As MSForms.ComboBox

Private Sub clsComboBox_Change()
    MsgBox "Change event " & clsComboBox.Name & " " & clsComboBox.Value
End Sub
Changing CommandButton1_Click to:

Private Sub CommandButton1_Click()
Create_ComboBoxes
Setup_Event_Handlers
End Sub

Doesn't make any difference.

The only thing that works is clicking CommandButton2.

Another way is by using the following Worksheet_Activate subroutine in Sheet1, however this requires another sheet tab to be clicked and then the Sheet1 tab clicked to trigger it.

Private Sub Worksheet_Activate()
Setup_Event_Handlers
End Sub

I want to set up the generic event handlers for the combo boxes programmatically, without having to click CommandButton2 or worksheet tabs. Any ideas?

Thanks for any help!

Using Excel 2007. I am trying to create either Form Control Combo Boxes or
ActiveX Combo Boxes that will change the selectable options based on
selections made in previous combo boxes. I am having a hard time finding any
information that will work for me. Can someone tell me how to make 5 combo
boxes change available listed options when specific options are selected in
another combo box? Please? I've been working on this for a very long time
without success. I've tried using Data Validation, but my formulas are too
long for all the different possible combinations.

I am going to try to make this as clear as I can, as it is a little confusing
to me. Here goes:

I have an ActiveX combo box on my worksheet to select a Vendor. I have the
Column Count property set to 2, so that I can see the Vendor name (which is
in the first column of the fill range) and the vendor number (which is in the
second coulmn of the fill range).

I have the AutoWordSelect property set to True so that you can start typing
a Vendor name and the combo box jumps to the vendor starting with the letters
that you are typing. The MatchRequired property is set to True, ane the
MatchEntry property is set to 1-Match Entry Complete to ensure that the value
entered is valid.

I need the combo box to return the vendor number when the vendor name is
selected. I have achieved this by setting the text coulmn property to 2.
However, when I do this, the AutoWordSelect property now uses the vendor
number instead of the vendor name.

Here is exactly what I need:

Combo box is filled with the 2 columns of the vendor name and vendor number.
As you start typing the vendor name, it jumps to the vendor name that
matches what is being typed.
The combobox needs to be populated with the vendor number which is in coulmn
two fo the list fill range.

Have I confused anyone yet?

The comments of Nick Hodge were usefull, but I still can't get the drop down
list to show when using the Controls ActiveX Combo Box. It appears to be
working the wrong way round, with the entry in the combo list apearing in the
first linked cell.

I am trying to add an ActiveX combo box to my worksheet for all cells using a data validation list (drop down list). I found a VBA code online to copy and paste to make the box work. I can't make it work and unfortunately don't understand VBA enough to figure out the issue. I would like the combo box to work with all drop down lists on my worksheet.

Attached is a sample worksheet.

Thanks for your help!

I am going to try to make this as clear as I can, as it is a little confusing
to me. Here goes:

I have an ActiveX combo box on my worksheet to select a Vendor. I have the
Column Count property set to 2, so that I can see the Vendor name (which is
in the first column of the fill range) and the vendor number (which is in the
second coulmn of the fill range).

I have the AutoWordSelect property set to True so that you can start typing
a Vendor name and the combo box jumps to the vendor starting with the letters
that you are typing. The MatchRequired property is set to True, ane the
MatchEntry property is set to 1-Match Entry Complete to ensure that the value
entered is valid.

I need the combo box to return the vendor number when the vendor name is
selected. I have achieved this by setting the text coulmn property to 2.
However, when I do this, the AutoWordSelect property now uses the vendor
number instead of the vendor name.

Here is exactly what I need:

Combo box is filled with the 2 columns of the vendor name and vendor number.
As you start typing the vendor name, it jumps to the vendor name that
matches what is being typed.
The combobox needs to be populated with the vendor number which is in coulmn
two fo the list fill range.

Have I confused anyone yet?

Hi All,

Running in circles trying to get this to work.

I'm trying to populate a combo box with titles and a database key value using ado.

the code look like this
  With Worksheets("Nonrecurring Costs").Cbo_NRE_LevelSelect
                .AddItem rs.Fields("Title").Value
                '.List(.ListCount - 1, 1) = CInt(rs.Fields("LevelID").Value)
                '.List = CInt(rs.Fields("LevelID").Value)
                '.Value = CInt(rs.Fields("LevelID").Value)
            End With
The additem works, but I get an error if I try to store both the title and the database key, ie:
and any of these variations also cause an error
                 '.List(.ListCount - 1, 1) = CInt(rs.Fields("LevelID").Value)
                '.List = CInt(rs.Fields("LevelID").Value)
                '.Value = CInt(rs.Fields("LevelID").Value)
I swear I was able to store text values and lookup values in combo boxes in the past.

basically what will happen is when the user select a new title, an event will fire off, I'll lookup the key value in the combo and and perform the necessary tasks.

Any ideas what I'm doing wrong?

thanks

I'm trying to set up a dropdown menu that choose a site number (hospitals) and the cell next to the linked cell does a vlookup from a list (same list that the site numbers come from). The problem is that for every value it returns #N/A because the link cell is converted to a text value and not a number.

How do I set this link cell to never be text?

The strange thing is that it works perfectly with the other dropdown aka "List Box" (the one with scroll arrows). But I like the functionality of the "Combo box" because it compresses to one cell size after selecting your value.

Two Quetsions,

#1, How do I have the box to the right side of the cell with the arrow in it that allows the pull down menu appear, not show when printing out the page? With a User form combo box the pull down box disappears when not clicked on the cell in question. Ca the same be done for the ActiveX combo box?

#2, How do I have the text to automatically adjust in size to fit the cell? Not have the combo box adjust the cells size to fit the Text. Again, with the user form combo box you can have the text go bigger or smaller automatically if the text string is longer or shorter.

Hi Everyone

I have just started on my Macro experience with Excel.

Currently I am working on standardising a Daily Flight Record (DFR).
The current DFR have been on MS Word, where contents have not been filled out in any particular format. This possesses many issues for our Finance team, resulting in delayed reports and errors in checking against invoices from air-traffic authorities.

My simple answer was "put it in Excel".
But as I go along to format everything to present it similar to how it was to MS Word, working towards the controls to establish it as a standardised format is proving to be difficult.

My current problem is to provide the body of the table with dropdown lists to provide controlled options.
Vertical axis lists Flight numbers
Horizontal axis lists Ports for various sectors.

Here's a madeup example:
Flight number: QFabcd Sectors flown: BNE-SYD-MEL-ADL
Flight number: QFdcba Sectors flown: ADL-MEL-SYD-BNE
Flight number: QF0bc0 Sectors flown: SYD-MEL
Flight number: QF0cb0 Sectors flown: MEL-SYD

Please refer to attached for the spreadsheet on the example.
The standard routes are displayed by vlookup when entering Flight No.
Please note that in reality the sectors flown changes from the standard routes due to various reasons.
This is reflected on the second sector of QF-0cb0 flight.

At the moment I have Data Validation to provide the dropdown list.
But, feedback on the first draft, the clerks have indicated that they are not comfortable with scrolling down on a very long list, especially for flight numbers and sectors.

From trial and error, I have worked out that Combo box from ActiveX Control is most suitable. This provides auto-fill and also narrows down the option as you type.
Eg
Type A and it displays anything that starts with A - ADL and ALS
Then Type D and it displays anything that starts with AD – ADL

So the issue now is, how to apply this to every cell in the DFR table?

Please, can anyone help?

Regards

David


No luck finding an answer? You could always try Google.