Free Microsoft Excel 2013 Quick Reference

VBA : Adding Items to ActiveX ComboBox?

Does anyone in here know how to add items to a combobox? I have a couple of macros written and ready to go but, I am not to familiar with ActiveX. After I have entered the terms how do I link the macros to run on the selected item?


Hi everybody,

I have a form in VBA containing a ComboBox >> what I want is adding items to that ComboBox .. The Items is a list availble in range (A1:A50).

Can you please help me in that case.

Thank you soooo much,

Hi,

I am unable to add items to a combobox in powerpoint. I need to add the items by default. means when I start the presentation ,the combo box will have the all items (Ex: a, b, c these 3 items should be in the combobox). could anybody please help me?

thanks in advance

Regards
Gopal

i had a userform in which i use a combobox for selecting certain values.
Is there any way for creating dynamic lists in combobox.

Right now, i m using the rowsource property to get the items into the combobox.i wanted to add & delete items to the combobox dynamically.
if i want to add "ITEM 6" To the list how would i do this.
if i want to delete "ITEM 4" From the list how would i do this.

Using vb comboBox is it possible to add the same items to numerous comboBox on the same sheet?
I have been trying the following....

Dim mycntrl As OLEObject
Dim sht As Worksheet
Set sht = ActiveSheet

For Each mycntrl In sht.OLEObjects

mycntrl.addItem "item1"

Next mycntrl

However I noticed that the addItem method is not available when trying this, so how would you add an item in a loop?

thank you in advance for any help

Hi all,

I have created an msoControlPopup menu, but I don't know, how can I adding items to it.

Does anybody help me?
Thanks

Hello,

My question is about adding items to a list box.

I want to add items to a list box from an array. Not a range on a worksheet. How do i do this?

So far I've created a form called frmTest. I've made a list box on the form called lboChoices. I've clicked on the lboChoices and have opened up the sub code for the List Box.

My array is called strArray. It's been sized to hold 5 values. One value for each element. The values are "1" "2" "3" "4" "5", respectively.

Private Sub lboChoices_Click()
   ' what goes here? 
End Sub

Thanks for the help! I can't figure out the way to use the additem method even with looking at the excel Help for some reason.

Looking forward to understanding how this works!

Hello.

I am trying to add a list to an ActiveX ComboBox on the sheet itself. I am
able to do this using a Userform:

For Each Item In MyList
MyForm.MyComboBox.AddItem Item
Next Item

This works great... for Userforms. I'm having trouble with the correct
syntax for a ComboBox residing on the sheet, and not in a Userform. Can
anyone out there help?

Thanks.

Dan

Hello,

I have a look up table, which I use to populate an activeX combobox, which is embedded in a worksheet. On another worksheet I have a lookup table. The table in the seperate work sheet "tables" is similar to:

--------A--------- B------- C
1--- Title 1------ 1------ 100
2--- Title 2 ------2 -------70
3--- Title 3 ------6 -------60
4--- Title 4 ------2 -------20

The code that fetches the items into the ComboBox menu is


	VB:
	
 
Private Sub ComboBox1_Click() 
    Sheet7.ComboBox3.ListFillRange = ThisWorkbook.Worksheets("Tables").Range("A1:A4").Address(external:=True) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This populates the ComboBox with 4 items, Title 1, Title 2, Title 3, Title 4. What I would like to do is have values in columns B and C populate two activex text boxes depending on the item choosen. So if item 1, "Title 1", was choosen, then values B1 and C1 would populate textbox 1 and textbox 2 respectively. I cannot use 4 if statments, because the real table is 163 rows, which would require 163 if statments.

If item 1 is choosen, then "Title 1" appears in the Value field as well as the text field in the comboxBox properties. Is there a way of have the value field have the corresponding choosen row?

Would really appreciate some help on this, thank you.

