Excel VBA Combobox Advice

[hayley1988]

Hi,
I have a spreadsheet where sheet 1 shows a list of stores and dates. I then have sheet 2, where I have a created a combobox showing the list of stores from sheet 1. I am trying to write a code in sheet 2, where when I click on each store in my combobox it calls up the date from sheet 1 in a particular cell on sheet 2. Can anyone help me?
Thanks very much

 

[MixedUpExcel]

Hi hayley,

With your combobox on sheet 2, if you go into properties over the combobox, you will see an option to put a cell reference for linkcell.

This will put data into that cell which you have selected from the drop down box.. so for example, if you select 'Store 3' then it will put 'Store 3' in that cell you have linked to.

For the date to appear in another cell, you could do a vlookup against your 2 columns on sheet 1 using the linked cell data in sheet 2 as your criteria.

If you don't want anyone seeing your linked cell data - you can set the linked cell to be behind your combobox.

Would that work for you?

Thanks.

Simon

 

[hayley1988]

Hi Simon,

Thank you very much for the response.
I know how do so the cell reference part, I have got that in my spreadsheet already.
I would like to stay away from the vlookup if possible.
What it is, is I have a timeline on sheet2, so I would like it so that when I click on a store in my combobox on sheet2, it calls up the date from a cell in sheet1, and the refernce cell in sheet2 where the date will be called up creates a timeline.
Does that make sense?

Thanks,
Hayley

 

[MixedUpExcel]

Sorry Hayley,

I can't visualise that at all.

I think it's the reference to 'timelines' that's confusing me.

S.

 

[hayley1988]

Sorry I'm not making any sense!!
Right, on sheet1 I have a list of stores in columnA, and its date in columnB.
On sheet2 I have a pre-populated timeline.
On sheet2, I also have a combobox, which calls up all the stores on sheet1.
I would like it so that when I click on a store in the combobox, it brings through the relevant date from sheet1 (columnB) onto a cell in my timeline.
Does that make more sense?

Thanks,
Hayley

 

[MixedUpExcel]

Hayley,

Nope. What do you mean by Timeline? (Everything else sounds straight forward)

If I can get my head round that.. I might be able to come up with a solution for you - hopefully.

Thanks.

S.

 

[hayley1988]

Sorry it's just like a schedule of works.
All I need is the date to come over from sheet1 onto cell C8 in sheet2.
But I need to be able to do this for each store I click in my combobox.

Thanks,
Hayley

 

[MixedUpExcel]

Hayley,

Is it set out something like this:

Sheet1
Column A (Store) // Column B (Date)
Store 1 // 01/04/2015
Store 2 // 05/06/2015

and so on....

Sheet2
Combobox (over Cell A1) - linked to Cell A1

Combobox (contains a list of all Stores from Column A on Sheet1)

Put this formula in Cell C8 on Sheet 2

'stores' is a named range for all of your stores in a list on Sheet1

=INDEX(Sheet1!B2:B21,MATCH(Sheet2!$A$1,stores,0))

Make sure to format Cell C8 to 'date' otherwise it just shows a number

If you don't want Index Match like you didn't want a vlookup - can you say why and I'll see if there is another way around this.

Thanks.

S.

 

[hayley1988]

Hi Simon,

Thank you for that. I like the idea of what you are suggesting, but I can't seem to get it to work, it keeps calling up a formula error?

Thanks,
Hayley

 

[MixedUpExcel]

Does it actually say 'Formula Error' or #Value or #NAME etc?

Have you named the range? If not, try it without the word stores (you may have given it your own name anyway) and try the following:

=INDEX(Sheet1!B2:B21,MATCH(Sheet2!$A$1,Sheet1!A2:A21,0))