Excel ActiveX Combo Box to Select Worksheets with VBA
HTML-код
- Опубликовано: 1 авг 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
You can make data entry easier by letting users choose a value from a combo box. A combo box combines a text box with a list box to create a drop-down list.
🎓 Get access to the full Excel VBA course here: www.xelplus.com/course/excel-...
You'll learn how to create a handy navigation combo-box for your Excel workbooks. The ActiveX combobox is automatically filled with the sheet names of your open workbook. If you add sheets, these are automatically added to the drop-down box. Deleted sheets are automatically removed from the combo-box.
What you learn in this video:
- User-Friendly Macro: A step-by-step guide to crafting an Excel macro that significantly improves navigation in extensive workbooks.
- Dynamic Combo Box Creation: We'll show you how to add a combo box that lists all worksheets, updating itself whenever you add, delete, or rename a sheet.
- Interactive and Automated: Learn to make the combo box fully dynamic, eliminating the need for manual updates.
- Simple Coding Explained: Follow along as we delve into basic coding in Excel, including using ActiveX controls and worksheet activate events.
- Problem-Solving Tips: Encounter common issues like duplicated entries and empty combo boxes? We'll teach you how to solve these efficiently.
- Enhanced User Experience: Make your workbook more intuitive with custom messages and navigation aids in the combo box.
- Workbook Open Event: Ensure your macro runs smoothly even when reopening your workbook by setting a workbook open event.
★ Links to related videos: ★
Check out my VBA playlist here: • Excel VBA & Macros Tut...
Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
🎓 Get access to the full Excel VBA course here: www.xelplus.com/course/excel-vba-excel-macros/
hOW ABOUT MAKING IT searchable assuming you have 100's or even thousands of sheets
Love how you bring up problems that arise, so that the viewers can get a deeper understanding. Awesome video!!
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.
Thanks for the kind feedback Daniele!
awesome! what actually i'm been finding, finally i got it. thank you leila it's very useful for me.
As always very useful. Thank you for the great explanation!
This is very useful; It' is what I was looking for. You're great Leila, thanks
I have the exact requirement and looking for solutions on various websites. Nothing helped except this one. Thank you so much
Thanks for showing us! It's an amazing feature.
You are the best Leila thanks so much for all your videos
I am excited about this solution. Thank you so much for doing such a great videos and sharing knowledge.
You're very welcome Olga! Glad you like it.
Great as always step by step explanation
Thank you so much Leila excelent job God bless you!
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.
Glad it's helpful for your own forms Mick!
Thanks leila. I enjoyed that !!!
Wow.. Awesome.. Really much useful class.. Waiting more videos .. Leila 😊
Thanks for this concept.
I really love your video. You explained clearly.
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
Thank you Salim! Glad you're enjoying the tutorials :)
@@LeilaGharani Thanks Leila. I am addicted to these tutorials. Greetings from Hamburg, Germany.
After a long time VBA thanx a lot mam
I didn't even realize that I wanted this, but now I must have it! Great presentation! The workbook seems to be missing though.
Thank you very much Leila for your time and cool video!
You're very welcome. Glad you like the video.
Your post ws very helpful. Thank you so much leila.
I'm glad to hear that Mark. You're very welcome :)
Very useful and easy to create. Thanks.
always excelent thks Leila
Sorry.. I was able to download it - thanks! You are an expert; but more.. an excellent teacher!!
Great. Thanks for the kind words Errol!
THanks your a life saver
Very usefull; Thanks a lot.
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.
Very useful, think I'm going to use it in my work.
Glad you like it. Hope it worked out.
Your videos r just wow !!
Glad you like the videos!
Thank you for share it Leila
Super pretty. Super smart.
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
Eccellente tutorial, complimenti.
Grazie! 😊
Most Helpful to us
VISIT Bangladesh Please Madam
Thank you much.
Excellent
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
That's a good one! Thanks for sharing.
Hi, where would you enter that code?
help... what about certain sheets?
"spesific"
this does not explain where to place it. please elaborate.
I form indonesia Good your jobs tanks u
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).
Brilliant.
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?
78 viewer and 1st comment. Great video Leila
Thanks for the quick watch & comment!
Thanks 👍
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.
You're welcome Mehdi - do you mean having the procedures on the same ActiveX sheet instead of the workbook sheet?
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
@@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.
@@TheHellis best practice when code invoke a sheet is to use its "VBA Name" not the "Sheet tab name"
thank you for this usefull function , is it possible to open the list box ( Active X ) sheet every time we open the workbook.
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
Thanks Leila
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?
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.
Great tutorial.
How to list and select worksheets that contain specific letters or word with Combo Box? IS it possible?
Thank you
Hi Leila - Any guidance you can provide on how I can wrap text in a combobox and make OptionButton (grouped) mandatory?
Thanks mam.................
Hi, Thanks very much for your RUclips. 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.
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?
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.
Hope it helps.
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
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.
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.
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.
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...
@@LeilaGharani Thank you. Amazing to follow your tutorials! ☺
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.
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.
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?
Hi. Very helpfull tutotial... im wondering is it possible that the drop.down details in the combo box will be arrange alphabetically? Thanks
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.
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)
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
Agree - that's a good workaround. Many thanks for your contribution.
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
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
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.
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.
Hi Leila :) What if I only want certain sheet in the combo box? What should I do?
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
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..🤔🤔
thanks.
Thank you so much Leila for this Video, Please help me with query "how to exclude excel tabs name from combo Box"
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?
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.
What about changing name of the sheet ActiveX :-( Why not run same Code as in Worksheet_Activate() also At start up?
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
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....
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.
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
How can I make my hidden sheets appear in the ComboBox? Since right now it gives me an error. Thank you!
Leila, I am not into excel in a big way; but follow your lessons. where can we get the code for the active box?
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
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?
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!
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!
ماشاء الله عليك
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.
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.
I want to ask is to use a combobox userform to select worksheet.
Please make a video tutorial.
Thank you !
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.
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 .
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.
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.
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
Thanks a lot. I was looking for same. In fact, i was using hyperlinks to navigate across sheets but it i was not satisfied.
However, i would also wish if the combo box does not include the name of the active sheet.
Merci
Possible to use an activeX DLL in excel 2016 vba?
This is great! What if the sheet are hidden?
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
👍👍👍
Hi, how to do make this possible if you want to hide sheets?
Is it possible to make this drop-down also searchable?