Excel ActiveX Combo Box to Select Worksheets with VBA

[@LeilaGharani]

🎓 Get access to the full Excel VBA course here: www.xelplus.com/course/excel-vba-excel-macros/

[@DodemMahapat-h2x]

hOW ABOUT MAKING IT searchable assuming you have 100's or even thousands of sheets

[@danielemerli9781]

I already knew this method, but your videos are so well done that I watch them even if (sometimes) they're nothing new for me. I think your explanations are very clear and useful also for Excel beginners.

[@LeilaGharani]

Thanks for the kind feedback Daniele!

[@sasavienne]

What is that? Simply stunning. Brilliant ideas. I fell in love with Excel after seeing such useful tutorials. I do not want to stop learning from you. Thanks

[@LeilaGharani]

Thank you Salim! Glad you're enjoying the tutorials :)

[@sasavienne]

@@LeilaGharani Thanks Leila. I am addicted to these tutorials. Greetings from Hamburg, Germany.

[@jimbarton7072]

Extremely helpful as are all your videos. I made one small addition. Because I often use a hidden "settings" sheet, I added this to cbSheet.AddItem: If Sh.Visible Then Me.cbSheet.AddItem Sh.Name End If

[@LeilaGharani]

That's a good one! Thanks for sharing.

[@NikBFeld]

Hi, where would you enter that code?

[@IrvanNoorHasim]

help... what about certain sheets?

[@IrvanNoorHasim]

"spesific"

[@valmirberisha5232]

this does not explain where to place it. please elaborate.

[@caseyjohnston3332]

I didn't even realize that I wanted this, but now I must have it! Great presentation! The workbook seems to be missing though.

[@Suryaprakash-uy8ng]

I have the exact requirement and looking for solutions on various websites. Nothing helped except this one. Thank you so much

[@TradShowTom]

Very nice. Thank you, Leila. The only issue I had was if I added the code to put "Select Sheet" in it crashed every time. It would go to the sheet but then give me an out-of-range error. I checked my code 5-6 times and, near as I could tell, it was right. I'm sure it's something I'm doing wrong on my end though. Your tutorials are great! Not 2 hours long so easy to stop and start and get it going.

[@greggowaffles]

Love how you bring up problems that arise, so that the viewers can get a deeper understanding. Awesome video!!

[@yemisiadeoluwa7907]

You are the best Leila thanks so much for all your videos

[@Galileo2pi]

This is very useful; It' is what I was looking for. You're great Leila, thanks

[@Mick-qq1pi]

Love your work, excellent teaching and now I use this on all my forms and it works brilliantly. Really Love your work Leila and would like to now learn how to activate more selections from 1 sheet to another.

[@LeilaGharani]

Glad it's helpful for your own forms Mick!

[@mehdihammadi6145]

Thank you for sharing Leila. . Maybe you do that intentionnally but I would combine the procedures (write the code) in a way that avoid the switch to another sheet and coming back to run the macro if the workbook open on the ActivateX Sheet.

[@LeilaGharani]

You're welcome Mehdi - do you mean having the procedures on the same ActiveX sheet instead of the workbook sheet?

[@mehdihammadi6145]

I would write the code that manage the updating of the ActiveX control in a module than just call it on the two event Worksheet_Activate and Workbook_Open

[@TheHellis]

@@mehdihammadi6145 exactly my thought too. Hardcoding a sheet switch to "data" and then back means "data" must exist. What if we rename it to "data [year]" then the code fails. Duplicating the code works too, but that is WET coding, and it means you need to change the code at two places if you want to change something.

[@mehdihammadi6145]

@@TheHellis best practice when code invoke a sheet is to use its "VBA Name" not the "Sheet tab name"

[@sanneng8856]

I really love your video. You explained clearly.

[@xaquison]

Very useful and easy to create. Thanks.

[@errolwirasinghe]

Sorry.. I was able to download it - thanks! You are an expert; but more.. an excellent teacher!!

[@LeilaGharani]

Great. Thanks for the kind words Errol!

[@flaviogarlatticosta]

Eccellente tutorial, complimenti.

[@LeilaGharani]

Grazie! 😊

[@jokkiossaka3306]

Thank you very much Leila for your time and cool video!

[@LeilaGharani]

You're very welcome. Glad you like the video.

[@dianseno7683]

I form indonesia Good your jobs tanks u

[@sterne6]

I am excited about this solution. Thank you so much for doing such a great videos and sharing knowledge.

[@LeilaGharani]

You're very welcome Olga! Glad you like it.

[@nguyenkhacquan1404]

