Increment ComboBox Linked Cel

Microsoft Excel 2003 (full product)
October 12, 2009 at 12:57:18
Specs: Windows Vista
I am trying to incorporate a dropdown selection list from which I can choose a product to populate an invoicing application I have been developing over the years.

having tried various ways without much success, I decided to try a difefrent route, whcih works quite well up to a point and now I am stuck again. Hence seeking your wisdom and experience :-)

I have created a combo box pulling rows of data from a woksheet that has product code and product title. Clicking on a particulat product code places the value in a cell.Using lookup functions it then pulls price and other information realted to that product. It works fine, although I have some other aspects of it which I will try and sort later. Like the combox is in a cell which is part of the invoice that gets printed. I set the property so that it does not print, but it would be aesthetically much better if I could click somewhere on the page , open up a a new window where I do my slection, and once clicked, close that window.

Anyway, back to my immediate problem. I need to repeat the selection process from the Combobox, for each line of the invoice ( some 10 lines) on each occasion droping the product id value in a different cell. ie D20, D21, D22, D30. Is there a way of doing that? I am using the linkedcell function of the Combobox property. and it works well for the first cell that the combo box is linked to. Is there a way of using a VBA incremental function to increase d20 to d30 step by step?

Thanks for any help.

Talât :-)

See More: Increment ComboBox Linked Cel

Report •

October 12, 2009 at 13:34:10
I am assuming that once the ComboBox puts the value in the cell, some sort of calculation takes place, such as the VLOOKUP. The reason I say this is because I am using the Calculation event to trigger this code. There may be other ways to do this, but this was just a first shot to see if it gets you close to what you want.

First, create a Named Range For D20. In my example I used NewLinkedCell.

Then, use this Name in the "Cell Link" field in your ComboBox Control tab.

Finally, add this code to the Sheet module where NewLinkedCell resides:

Private Sub Worksheet_Calculate()
 Application.EnableEvents = False
  For nxtRow = 20 To 30
   If Range("D" & nxtRow) = "" Then
    Range("D" & nxtRow).Name = "NewLinkedCell"
    Exit For
   End If
 Application.EnableEvents = True
End Sub

What the code does when fired by a calculation in that sheet is assign the name NewLinkedCell to the next empty cell in D20:D30. Since your ComboBox is linked to the name, not any specific cell, it places the value in whatever cell is named NewLinkedCell at the time.

Please note this was just a quick idea and may need to be extensively modified to fit your needs. As written, it keeps overwriting D30 once it fills the range, but that might not be an issue if all you ever have are 10 items.

With more specifics, we can make it more refined.

Report •

October 13, 2009 at 03:03:46
Thanks DerbyDad03,

As suggested, I highlighting D20:D35 and defined it as a named range called NewLinkedcell. I also added the code to the sheet where other codes, the range and vlookups functions resides.I also changed the Combobox linkedcell property from D20 to NewLinkedCell. Also changed the line of code
For nxtRow = 20 To 30 to
For nxtRow = 20 To 35 as there are in act 15 invoice lines.

However, when selecting from the combobox by clicking on a product, it still places the value in cell D20 for teh subsequent tries.

Cells D20:D35 do not have any calculation functions/formulas. They hold the pure data which is used in cells F20:F35, G20:G35 etc. ie these are the cell which have the vlookup formulae, and the reference for each lookup function is the value in the range now defines as NewLinkedCell. Does the code expect cells D20:D35 to have/perform a calculation function? If so, this may be where it falls.

Again, thank you for helping with this.


Report •

October 13, 2009 at 04:17:20

Instead of using the linked cell as the output, hard code the first output address into the combo box change event, and use a counter to add an offset to the base address, then increment the counter after each selection.

The following is the code for the Combo box change event, with a variable (counter).

Option Explicit
Dim intCounter As Integer

Private Sub ComboBox1_Change()
Dim rngOutput As Range

'base location for output
Set rngOutput = ActiveSheet.Range("D20")

'add a row offset to the base address, and save the new value
rngOutput.Offset(intCounter, 0).Value = ComboBox1.Value

