CODEWITHSUNDEEP
vbaexcel
David
David

Reputation: 1

VBA: place selection from combo box into a specific cell in a spreadsheet

I would like to know what commands are needed to be able to select a value from a combo box and have it inserted into a specific cell in excel using VBA. I would also need it to overwrite what is in that cell if another selection is made from that combo box. Thank you in advance.

Upvotes: 0

Views: 9329

Answers (3)

michwalk
michwalk

Reputation: 313

This code creates a list of fruits for a combobox and places the value in cell A1. You'll need to create a Userform with a combobox.

Private Sub ComboBox1_Change()
    Range("A1").Value = ComboBox1.Value
End Sub

Sub Userform_Initialize()

    With Me.ComboBox1
        .AddItem "Apple"
        .AddItem "Banana"
        .AddItem "Orange"
    End With

End Sub

Upvotes: 1

MutjayLee
MutjayLee

Reputation: 568

below will change the value of the combobox

ComboBox1.value = 100

below will get the value to the cell

range("A1") = ComboBox1.Value

You can also set combobox linked cell by

  1. Turn on design mode.

  2. Right click on combo box and select properties

  3. find the row that says LinkedCell

  4. type in the range you want to change automatically whenever combobox is changed

When you type in the LinkedCell, just type A1. Not Range("A1") or "A1"

Upvotes: 1

Dave
Dave

Reputation: 4356

You need a reference that points to the combo box and retrieves its value, then sets the cell range in Excel to that value. This will differ whether the combo box is on a form or on a worksheet:

In the form's or worksheet's code, you can add a Change event for the combobox and code it to insert the data into your worksheet cell reference (example will set A1 on Sheet1 to the value of the combobox whenever the combobox value is changed).

Public Sub ComboBox1_Change()
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = Me.ComboBox1.Value
End Sub

Also, I think you might need a better VBA book :)

Upvotes: 1

Related Questions