Adding a combobox to multiple sheets

[Ginjaninja]

Hi there

i was wondering if someone can point in the right direction, i am a newb when it comes to VBA so please be very basic if you reply

i am creating a daily planner for my partner for her very small business, i have made a drop down list using data validation which ok, but the problem was that the the list is quite long and i wanted it so that she could just type the first letter of a name and the choices would appear, so I googled it and found how to add a combo box and use some VBA code. I thought this may be applied to every sheet as i have a sheet for every week in the year but unfortunately it doesn't.

I have tried applying the code to every sheet and copying the combo box to some of the sheets individually but is does not like it as the combobox name changes and the code gives me an error.

is there anyway this could be done and if so i would appreciate if someone could help me solve it.

Thanks in advance
The NiNja

 

[Yongle]

Welcome to MrExcel
One way would be to use a combobox on a userform
The same userform can be opened on any sheet
If necessary the combobox can be populated either from data in the active sheet or a dfferent common sheet
I assume that the item selected in the combobox will be placed in the active sheet

More details will be required if you need further help

 

[Ginjaninja]

problem I have is that the planner is 7 day daily planner with multiple cells per day and I had already setup a data validation to provide a dropdown list, then I created the combo box to get the boxes to auto populate when I type the start of the name.

the issue i was having is that the original combobox tutorial told me to rename the combobox TempCombo which tallied up with he VBN code which contained the name TempCombo, but the copied combobox in the other sheets where labelling themselves a ComboBox1, i eventually had to rename the combobox to Combobox1 and change the script to match, then and then i just copied the combobox to all 53 sheets and copied the code and pasted it into sheet code. so i managed to get the result i was after but i am sure there could have been a much easier way to get there. oh well at least the other half is happy now.

Thanks
The NiNja

 

[Yongle]

Yes - I think you could have achieved that with ONE combobox on ONE userform with one script - but with 53 comboboxes and 53 identical scripts and a happy other half you are still ahead

 

[Ginjaninja]

Yep that’s the main thing so all good, thanks for your suggestions
The NiNja