Adding a combobox to multiple sheets

Ginjaninja

New Member
Joined
Jul 24, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 24, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
Yes - I think you could have achieved that with ONE combobox on ONE userform with one script :unsure: - but with 53 comboboxes and 53 identical scripts and a happy other half you are still ahead ;)
 

Ginjaninja

New Member
Joined
Jul 24, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Yep that’s the main thing so all good, thanks for your suggestions
The NiNja
 

Watch MrExcel Video

Forum statistics

Threads
1,114,073
Messages
5,545,832
Members
410,709
Latest member
Mrsamir
Top