List in Combo Box, when new item is selected put todays date in the adjacent column

Hi,

I have a spread sheet for equipment that gets loaned out on a regular basis. Cells B4 - B25 have combo boxes in them, with the options to select "On Loan", "In Stock Room" etc in them. What I would like to do is create something so that as the combo box is changed, i.e. from "In Stock" to "On Loan", a date changes adjacent to each combo box.

For example, combo box in B10 has been changed from "In Stock" to "On Loan". The adjacent cell records the date automatically that the combo box was changed.

Combo box date example.jpg

Can this be done?

Thank you for your help on this.

I have a way to do it. It may be a little obscure but it should work. First open up the VBA editor and add a module. Then you will need to add two subs to it. First off you must format the boxes so they are even and in the right position using the following code. Put both sections of code in your module before continuing.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The first sub will move all of your drop down boxes into a line starting from row 4 and column B. If you want to change the starting points just change the value of sr or sc. Now you must run the Sub setallshapesizes(). To do this click in between the Sub setallshapesizes() and the end sub and hit F5. After this as long as you have put both sections of code in your module it should work that every time you change a box the date should appear in the cell next to it.

Sorry for the late response.

It all works great until you try and add an extra row into the column (new stock is always coming in). I added a row into row 14, now when the new combo box in B14 is changed. I right clicked on all of the boxes and in the Format Control option I have selected the Cell link to be in column Z. For example, Cell B4's cell link is Z4, B12's is Z12 etc. Now when I run the macro, cell B14's combo box puts the date a row above it's adjacent cell. . Is this something to do with the formula, or is this something else?

Unsure what I have done wrong!