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

[@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?

[@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".

[@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.

[@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.

[@danielcousins8872]

Your tutorials are not clearly explained

[@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.

[@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.

[@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

[@sudhirgulekar3818]

Hi, Liela Gharani Can we do a VBA code to go to a specified sheet? Sub GotoSheet () Dim xRet As Variant Dim xSht As Worksheet xRet = Application.InputBox("Go to this sheet"," ") On Error Resume Next If xRet = False Then Exit Sub On Error GoTo 0 On Error Resume Next If xSht Is Nothing Then Set xSht = Sheets(Val(xRet)) If xSht Is Nothing Then MsgBox "This Sheet does not exist", , " " Else xSht.Activate End if End Sub I Tried this....isnt working

[@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.

[@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 .

[@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.

[@truebitsapplications7806]

Excellent

[@ATPRO92]

There is a tiny problem, example: I have 100 sheets, if I click sheet no# 99 So i can go easily at 99, how can I come back from sheet99 to search sheet . Cloud please.....

[@jomelsanpedro7271]

MOUSE SCROLL WHEEL ActiveX COMBOBOX not working. please help thanks

[@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?

[@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.

[@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

[@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

[@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"

[@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?

[@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.

[@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?

[@8m405]

Possible to use an activeX DLL in excel 2016 vba?

[@yoishitanujaya7642]

Sorry lelila I'm talking about your video Activex combo box with vba

[@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

[@mdhashmath9353]

Madam make a video on to select range for data entry in userform based on combo box range value.....

[@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.

[@MrElleoluis]

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

[@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.

[@Eazynl]

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

[@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.

[@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!

[@glydllnn]

how to create an active combo box list from a fix list of sheet to open?

[@markanthonyramos144]

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

[@LeilaGharani]

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

[@Suryaprakash-uy8ng]

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

[@Amerona]

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

[@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.

[@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

[@mohammedMbwana]

78 viewer and 1st comment. Great video Leila

[@LeilaGharani]

Thanks for the quick watch & comment!

[@valmirberisha5232]

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

[@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.

[@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.

[@omar-sq6zq]

ماشاء الله عليك

[@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

[@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.

[@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?

[@joemarvisda9541]

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

[@alibourkaib3003]

How to define a line using combox ?!

[@grzegorzrzoska4344]

How can I achieve this in the user form?

[@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..🤔🤔

[@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?

[@dianseno7683]

I form indonesia Good your jobs tanks u

[@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

[@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.

[@PFunk-kt9gc]

I could not find the workbook

[@roberttimmerman8907]

I tried the excercice "Excel activeX Combo Box to select Worksheets with VBA" and at the end you make changes in the workbook. In my excercise there is no workbook present.

[@forrestgump2114]

Why not populating the combo box at the workbook open event, it’s the same code when activating the sheet just copy/paste instead of running two codes !!

[@daljeetdhillon4588]

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

[@bhavanishtrivedi458]

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

[@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.

[@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 👍👍👍

[@mantasmontvila1007]

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

[@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.

[@gregt7725]

Hi, What to do when user form seems to be doubled? Is it some kind of error in my excel? Once form has been created every click on it creates second one that actually is operative.

[@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.

[@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

[@azizuke]

plz leila; can you synchronize the combo box with excel drop down list !?

[@sanneng8856]

I really love your video. You explained clearly.

[@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

[@NetherVoiD]

The bad thing about combo box is that you cant stick it insidethe cell like data validation. You have to program like an engineer just to make combo box do that

[@maisarahmustafa3927]

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

[@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

[@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)

[@Galileo2pi]

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

[@mohamed.montaser]

how to contact you, i have a sample data that i want to show you and i have a question about it, how to lookup 2nd or 3rd value with multiple criteria with index and match

[@positive7393]

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

[@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).

[@faisalalrubai]

Thank you much.

[@zahirshah4117]

i love you sweet

[@raymarreyes2208]

How about in Google Spreadsheet? please help me :)

[@taizoondean689]

Thanks 👍

[@jamesjujare2599]

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

[@ATPRO92]

If there any video related about this problem please send the link

[@hosseinhosseinpoor4845]

thanks.

[@yemisiadeoluwa7907]

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

[@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!

[@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.

[@mchandan76]

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

[@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!

[@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.

[@flaviogarlatticosta]

Eccellente tutorial, complimenti.

[@LeilaGharani]

Grazie! 😊

[@bassisessaidexcel]

Merci

[@greggowaffles]

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

[@nguyenkhacquan1404]

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

[@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?

[@ernstguillaume2788]

This is great! What if the sheet are hidden?

[@zeny84]

does it work if selected sheet is hide?