Thanks for showing us! It's an amazing feature.

[@jamesjujare2599]

Wow.. Awesome.. Really much useful class.. Waiting more videos .. Leila 😊

[@gregsdoodles4547]

THANK YOUUUU! I don't need the combo box, but I've been trying to figure out how to make sure my file opens to my dashboard every time it's opened and I think that last segment answers that. Now I'm kicking myself for leaving my laptop in my office so I have to wait until tomorrow to try it.

[@LeilaGharani]

Hope it helps.

[@gabriellecajurao7391]

Hi Leila i need your help. I am applying what you taught on multiple worksheets. I have a hundred of worksheet. Is there a way to have a search function with that combobox?

[@fotogenius7802]

Hi Leila ; thanks for sharing your knowledge . Your videos are always awesome .....i am a Mac user ; beginner in VBA and fan of it . However the ActiveX controls are not activated?? on my Mac!!!! Grateful if you could assist me . Thanks beforehand for your assistance .

[@vegaswould]

really awesome video. but if you right click on the sheet navigation arrows on the bottom left of the window, you get a pop up list of all the sheets in the workbook that essentially does the same thing.

[@markanthonyramos144]

Your post ws very helpful. Thank you so much leila.

[@LeilaGharani]

I'm glad to hear that Mark. You're very welcome :)

[@sumitthakur2241]

After a long time VBA thanx a lot mam

[@Scotts_Outdoor_Channel]

Hi I love your work here. I was wondering if you could send me the code to deal with the error when I pick a hidden sheet in my combo box?

[@giovannidimaio1977]

Hello Leila. I love what you do, it is really useful and helpful. You were born to be a teacher. This video really helped me with my work especially when I have a Workbook full of sheets. Is there a way that we can make the combobox searchable digiting some letters? Thank you

[@HiteshPatel-hq4lt]

Hi Leila. Thank you for all your VBA Videos. I have been using many of your VBA Codes quite a while now. Recently I have used your code of this video and achieved what I was willing to. However, as an additional feature, I wrote code to first unhide the sheet based on ActiveX selection but failed to re-hide the same sheet again when ActiveX sheet is reselected after relevant data entry. Below is my attempted code: Private Sub cbSheet_Change() If CBSheet.Value "Select a sheet" Then Worksheets(CBSheet.Value).Visible = True Worksheets(CBSheet.Value).Select If Worksheets("ActiveX").Select = True Then Worksheets(CBSheet.Value).Visible = False End If End If CBSheet.Value = "Select a sheet" End Sub

[@udihen4135]

hi, i have learned a lot from this lesson. how can i make a specific list and not a dynamic list? for exp-only 2 sheets from 5 sheets , that in the workbook?

[@ATPRO92]

awesome! what actually i'm been finding, finally i got it. thank you leila it's very useful for me.

[@nekomamoshi672]

Hey! thank you for the wonderful tutorials. Say I used the vlookup formula to create a class card all are working fines, thanks to the tutorial of yours. Now, my problem is I want to print it all once as one print job instead of selecting a name from drop-down list then pressing print because it can be annoying and time-consuming. Is there a way to do it? perhaps using VBA? thanks a lot.

[@joemarvisda9541]

Hi. Very helpfull tutotial... im wondering is it possible that the drop.down details in the combo box will be arrange alphabetically? Thanks

[@vida1719]

Great as always step by step explanation

[@mohammedMbwana]

78 viewer and 1st comment. Great video Leila

[@LeilaGharani]

Thanks for the quick watch & comment!

[@dagfinnvines9295]

Very useful, think I'm going to use it in my work.

[@LeilaGharani]

Glad you like it. Hope it worked out.

[@rajaelrajon]

Thanks Leila, I'm learning very much with your Videos. Since I found your channel I visit it every day. I have an observation, since the the ComboBox is dynamic, what if the user delete the worksheet "Data" that is used at the open event. It´s better to use something like this ... Private Sub Workbook_Open() Dim SH As Worksheet temp = "" For Each SH In ThisWorkbook.Worksheets If SH.Name "ActiveX" Then temp = SH.Name If temp "" Then GoTo salir Next SH salir: If ActiveSheet.Name = "ActiveX" Then Worksheets(temp).Activate Worksheets("ActiveX").Activate End If End Sub

[@LeilaGharani]

Agree - that's a good workaround. Many thanks for your contribution.

[@DelveInfotainmentNetwork]

Your videos r just wow !!

[@LeilaGharani]

Glad you like the videos!

[@ckokse]

