Building a List with Combo Box

[The_Taffinch]

Hi all,

Wonder if someone cvan point in the right direction.

I ma wanting to create a sheet where the user can add items to a quote/list using by selecting items from a drop down list. I have set up a combo box to output a value based on items from a pre-set list but I am struggling to work out the ouptut side of things.

I would think it is a case of incrementing the LinkedCell property of the combo box somehow so that the output is generated to successive lines...but I can't work out how to do this. I have tried using an INDIRECT expression to increment the row ref in the combo properties but Excel won't accept this. I think it wants a straight reference.

Any ideas? I have only very basic VBA skills so any 'scripting'-based answers would have to be written for dummies!

 

[HippoCampus]

Afternoon

Have a look at the Contextures website http://www.contextures.com/xlDataVal11.html

They have some good examples and sample worksheets there

Cheers

 

[Norie]

What type of combobox is it and where is it located?

 

[The_Taffinch]

Hi,

Thanks for this. None of the tips examples on site you suggested solves my problem but I did find that perhaps you can adjust the Linked Cell reference in the VBA code. One of the examples cites:

.LinkedCell = Target.Address

I want the LinkedCell reference to be:

=INDIRECT("G",&$H$10) where H10 is an number which will effectively increment the row so that the output from the combo box is entered into successive rows of column G.

However, I'm not sure how I enter this into the VBA code for the combo box. I can get into the VBA console for the combo box but I am not sure what notation to use.

 

[The_Taffinch]

I'm not sure what the specific type of combo box it is...I just selected combo box from the Control toolbox toolbar.

For now it's just sitting in a single test worksheet and pulling values from that sheet only.

 

[Norie]

Don't use LinkedCell.

Double click on the combobox.

That should take you to the VBE where you will see this, or similar.

Private Sub ComboBox1_Change()
     
End Sub

This is the code 'stub' for the combobox and will be triggered whenever the value in the combobox is changed. ie when a value is picked from it.

Any code for putting the value from the combobox on a worksheet should go here.

What exactly that code is depends on where you want the value(s) to go.

 

[The_Taffinch]

Thanks.

So can I just add...

.LinkedCell = ""G",&$H$10"

...between the header and footer of that bit in the VBE?

 

[Norie]

Well you could if you wanted but I wouldn't recommend it.

Let's say you want the value from the combobox to go into the next blank cell in column G.

You could try something like this.

Private Sub ComboBox1_Change()
Dim NextRow As Long
    If ComboBox1.ListIndex = -1 Then Exit Sub ' stop code if no value selected in combobox
    
    NextRow = Range("G" & Rows.Count).End(xlUp).Row + 1 ' find row for next empty row
    
    
    Range("G" & NextRow) = ComboBox1.Value
    
End Sub

 

[The_Taffinch]

It works! Brilliant thanks!