I have a two column ActiveX ComboBox on a worksheet. The first column (column 0) contains numbers and is zero width. The second column contains the descriptive text the user sees. The list contents are loaded in VBA and are showing correctly. The bound column is set to 1 (i.e. returning data from the hidden column 0. The LinkedCell property of the control is set to 'MySheet!$E$2'. When I update the combo interactively it happily updates the value in MySheet!E2 to the appropriate numeric value from the hidden column.

My problem comes when I am trying to set the value of this ComboBox (and hence the value in MySheet!$E$2) by using VBA. I was expecting to be able to set the .Value property to the correct numeric value but this doesn't work (depending on where I do it from the assignment is either ignored or causes a RunTime error telling me the value is invalid for the property). So in code:


	VB:
	
 MSForms.ComboBox 
Dim i As Long 
Set oCombo = ActiveWorkbook.Sheets("SheetContainingCombo").cboStuff 
 
 'The following assignment fails
oCombo.Value = 5 
 
 'But this longwinded method of setting the value works
For i = 0 To oCombo.ListCount 
    If oCombo.Column(0, i) = 5 Then 
        oCombo.ListIndex = i 
        Exit For 
    End If 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Why can't I just set the .Value property directly like I would in say MS Access?

Anyone answering please treat me as a seasoned VBA coder for MS Access (roughly 10 years) but relatively new to the Excel object model.

Thanks

Robert

Hi there. How do I add the column heads (e.g. A1, B1, C1....) as items to a combobox or listbox if they are not empty?

Cheers.

I want to add combobox to my sheet in vba code, then I hope I can add some items to this combobox.

I knew how to add combobox to sheet, but I couldn't find any information about how to add item to that combobox, does anyone help me?

The Situation: I am trying to loop through a range to fill a listbo
based on the value of another combobox (imagine selecting state an
having another box populate with only information specific to th
selected state). I need to add multiple columns of information to th
box if the "row qualifies". I can get my items to add, but also ge
blank lines where the row does not qualify. I need to eliminate th
blanks.

I also would like to use a VARIABLE instead of a constant for the # o
loops. Currently, the way it is set up is using an array whic
requires a constant ("66"). I have added a line to generate th
desired variable ("PackCount").

Any help would be great. Below is my code:

Sub PopulateBox()

Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount
Application.WorksheetFunction.CountA(PackagesAvail able.Range("A:A"))
Dim i As Integer

ListBox1.Clear

Dim Data(1 To 66, 1 To 2)

On Error Resume Next
For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 1) = PackagesAvailable.Cells(i, 2).Value
End If
Next i

For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 2) = PackagesAvailable.Cells(i, 3).Value

End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Su

--
Message posted from http://www.ExcelForum.com

Hi All!

For my first post . . . I NEED HELP!

I would like for a user to be able to click a button, a userform open
and the user can input data. This new data will be added to
combobox.

I have the userform and botton covered. I need help with the dynami
code for adding new data to the combobox!

Any ideas?