As always very useful. Thank you for the great explanation!

[@michaeldiamond2726]

Hi Leila, Nice! I think it would be cool if you added some code that sorted the sheets in the combo box alphabetically. Could you create some sort of event that would automatically bring up the combo box (maybe add userform with a double click event - like I have in my custom formatting file)? I know this is only an example to show what VBA could do but I think it would be a cool tool to be able to add to my VBA toolbox at work (when I start working again). Maybe you could add this new code to the comment section (so you won't have to create another video). Really cool

[@LeilaGharani]

Hi Michael, sorting is a good idea - you can use an array for that like in this link: exceloffthegrid.com/sorting-an-array-alphabetically-with-vba/ We could create this on a userform too and maybe have a shortcut key that brings it up. This way the users aren't going to unintentionally bring up the userform when they double-click or right-mouse click etc... this could also be added to the QAT. It's a good idea actually....

[@michaeldiamond2726]

HI Leila, Thank you for the link to that code. I'lll add this to my userform that I have in my custom formatting sheet. I imagine you could call the function into the existing code, correct? Great idea about the QAT, I am afraid to use a mouse event because you might not want to call the userform but still perform the same action with the mouse.

[@michaeldiamond2726]

I was able to get some additional code from the Mr Excel Message Board. Just search a search on Sorting sheets in a list box

[@leeeric6292]

Hi, Thanks very much for your KZbin. Instead of using change event, I have tried the "click" event also, and also work. Could you tell me their differences ? Under what circumstances they will get different results? Thanks.

[@gwhittaker]

Hi Leila, I love your work and have been silently following for years now. I don't know if you actually answer questions here or not, but here goes. I've been using your sheets navigation list form this video and have come across the need for it to be alphabetized. Can you point me in the right direction? Thanks for any help.

[@kadermeeran8816]

Thank you Leila.. Your Videos are very helpful and your presentation is amazing very calm and composed... I would like to know "Excel ActiveX Combo Box to Select Worksheets with VBA" same code how can we print in PDF selected sheet with sheet name as file name . I added the code but showing error Sheets(SheetArray()).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ Sheets(i).Name, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False awaiting your reply

[@frankk1818]

Hi Leila, i’m always suprized to see how easy and understandable you make excel. Leila how can i make this combobox work if I “hide” worksheets. With the current vba formule it give a error (de bug”? Thanks frank

[@sjurnielsen8230]

Another great tutorial! Thank you for your effort. The only thing I miss here is a fully dynamic solution on the Workbook/open macro. If you change sheet name on "ActiveX" or "Data" you run into minor trouble :-) Would be nice to be able to copy and paste the Macros into other workbooks without rewrite it.

[@LeilaGharani]

Thank you. Yes - agree. To make it more dynamic we could jump to the last sheet in the workbook by counting the sheets - something like Sheets(Sheets.Count).Select and then jumping back to the first sheet...

[@sjurnielsen8230]

@@LeilaGharani Thank you. Amazing to follow your tutorials! ☺

[@kaaa3485]

Hi Leila, If you we are analyzing 2 factors like time and temperature, we use a graph to see the effect of time on the temperature. But how can we analyse the data based on 3 or more factors to see their effect. Thanks in advance.

[@hoffy11394]

Does anyone know how to only show a subset of the sheets? I have hidden sheets that I don’t want visible to the end user.

[@josebernal3911]

Great code Leila, what about if you have lets 20 sheets and you go to the last how you return to the activeX sheet without going thru the slider? let's say include a button in every sheet to jump to the ActiveX sheet

[@LeilaGharani]

Good question - There are different ways you can do this: 1 way is to use a name to bookmark the table of contents page and use the name to go back. Another simple method is to include a hyperlink in all the sheets. All you have to do is create a hyperlink in sheet 2 for example, then copy the cell, right mouse click on the tabs and select all sheets. de-select the sheets you don't want to have the link and paste. This puts them automatically in all the sheets (I'll make a video on this soon...) Another approach I use is a macro that creates a table of contents and puts the link-back as well. I cover this one inside the course.

[@educacionportable]

Thank you so much Leila excelent job God bless you!

[@johnborg6005]

Thanks leila. I enjoyed that !!!

[@mikelennon1078]

The Format Control is there with 4 options. (Size, Protection, Propertied and Alt Text) there isn't a 5th option " Control" . I have checked two different computer with Excel 2019 and 365. Non shows the Control tab???? Your thoughts please.

[@ernstguillaume2788]

This is great! What if the sheet are hidden?

[@bhavanishtrivedi458]

