How to copy a ActiveX combobox from one cell to another in vba - Urgent, please help!

**smandeh** · 08-24-2013, 03:33 PM

I'm new to VBA but business requirements demand that I implement this.

I have a worksheet with a combobox1 in row1 cell - say cell B1 with some static data. For every time a user enters a value in Row2 - say A2 - , I would like the combo box in B1 to be copied to B2. I don't want the user to copy and paste for each time he/she enters data in each row. Likewise, when the user enters data in Row3 - say A3, I would like the combo box in B1 to be copied to B3

Also, if the user removes the data from row A2, I would like the corresponding combo box in B2 to be deleted. How do I accomplish this? And how do I access the value for these combo box control in VBA?

I'm putting the code in Worksheet_Change method.

For example:

Column A B C D
Row 1 combobox1 control
2 combobox1 control

**AlphaFrog** · 08-24-2013, 07:13 PM

Deleted. Misread the question.

**AlphaFrog** · 08-24-2013, 07:49 PM

Try something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range, cboMain  As ComboBox
    
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
        Set cboMain = Me.ComboBox1
        Application.ScreenUpdating = False
        For Each cell In Intersect(Range("A:A"), Target)
            If IsEmpty(cell) Then
                On Error Resume Next
                    Me.OLEObjects("cbo" & cell.Offset(, 1).Address(0, 0)).Delete
                On Error GoTo 0
            Else
                With Me.OLEObjects.Add(ClassType:="Forms.Combobox.1", _
                                       Left:=cell.Offset(, 1).Left, _
                                       Top:=cell.Offset(, 1).Top, _
                                       Width:=cell.Offset(, 1).Width, _
                                       Height:=cell.Offset(, 1).Height)
                    .Name = "cbo" & cell.Offset(, 1).Address(0, 0)
                    .ListFillRange = cboMain.ListFillRange
                End With
            End If
        Next cell
        Application.ScreenUpdating = True
    End If
End Sub

Depending on what you want to achieve, this may be easier to implement with Form-type comboboxes instead.

**smandeh** · 08-24-2013, 08:05 PM

Thanks for your response. Your solution is partially working. It's inserting the control in B1 when A1 is populated. But when I delete the cell in A1, the corresponding combo box in B1 is not deleted.

I can switch to Form type combo box. In this case, how do I implement?

**AlphaFrog** · 08-24-2013, 09:11 PM

The code worded for me.

What's the point of all this? It could matter. What's in the comboboxes. What, if anything, should happen when a selection is made in the comboboxes.

**smandeh** · 08-24-2013, 09:45 PM

AlphaFrog,

I explained what I'm trying to accomplish. Let me try again.

I have a worksheet where the user will enter some data. The third row contains a combo box at C3 with some values. The user will enter some info in A3, B3, and probably select one of the values from the combo box.

The user may decide to enter more information at the fourth row, fifth row, and so on. But in doing so, I don't want the user to manually copy the combo box from the third row at C3 to subsequent rows. So, I would like to auto populate the C cells - C4, C5, C6, and so with the combo box. If the user decides to delete the data at A4, its corresponding C4 combo box should also be deleted.

How to copy a ActiveX combobox from one cell to another in vba - Urgent, please help!

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to copy a ActiveX combobox from one cell to another in vba - Urgent, please help!

Re: How to copy a ActiveX combobox from one cell to another in vba - Urgent, please help!

Re: How to copy a ActiveX combobox from one cell to another in vba - Urgent, please help!

Re: How to copy a ActiveX combobox from one cell to another in vba - Urgent, please help!

Re: How to copy a ActiveX combobox from one cell to another in vba - Urgent, please help!

Re: How to copy a ActiveX combobox from one cell to another in vba - Urgent, please help!

Thread Information

Users Browsing this Thread

Similar Threads

Excel 2007 ActiveX Combobox - LinkedCell automatically change w/ copy/paste

ActiveX ComboBox :: How to setup range & cell link

[SOLVED] ComboBox Activex Linked Cell displaying numbers

Set ComboBox (ActiveX Control) value to cell

Copy and Paste to Worksheet using Combobox (ActiveX)

Bookmarks

Bookmarks

Posting Permissions