'increment the counter or reset it to 0
If intCounter = 14 Then
    intCounter = 0
    intCounter = intCounter + 1
End If
End Sub

Note that the counter is declared outside the combo box change event, so that it is not reset to zero everytime the combo box change event is fired.

It sounds as though this project would be more manageable if you used a form

The form could then control the creation and naming of the output (?invoice) document, add a date or dates to the document, control printing and save & clear up when done. You could include a correction option, so that if a wrong selection was made from the combo box it could be corrected. A spin button would allow the user to go back to any of the 15 lines and replace it with a corrected item. You could also include a 'prepared by' combo box and 'prepared on' clickable calendar.

The combo box is no longer embedded in the worksheet, but is part of an independant form, called up from a button on a toolbar.


Report •

Related Solutions

October 13, 2009 at 06:25:41
re: As suggested, I highlighting D20:D35 and defined it as a named range called NewLinkedcell.

That's not what I suggested. I said:

"Create a Named Range for D20" - not the whole range. If you link a ComboBox to a range of cells, it's only going to link to the first cell in the range.

My suggestion was to create a Named range for a single cell (D20), link that Named Range to the ComboBox and then change the cell that the Name refers to once the cell is populated.

Report •

October 13, 2009 at 12:59:26
Thanks Hack.

I have done it now as suggested, but it still keeps placing the value in the same cell, D20.

The range named NewLinkedCell now only refers to D20. Combobox linked cell property set at NewLinkedCell. The VBA code copied to the module where I have other VBA codes in the main invoice excell sheet. Not sure where I am going wrong as you are sure it should work.


Report •

October 13, 2009 at 14:20:54
Thanks Humar.

I tried your suggestion by copying your code into the module linked to the main excel invoice sheet. The Combobox in question is Combobox2, so I changed the code to reflect that. I also cleared the value from the combobox property Linkedcell.

But, everytime I pick a value it still puts it in cell D20.

I will look at your suggestions about using a userform. But I know next to nothing about user forms. I have never used one before let alone create and program one. I have alot of reading up to do...


Report •

October 13, 2009 at 15:25:14
Don't take this wrong way, but you need to read the instructions more carefully.

You said: The VBA code copied to the module where I have other VBA codes in the main invoice excell sheet.

However, I had said: add this code to the Sheet module where NewLinkedCell resides

A Sheet module is not not a standard module. It is the module that opens when you right-click a sheet tab and choose View Code or when you double click on one of the Sheet names listed under Microsoft Excel Objects in the Projects Viewer of the VBA editor.

VBA code for Sheet level events, such as the Worksheet_Calculate() code I suggested, has to be stored in the Sheet module for the sheet where the event will take place.

So I am assuming that the some sort of calculation takes place in the same sheet that D20 resides in. If that's not the case, then we need some more details to make this - or any other solution - work.

Report •

October 14, 2009 at 04:31:36

As DerbyDad03 said, the code must be in the right place.

To ensure that you are putting the code I gave you, in the right place, use the Control toolbox menu and enter design mode. Then right click on the combo box and select View code.

You will note that the Combo box change event code is associated with a worksheet and not with a module.

Also ensure that you use Option Explicit
and Dim intCounter As Integer

Both of these are in the worksheet code (not a Module code) and are outside the Combo box change event.

If you don't Dim the counter, the combo box will create a new counter every time and it will always be zero.

If you Dim the counter outside of the Combo box code it will exist and hold its value as long as the Worksheet is open, so when the Combo box change event fires, the Counter will already exist and have the incremented value from last time.


Report •

October 14, 2009 at 04:31:36
Thank you Derbydad03.

I try to read teh instruction carefully, and No, I am not offended by your comments. On the contrary, I am very grateful for the way you tirelessly try to explain and are not stingy in your explanations. My problem is that I am very novice with excel and VBA cosed that apply to it, and I had no idea that "Sheet module" had a special meaning. I thought it referred to the sheet that already hold some modules of code on it.

Anyway, armed with that piece of new knowledge you have imparted to me, I now have a piece of code that does what I want. I am very happy, and grateful.

Thanks again.


Report •

Ask Question