Hi Leila - Any guidance you can provide on how I can wrap text in a combobox and make OptionButton (grouped) mandatory?

[@daljeetdhillon4588]

Thank you so much Leila for this Video, Please help me with query "how to exclude excel tabs name from combo Box"

[@truebitsapplications7806]

Excellent

[@taco7rk34]

Always great videos Leila. I like when I get notifications that you have a new video. I run to my computer. From a practical standpoint, in what business scenario would this be a better option then just clicking the tab itself? Do you have any books that you recommend to learn VBA for Excel?

[@LeilaGharani]

Thank you. I'm really glad to hear you enjoy the videos :) I'd say this is good for files that have a lot of tabs where you need to scroll to find the right tab, or for customized applications, where you hide the tabs from view and get the user to jump to different pages from the drop down only.... You can find the books I recommend here: www.xelplus.com/resources/#excel I love the one from Walkenbach.

[@mchandan76]

thank you for this usefull function , is it possible to open the list box ( Active X ) sheet every time we open the workbook.

[@fadhelzammouri4955]

Leila, I want to keep the VeryHiden Sheets from appearing in the drop down list, how would I go about doing that? your hep is much obliged! Fadhel

[@younisrafiq4161]

Hi Leila.. what if we have to select multiple sheets without referring name or index. number As an example I want to print multiple sheets, how can I select them to get printed at once. Thank you

[@Darioq57]

Hi Leila! Why isn't the code working for me? It will not populate the box. CBsheets is what I named the Combobx. Private Sub Worksheet_Activate() Dim Sh As Worksheet For Each Sh In ThisWorkbook.Worksheets Me.BCsheets.AddItem Sh.Item Next Sh.

[@vargheseaugustin7733]

leila can we select multiple sheets from this combo box by pressing ctrl and activate multile sheets, if not what changes we have to make

[@wayneedmondson1065]

Hi Leila.. another excellent and useful video.. very professionally presented and easy to follow. I watched and copied your steps through the video and then to reinforce the lesson, I built the same from scratch without the video. Both worked without a hitch. This could be easily adapted to printing vs. selecting, sending the selected sheet name from the Combo Box to a Worksheets(cbSheet.Value).PrintPreview or Worksheets(cbSheet.Value).PrintOut command instead of Worksheets(cbSheet.Value).Select command. That would be a time saver. On that thought.. what about a video lesson on using the List Box ActiveX control to enhance a Print Preview or Print Out procedure for selecting multiple sheets for processing simultaneously? I can envision multi-selecting from a List Box to do Print Previews of groups of sheets or sending selected sheets directly to the printer simultaneously as a single print job. That would be helpful.. something to consider if you are looking for a new topic for your next video. Thanks again and Thumbs up!

[@LeilaGharani]

Thank you Wayne for your comment! I love your idea on ListBox that enables print preview or directly printing selecting sheets. I've added that to my list. Thank you!

[@wpaz76]

How do use radio buttons for activeX control and record the output into another tab - options are Yes And No - what VBA code would you write for this please?

[@DarcyWhyte]

Nice! Have you thought of adding a function into a module that populates the dropdown? Then it can be reused from the workbook load and the sheet activate. You just pass "me" from the activate as a parameter to get the context. From the open pass a reference to sheet(1).

[@Habib-fz3zq]

Amazing videos. I am using your version here. I am also trying to create a version where all worsheets are hidden (but ActiveX) and where only the selected combo-box option is unhidden with a button in each worksheet to take me back to ActiveX worksheet. I hope this makes sense :) If you have a tutoral video to help with this... Thank you again.

[@ferdinis]

Hi Leila. Thank you for your video. Very well made. I have a question regarding this topic. If we want to sort the worksheets inside the combo box. How can we do it? Your reply is much appreciated.

[@errolwirasinghe]

Leila, I am not into excel in a big way; but follow your lessons. where can we get the code for the active box?

[@pjy1006]

Hi Leila, The video is great! I did the exact same one yesterday and the combo box was populated successfully. Today I played with the code a little bit in VBA editor. But later in the day, I tried to open a new excel file and used the exactly the same code by following every step here. However, this time, my combo box just wouldn't populate anything. I tried 3 times this evening but still failed. Any clue?

[@HamidurRahman]

Thanks for this concept.

[@jesusmyhope554]

How to set lable or any command button back style to transparent. After click on design mode or running userform it came to its original back style

[@karuppusamyn515]

Thank. How to transfer data to specific sheet same method in userform combobox ? i have fixed the sheet name like data (wrote in the algorithm). now I need change instead of data to combobox value. I hope you. Help me..🤔🤔