(ie: currently, this combobox has 4 items. The user wants to ad
another item. They click a button, input data into the userform. No
the combobox has 5 items

--
Message posted from http://www.ExcelForum.com

hi, i want to know the procedure of adding and deleting the items to a combobox or list box dynamically.

I am using the following code to add a menu item, "Top of Sheet", to the right click shortcut menu that comes up when you right click on a cell in an Excel worksheet:


	VB:
	
 CommandBarControl 
 
Set NewItem = CommandBars("Cell").Controls.Add 
With NewItem 
    .Caption = "Top of Sheet" 
    .OnAction = "TopOfSheet" 
    .BeginGroup = True 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I added this to the Worksheet_Activate subroutine for the sheet on which I would like the menu addition. The funny thing is that it never gets added when I am in the workbook in which I would like this menu addition. When I switch to another workbook, however, the new item is added. My question is this:

Do I have some kind of wrong setting that is preventing the addition to the shortcut menu on the original workbook? I appreciate any help on this, thanks!

Good morning everyone,

I hope you don't mind my posting two separate and completely unrelated questions in the same topic... I seem to remember seeing posts of a similar nature in answer to both questions, but I can't seem to locate them. Anyways, here goes.

First is regarding adding items to a combobox. What I am wanting to do is design a full accounting and inventory management system in Excel. Part of this will be the inventory module. The first combobox on my form will request the item category. This determines what item codes will be loaded into the item code combobox. This is all easy to accomplish, but here is where I run into problems. How do I get the range to load up to, but not go past each blank row? Basically, there will be a blank row that separates each category. Also, let's say that I have the category names in column A. The first category has 120 items, the second has 128 items, so on so forth. If row 1 is my primary header line, then the first category name will be in A2, the second in A123. With the source for the category combobox being column A, how do I strictly just get the category names in the combobox instead of Category1 followed by 120 blank lines, then Category2 followed by 128 blank lines, etc.? Hope this first question makes sense and is possible to accomplish.

For my second question, it is for creating new controls on a userform using VBA. Basically, and I will use my department's collection system as the example for this post. Let say that Customer XYZ has a total of 3 returned items. One of the textboxes on the userform will request how many RI's the customer has. Based on this, I want my code to be set up so that it automatically resizes the form, inserts the correct number of additional textboxes, assigns each new textbox a name to keep them congruent, and rearranges the tab index of all controls on the form so that proper tabbing order will be maintained. I know this all is possible, although I have the feeling that this is something that is rather complex.

Thanks to all for any and all help rendered.

Hello,

I have created a new CombobBox using:


	VB:
	
, Left:=336, Top:=26.25, Width:=96, Height:=14.5).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The ComoboBox is named ComboBox2 in excel.

With previous ComboBoxes I have created, I have based them on rows of data in one of the worksheets. However, in producing the latest ComoboBox the data that I want to list (vertically) in the ComboBox is stored in a single row of data (in ten coloumns - horrizontally).

How do I base the ComboBox on a row of data? I have tried altering the ComboBox properties but it lists the data in one row of ten columns rather than one column of ten rows. Is there a way to change the properties of the ComoboBox?

Alternatively I could write some code as follows:


	VB:
	
 ComboBox2 
    .AddItem "1" 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have also tried:


	VB:
	
 ActiveSheet.ShapesComboBox2 
    .AddItem "1" 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If the above code worked I would not mind this alternative however I keep getting the error messages:

"Object doesn't support this property or method"
"Object required"

What am I doing wrong?

Many thanks,

Phil

Hi all,

I'm trying to add an item to the right click menu, and struggling!

I've followed the code (i.e. copy any pasted) from both the following sites

http://www.vbaexpress.com/kb/getarticle.php?kb_id=437

http://www.ozgrid.com/VBA/right-click.htm

But it doesn't seem to be working.

When I change the command bar from "Cell" to "Tools" it adds the relevant pop up / buttons to the Tools menu, but I can't figure out why it won't add items to the right click menu (I've also tried the row and column menus and can't seem to add to those either)!

Any ideas?

Will post again as I do not see my post.

The Situation: I need to add items (mulitcolumn) to a listbox based o
the value of another combobox. Basically, the user will select th
state, and then based on the state--the listbox will populate.

The problem is 2 fold- first-I can get the items to add, but it als
adds blank lines. I do not want the blank lines-It basically adds
blank line if the value does not match or adds the data if it does.

The second part is that I have to use a constant ("66") rather than
variable ("PackCount") due to the way I structured the code. I'd lik
to use the variable of PACKCOUNT rather than 66 to determine the numbe
of loops for flexability.

Any help would be greatly appreciated.

My code is as follows:

Sub PopulateBox()

Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount
Application.WorksheetFunction.CountA(PackagesAvail able.Range("A:A"))
Dim i As Integer

ListBox1.Clear

Dim Data(1 To 66, 1 To 2)

