HomeОбразованиеRelated VideosMore From: Dinesh Kumar Takyar

Display all worksheets in listbox and print specific worksheet

74 ratings | 19636 views
Today we learn how display all worksheets in a listbox and print a specific worksheet by clicking a command button. We use a 'do until' loop to count all the worksheets and display them in a listbox when the userform initializes. Next we code a command button to print the sheet selected from the list box. This Excel training video is based on a 'comment-question' from one of our subscribers.
Html code for embedding videos on your blog
Text Comments (27)
XARAK KHAN (20 days ago)
Sir kindly help me on one thing . I want to show all sheets names in combobox. And then when i select any random sheet from Combobox it should show the data of selected sheet in listbox. Kindly help me. Thanks
Dinesh Kumar Takyar (18 days ago)
On Sheet1 create data with 6 columns. For example, Student Name, Address, Phone, Course Joined, Date of Joining, Date of Birth In the Visual basic editor insert a user-form. Add a combobox, listbox and a command button. Use the code given below. Option Explicit Dim myData As Range Private Sub ComboBox1_Change() Me.ListBox1.ListIndex = Me.ComboBox1.ListIndex Dim mySearchRng As Range Dim myFindRng As Range Dim myValToFind As Variant With Worksheets("Sheet1") myValToFind = ComboBox1.Value 'Name is ComboBox1 Set mySearchRng = .Columns("A") End With Set myFindRng = mySearchRng.Find(What:=myValToFind, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) ListBox1.AddItem With ListBox1 .List(.ListCount - 1, 0) = myFindRng.Value 'Data from Col A .List(.ListCount - 1, 1) = myFindRng.Offset(0, 2).Value 'Data from Col B .List(.ListCount - 1, 2) = myFindRng.Offset(0, 3).Value 'Data from Col C .List(.ListCount - 1, 3) = myFindRng.Offset(0, 4).Value 'Data from Col D .List(.ListCount - 1, 4) = myFindRng.Offset(0, 5).Value 'Data from Col E .List(.ListCount - 1, 5) = myFindRng.Offset(0, 6).Value 'Data from Col F End With End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() Set myData = Sheet1.Range("A1").CurrentRegion Me.ComboBox1.List = myData.Offset(1).Value Me.ListBox1.ColumnCount = 6 Me.ListBox1.List = Me.ComboBox1.List End Sub
XARAK KHAN (19 days ago)
+Dinesh Kumar Takyar cant find please make a video on it..
Dinesh Kumar Takyar (20 days ago)
Search https://www.exceltrainingvideos.com/
Sir please make a video about crystal report preview on userform
Pawan Chaudhari (11 months ago)
Hello Sir, I want to print the data only which I added from userform..Please can you help? Thanks In Advance.
Dinesh Kumar Takyar (11 months ago)
Search https://www.exceltrainingvideos.com
Ali Elbasry (1 year ago)
I need to make the listbox is multiselection  then a need to print all sheet I was selectexample: If I was select 3 sheet, I need to print the 3 sheetsHow I can do this?Thanks
sdyuisjkr3wui (1 year ago)
Hi, thank you for your video. I receive an error when trying to print. Error 13 or 9. It is related to the listbox1.value section.
sdyuisjkr3wui (1 year ago)
Hi, i believe the issue was the listbox being set to multiselect. It only works with singleselect? How can i make it work with multi?
sdyuisjkr3wui (1 year ago)
The error is gone, but now whatever sheet i choose, the page with buttons prints only.
sdyuisjkr3wui (1 year ago)
Dinesh Kumar Takyar hi, has there been an error with your reply?
Dinesh Kumar Takyar (1 year ago)
Check your code. This link might help: https://www.exceltrainingvideos.com/how-to-print-specific-worksheet-from-multiple-worksheets-in-workbook-using-vba/
Nez Rebosura (1 year ago)
Hi Dinesh, the list box is exactly what I was needing but is it possible to select multiple sheets to print instead of one by one? Thank you.
Dinesh Kumar Takyar (1 year ago)
ZiZa Digital (2 years ago)
hamsncom Hamid (2 years ago)
Hello Dinesh sir, its was very easy and meaningfull. i want to know the method how can i print all my sheets with one single click, can you please demonstrate it. please
Robert Whittle (2 years ago)
Dinesh, I have just been following your tutorial and when I try and run the form I get the following error, Complie error, Invalid outside procedure. Any thoughts?
Grimo (4 years ago)
Thank you!
Grimo (4 years ago)
How do select other worksheets using a drop down list? example: let's say I have 3 sheets. Sheet 1 has my drop down. I want to use my drop down to select sheet 2 or 3 instead of using my mouse to select the sheet tab. Thanks.
Grimo (4 years ago)
+Dinesh Kumar Takyar thank you
Dinesh Kumar Takyar (4 years ago)
This link might help: http://www.exceltrainingvideos.com/how-to-use-multiple-listboxes-on-user-form-in-excel/
Henri Padilla (5 years ago)
I have 150 sheet, in my book...and I want to print out 50 at the same time with out go back. I just want to print 50 sheet by click a box what sheet I want to print. This video can only print one at the time. Thanks
Dinesh Kumar Takyar (5 years ago)
Maybe this link will help: familycomputerclubdotcom/excel/working-with-3D-ranges-using-excel-macro.html. Please remove the word 'dot' and use a '.' :)
Vamsi Krishna (5 years ago)
Firstly Thank You for your video's. They are really very helpful. I have 1 quick question, it would be great if you could help me out. I have a work book with 150 worksheets, out of which 1 is the Summary of all 149. I created a macro to copy last row value (Most recent value) in cell C2. What I am trying to do is Copy C2 from all 149 work sheets and paste it in Summary work sheets from A1:A149. So that it would be easy for me to send the most recent data to team. Thank you
Henri Padilla (5 years ago)
Great, this great...way to print many sheet save time by clicking. And thanks again this is "HENRI" ....I also emailed a picture and another question similar about the previews one. Please check "inbox" ,,,@@@. That question is very helpful for many of your subscribers in general. Is about the same but other way to do it... Thanks again, keep learning and making life easy!
Kreetheran Rajoo (5 years ago)
hi fren, great info i recved from you. my enquiry is, with a command button, how can i print a selection part of a sheet example fr A1 to H40 only but want it to be in 1 page ( normally we need to do changes in page setup)

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.