[@maisarahmustafa3927]

Hi Leila :) What if I only want certain sheet in the combo box? What should I do?

[@taizoondean689]

Thanks 👍

[@positive7393]

Great tutorial. How to list and select worksheets that contain specific letters or word with Combo Box? IS it possible? Thank you

[@faisalalrubai]

Thank you much.

[@nhac_hay1995]

I want to ask is to use a combobox userform to select worksheet. Please make a video tutorial. Thank you !

[@Amerona]

What about changing name of the sheet ActiveX :-( Why not run same Code as in Worksheet_Activate() also At start up?

[@ashvinkalleejaisriram]

Thanks a lot. I was looking for same. In fact, i was using hyperlinks to navigate across sheets but it i was not satisfied.

[@ashvinkalleejaisriram]

However, i would also wish if the combo box does not include the name of the active sheet.

[@Eazynl]

Is it possible to make this drop-down also searchable?

[@abdultraders]

the question might be a bit out of context. but i am searching answer since a month surfing internet. Is there any VBA code which help us to automatically drop down list when we start typing in a cell that contain data validation. using Mac version so no ActiveX. thanks.

[@dembasy6717]

Very usefull; Thanks a lot.

[@8m405]

Possible to use an activeX DLL in excel 2016 vba?

[@wasimchhipa5708]

I have combo box which uses list fill range & on click of value in combobox userforms open, but after closing when I click the value selected it does'nt open the userform again.

[@DidNotReadInstructions]

Super pretty. Super smart.

[@jesusmyhope554]

hello madam, is there any chance that we can make a dropdown list in combobox of available userforms in same excel and open them by selecting combobox value. looking for your answer.

[@vasudevan7637]

Hi Leila I need one clarification in excel conditional formatting (How to turn conditional formatting cell color into fill color in another cell) Please let me know once you found the solution Thanks

[@SpiritualMotto]

Bro, i developed a VBA where it will be used globally so many regional PC language there, but i want extract current month using following, FORMAT(Now, "MMM") it returns in my PC as FEB but if PC has Bulgarian language, it is showing as Febr as month using the same function. Is there anyway to get the month in english irrespective of any PC language.

[@yoishitanujaya7642]

Hi leila I have a question, what if we hide the selected sheets Is it still appears in combo box Thanks anyway God bless you 👍👍👍

[@accountingjobwantedanywher8220]

Most Helpful to us

[@accountingjobwantedanywher8220]

VISIT Bangladesh Please Madam

[@valmirberisha5232]

Hi, how to do make this possible if you want to hide sheets?

[@Meolimo]

always excelent thks Leila

[@BigIz104]

Awesome! Thank you! I recorded macros for hiding my sheets that will only appear when selected from the combobox. Is there a way to have the combo box unhide the sheets then go to them?

[@johnjohnjman]

What if I want the Combo Box to select a few specific sheets? (i.e. not all the sheets in the workbook but just the ones I want)

[@domuhutagaol2958]

Dear Ms Leila, How are you and hopefully you are well. I am working with VBA macro where I am creating user form of combobox to list some of sheets on my workbook into combobox ( sorry not all). let say I have 6 sheets which are Sheet1, sheet2, sheet3, sheet4, sheet5, sheet6. I wanted sheet2, sheet5 and sheet6 go into combobox. Could you please advise me the vba code on this. appreciate your comments. FYI I am using the below vba code but all of the sheet went into the combobox but I want only few sheets. below was the vba code : Private Sub userform_initialize() Dim n As Integer Do n = n + 1 CmbReporting.AddItem sheets(n).Name Loop Until n = Worksheets.Count end sub I change the name of combobox become cmbReporting.

[@MrElleoluis]

How to show only the sheets that i want in the combo box? Only 5 sheets in 30 for example

[@smithy559]

Thanks, Very useful - can we adapt so it also includes Chart sheets?

[@LeilaGharani]

Yes - we'd just have to use Sheets instead of Worksheets.

[@ashutoshbagde3276]

Nice Video. but still there is a big problem. U coded If Sheet = ActiveX then goto Sheet2 'to disable event goto ActiveX 'to enable event endif Now Suppose user deletes the tab Sheet2 and saves the excel. Then on reopening code wont find Sheet2 and this auto loading wont work. So there should not be event hard coded "goto Sheet2".

[@sunnyc25]

THanks your a life saver

[@mantasmontvila1007]

How can I make my hidden sheets appear in the ComboBox? Since right now it gives me an error. Thank you!