On Error Resume Next
For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 1) = PackagesAvailable.Cells(i, 2).Value
End If
Next i

For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 2) = PackagesAvailable.Cells(i, 3).Value

End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Su

--
Message posted from http://www.ExcelForum.com

I have values in some cells in Range A1:A100. What is the code I would use
to add each value to combobox1 only if that value isnt already located above
where it currently is in the loop.

For instance,
Lets say the first 5 values it comes across are all different, then it hits
the 6th value. Lets say the 3rd value matches the 6th, since that value is
already in the list, then the code either skips the 6th value, OR adds and
then removes it, OR removes the 3rd value and adds the 6th. No matter which
of the 3 logics you use, it will still prevent duplicate values from being
added to the combobox.

Is this a possible method or no?

Thanx

Todd Huttenstine

people i cant add items to the combobox,

i have sheet.name = s1
and on the sheet i have control placed combobox,
combobox name is combo1

in vb editor i write this text

worksheets("s1").shapes.item("combo1").additem "test"

but vb gives me error like = cant find object ???

how to correctly work with sheet controls from vb ?

thank

--
Message posted from http://www.ExcelForum.com

Hi,

I am trying to add a range of items to a combobox on a userform and the macro works fine except when the range is only one cell (ie one item long).

How do I make the macro accommodate a list that can be one item long? Or even zero items (which of course would tell the use that no items are available to choose from).

Here is my code now:

Thanks,

Lawrence

Howdy. i have the code for adding items to the combobox based on the combobox text though would like to remove duplucated items

This is what i have (thanks to mikerickson)
Dim oneCell             As Range
Dim vaItems             As Variant
Dim i                   As Long
Dim j                   As Long
Dim vTemp               As Variant
Dim rSource             As Range

    With Sheet1
        Set rSource = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With

    With Combobox_List_Search.ComboBox1
        .Clear
        Rem fill with matches from the list
        'If .Text <> vbNullString Then
    For Each oneCell In rSource
            If oneCell.text Like .text & "*" Then .AddItem oneCell.text
        Next oneCell
            
        'Store Combobox1 items in a variant array
        vaItems = Combobox_List_Search.ComboBox1.List
        ' sort the array
           For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
            For j = i + 1 To UBound(vaItems, 1)
               If vaItems(i, 0) > vaItems(j, 0) Then
                   vTemp = vaItems(i, 0)
                    vaItems(i, 0) = vaItems(j, 0)
                    vaItems(j, 0) = vTemp
                End If
            Next j
        Next i
End With
Combobox_List_Search.ComboBox1.DropDown
End Sub
Ive found this and cont figure out how to merge it in
Sub x()

Dim vData, r As Long

vData = Range("A2", Range("A2").End(xlDown)).Value
Sheet1.ComboBox1.Clear
With CreateObject("Scripting.Dictionary")
    For r = 1 To UBound(vData, 1)
        If Not .exists(vData(r, 1)) Then
            .Add vData(r, 1), Nothing
            Sheet1.ComboBox1.AddItem vData(r, 1)
        End If
    Next r
End With

End Sub
Cheers

I am in the process of adding items to a search string that all have a common result i.e that Me.Checkbox2 = True if the condition is met. Is there an easier way of adding these items i.e "G1009","E1001","E1026" ect ect rather then typing the same code again and again with just a different sString?

sString =
"G1009"
    Set rFind = ws.Range("A1:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Find(what:=sString)
        If Not rFind Is Nothing Then
            Me.CheckBox2 = True
            End If
            
    sString = "E1001"
    Set rFind = ws.Range("A1:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Find(what:=sString)
        If Not rFind Is Nothing Then
            Me.CheckBox2 = True
            End If
            
    sString = "E1026"
    Set rFind = ws.Range("A1:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Find(what:=sString)
        If Not rFind Is Nothing Then
            Me.CheckBox2 = True
            End If