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?
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?
Re: Add Item To Combobox On Sheet
Try this
[vba] With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=69.75, Top:=56.25, _
Width:=98.25, Height:=20.25)
With .Object
.AddItem "One"
.AddItem "Two"
.AddItem "Three"
End With
End With[/vba]
Re: Add Item To Combobox On Sheet
You could take the easy way out and use Data Validation and get your ComboBox when the cell is selected without the code.
You'd first have a list of what you want in the sheet. Select the cell in which you want the cell to appear. Then, go to Data -> Validation -> Settings -> List. Then select the cells where your list is at. After clicking OK, whenever you select the cell, you'll be greeted with a dropdown box in the list.
There is a shortcoming in that another sheet may not reference this. However, if the sheet isn't too terribly complex, you can use the indirect function to override that concern. (I say not "too complex" because I understand the indirect function, as well as arrays and vlookups, may use some system resources if you've got a lot of them.)
Just a thought.
Quote from mikezangI 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?
Re: Add Item To Combobox On Sheet
Quote from Andy PopeDisplay MoreTry this
[vba] With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=69.75, Top:=56.25, _
Width:=98.25, Height:=20.25)
With .Object
.AddItem "One"
.AddItem "Two"
.AddItem "Three"
End With
End With[/vba]
Thansk a lot. My next question is how to handle change event, the calculation will be done automatically when I select a new item in combobox, I don't know how to add the event handler, although I knew how to handle for combobox inside userform.
Re: Add Item To Combobox On Sheet
I think you have to write the event code to the sheet object.
This is sheet level event code, but should give you an idea.
http://www.ozgrid.com/VBA/delete-sheet-code.htm
Re: Add Item To Combobox On Sheet
But I have to handle combobox events, not sheet event, how can I do it?
When I add a new combobox in dynamic, theie will be a new event handler added, I am not sure how I can do it, can you give me some detail codes?
Re: Add Item To Combobox On Sheet
It was an *example* of the sort of coding you would need.
You need to use the extensibility library to write code to the sheet object.
Re: Add Item To Combobox On Sheet
I checked that sample, but I am not sure what I can do, it is too complex to me, myabe:)
Re: Add Item To Combobox On Sheet
When I use StartLine = .ProcStartLine(vName, vbext_pk_Proc), I got no vbext_pk_Proc error, why? I am using Excel 2000, may I have to use later 2002?
Re: Add Item To Combobox On Sheet
Did you include reference to extensibility library?
Rather than adding controls and events on the fly can you add them manually then control whether they are visible or not.
Perhaps if you explain your problem we can suggest alternative approaches.
Re: Add Item To Combobox On Sheet
Well, Here is my way to handle combobox on fly as below:
The AddCtrl, DeleteCtrl, AddProc is ok, but I can't compile DeleteProc, can you helpme?
Sub DeleteCtrl(ByVal vCell As Range)
On Error Resume Next
ActiveSheet.OLEObjects(Chr(64 + vCell.Row) & vCell.Column).Delete
On Error GoTo 0
End Sub
Sub AddCtrl( _
ByVal vCell As Range, _
ByVal vItems As Variant, _
ByVal vIndex As Integer _
)
Dim iLeft As Integer, iTop As Integer, iWidth As Integer, iHeight As Integer
Dim oCombo As OLEObject, vItem As Variant
Call DeleteCtrl(Selection)
vCell.Select
iLeft = ActiveCell.Left
iTop = ActiveCell.Top
iWidth = ActiveCell.Width
iHeight = ActiveCell.Height
Set oCombo = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1")
With oCombo
.Name = Chr(64 + vCell.Column) & vCell.Row
.Left = iLeft
.Top = iTop
.Width = iWidth + 2
.Height = iHeight
With .Object
For Each vItem In vItems
.AddItem vItem
Next
.Font.Size = 9
.ListIndex = vIndex
End With
End With
Call AddProc(vCell.Row, vCell.Column)
Cells(vCell.Row, 2).Select
End Sub
Sub AddProc( _
ByVal vRow As Integer, _
ByVal vCol As Integer _
)
Dim ModEvent As Object 'CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim Ap As String 'Apostrophe
Dim Tabs As String 'Tab
Dim LF As String 'Line feed or carriage return
Dim sSheet As String, iRow As Integer, iColBid As Integer, sColBid As String
Dim sName As String
sSheet = ActiveSheet.Name
iRow = Selection.Row
iColBid = GetColumn(sSheet, "Bid")
Ap = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
sName = Chr(64 + iColBid) & iRow
'Call DeleteProc(sName)
SubName = "Private Sub " & sName & "_Change()" & LF
Proc = Tabs & "Cells(" & iRow & ", " & iColBid & ") = " & sName & ".Value" & LF
EndS = "End Sub"
Set ModEvent = ActiveWorkbook.VBProject.VBComponents("Sheet14").CodeModule
With ModEvent
LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With
End Sub
Sub DeleteProc(ByVal vName As String)
Dim VBCodeMod As Object
Dim StartLine As Long
Dim HowManyLines As Long
Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("Sheet14").CodeModule
With VBCodeMod
StartLine = .ProcStartLine(vName, vbext_pk_Proc)
HowManyLines = .ProcCountLines(vName, vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With
End Sub
Display More
Re: Add Item To Combobox On Sheet
Complies for me in xl2000 if I include a reference to the extensibility library.
VBE Tools > References > Browse for library.
Re: Add Item To Combobox On Sheet
There are a lot of libraries, but I can't find extensibility library, waht is the exact library name?
Re: Add Item To Combobox On Sheet
Microsoft Visual Basic for Applications Extensibility 5.3
Re: Add Item To Combobox On Sheet
Thank you very mcuh.
Re: Add Item To Combobox On Sheet
I found new problem. DeleteProc dosn't work, the event handler does still exists. do you have any idea?
Sub DeleteProc( _
ByVal vRow As Integer, _
ByVal vCol As Integer _
)
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
Dim sName As String
sName = Chr(64 + vCol) & vRow
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Sheet14").CodeModule
On Error Resume Next
With VBCodeMod
StartLine = .ProcStartLine(sName, vbext_pk_Proc)
HowManyLines = .ProcCountLines(sName, vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With
On Error GoTo 0
End Sub
Display More
Re: Add Item To Combobox On Sheet
Is all you want is the code in the AddProc routine you can forget about the need to generate event code and just use the builtin property LinkedCell.
[vba] oCombo.LinkedCell = vCell.Address[/vba]
Re: Add Item To Combobox On Sheet
Thanks for your idea.
But I need to recalculate when I change the item in comboBox, I tried your method, it looks like that no any recalculation when I change item, is it true?
Re: Add Item To Combobox On Sheet
I found linkedcell will change the value in cell, but the event sheetchange doesn't happen, I need that event to recalculate, any idea else?
Re: Add Item To Combobox On Sheet
If the combobox has a linked cell of B2 you could place a formula in C2 which is
=B2
Then when the combobox value changes C2 recalculates and the Worksheet_Calculate event occurs.
Don’t have an account yet? Register yourself now and be a part of our community!