If you're referring to what I said around the 12 minute mark, you'd use the listbox double click event and capture the data from there, i.e. determine what was highlighted and open a new form or whatever, it's up to you, you have total control with the code.
Thank you very much for the tutorial. I have tried this and its working. However I have numbers in some of my columns and would want to have them right aligned. Is there a way of doing this?
As far as I can tell from experimenting over the years, there isn't a way of doing this using this method, this is a hack that I came up with to avoid having to "draw" labels above the listbox. Remember the list box isn't actually a grid, we're just doing this as a workaround to get some gird functionality on a listbox that's designed for text.
@@BusinessProgrammer sir I have a userform with list box . So when we search (example student id) then I want list box to show the result in vertical direction .
@@BusinessProgrammer I did what you told me to do and got an email to confirm my subscription tot your newsletter but still haven't gotten a reply from that. Was there something else I was supposed to do?
With external data source, there is no way to bypass mimicking header listbox unless you use hidden sheet because only range can be data source for header - not even an array. But with multiple listboxes it may be tricky to manipulate headers in a hidden sheet.
I don't understand your comment, because I have multiple listboxes in this tutorial and have demonstrated how to do it, have you actually watched the whole tutorial? The step with an External data source is that your code writes the data be it an array or a collection to the sheet first. This can be accomplished quite easily with skilled coding where your getData (or whatever you want to call it) function handles this behind the scenes.
@@BusinessProgrammer , Sir, thank you for your comment. Yes i watched the whole tutorial twice. Sorry for my English, perhaps i did not put it clear enough. My point is only about ColumnHeaders. There is no problem to populate data to a listbox , from recordset, collection, an array, etc. But in order to display ColumnHeaders in the same listbox like you have shown - it is a must to first put data to some range being refferred to in listbox properties. You can not get such ColumnHeaders directly from datasource other than range. For me copying data to a sheet is a problem because there are hundreds of listboxes in my app an I would prefer to fill my ColumnHeaders directly from database
@@aNDy-qh1em Yes but that's my point, The way I show is the only way to do it. But the point I'm making is that you make writing to a range part of your process, I can see that if you've got hundreds of list boxes that this might be a problem, but are the 100's of list boxes all database grids?, because you only need to do this if you want them to look like database grids or tables. My point is that the worksheet range would be on a hidden sheet or sheets and you have code that handles the writing to the sheet behind the scenes such as a get data function where you write the sheet handling code that does it for all your calls, I've done this for clients and it works and for many years I've used the artificial headers with textboxes method, this one, however, is much better because headers and data stay aligned and it's easier to have code that writes to the sheet first. Say you have an array of 10,000 records, write the header of the array once to say range A4:F4 then you could page through the array with a next button on the form that writes say 20 rows of the array at a time to the hidden sheet, a memory variable in the form keeps the last row number you've processed and you could page up and down using that method, it requires being good at coding VBA, but that's what I teach. ;-)
@@BusinessProgrammer , all 100's of listBoxes are database grids alas. This is a corporate legacy tool (excel+mysql) refering to numerous business essences. Whatever, thank you for your tutorial, could be helpful for some specific 'degraded mode' solutions (when disconnected from db).
Hi. I didn't know about the 0 width to hide a column. Very nice, thank you. About the column widths, wouldn't be better to construct a function that would iterate over all column items, get the 'longest string' and recalculate it into width automatically? The only problem is it can't be a constant multiplier because for example 'i' has a different width than 'x'. And we don't always want to use monospace font. Is there a way to work around that?
Sure that's possible, however when doing tutorials one needs to try to keep to one subject, otherwise things can get confusing real fast. No one solution works for all situations, for example you may not want a particular column to go to max width as it may mess up the "look" of your grid. But if that doesn't worry you yes you could write a functions that counts the characters, but you also have to take into account the font type as all fonts are not proportional.
@@BusinessProgrammer Thank you for your reply. One other thing - did you ever experiment with interfaces / implements (statement). I cant find a good video tutorial for that and mainly usage when that is preferable. Or advanced creation of user forms for example: I've got data in worksheet. My Form1 loads them into listbox. That form has "edit selected row" button, when pressed, Form2 should appear with prefilled data, being able to edit them, click on save, that should "refresh" the listbox and when pressed save on Form1, it should update the worksheet. Any change you would create something like this here?
@@songokussj4cz I've looked into implements in the past, but didn't like it, mainly as a workaround for vba not having Object Oriented inheritance, I found the interface code to be too bulky. Yes I use forms as objects (hint) all the time, maybe I should create a video on that or ... put it in a paid for course ;-)
OMG...you freaking trainers...stop telling me what doesn't work, and stop telling me what i should not do. just tell me what i should do, and just tell me what works...i don't care about your trial and errors...it reaks of narcissism; however, i will subscribe for a couple of reasons. First, you started with the end result...i was sold at that point; and two, it worked. Earning a thumbs up.
Alexius, what doesn't work is useful for beginners so they don't go down the wrong path. Sometimes people need to know why not to do something, lots of different people have different learning styles. You like to learn a particular way, then you're free to skip the parts that don't work for you, meanwhile creators like me will continue sharing free content that you obviously benefited from.
Sean Sean Sean...that is an amazing trick to do the column widths! Thank you so much.
You're welcome Wyatt
Very good tutorial.
Glad you liked it.
Thank you very much for awesome tutorial.
You're welcome Yasser
Thank you very much
You're Welcome OĞUZ
So can you double click on the line to insert it to the cell in excel? If yes please tell me how, thank you
If you're referring to what I said around the 12 minute mark, you'd use the listbox double click event and capture the data from there, i.e. determine what was highlighted and open a new form or whatever, it's up to you, you have total control with the code.
Thank you very much for the tutorial. I have tried this and its working. However I have numbers in some of my columns and would want to have them right aligned. Is there a way of doing this?
As far as I can tell from experimenting over the years, there isn't a way of doing this using this method, this is a hack that I came up with to avoid having to "draw" labels above the listbox.
Remember the list box isn't actually a grid, we're just doing this as a workaround to get some gird functionality on a listbox that's designed for text.
@@BusinessProgrammer Understood, thank you very much for the prompt response and clarifying the issue.
Amazing, Thanks a lot!
Glad you like it!
sir i want to know if i have to show my search data in transpose direction in listbox .
Can you ask the question a different way please?
As I don’t understand what you mean.
@@BusinessProgrammer sir I have a userform with list box . So when we search (example student id) then I want list box to show the result in vertical direction .
I can't find the link to download the code for this lesson on the website address given at the end of the video
Kevin, the page will have an area where you can sign up and the code download link will be automatically emailed to you.
@@BusinessProgrammer I did what you told me to do and got an email to confirm my subscription tot your newsletter but still haven't gotten a reply from that. Was there something else I was supposed to do?
@@kevincoleman4871 HI Keven, I've Just noticed that my site is down, It's going to take a day or so to sort it out.
Bravo!
Thanks for the comment.
With external data source, there is no way to bypass mimicking header listbox unless you use hidden sheet because only range can be data source for header - not even an array. But with multiple listboxes it may be tricky to manipulate headers in a hidden sheet.
I don't understand your comment, because I have multiple listboxes in this tutorial and have demonstrated how to do it, have you actually watched the whole tutorial?
The step with an External data source is that your code writes the data be it an array or a collection to the sheet first. This can be accomplished quite easily with skilled coding where your getData (or whatever you want to call it) function handles this behind the scenes.
@@BusinessProgrammer , Sir, thank you for your comment. Yes i watched the whole tutorial twice. Sorry for my English, perhaps i did not put it clear enough. My point is only about ColumnHeaders. There is no problem to populate data to a listbox , from recordset, collection, an array, etc. But in order to display ColumnHeaders in the same listbox like you have shown - it is a must to first put data to some range being refferred to in listbox properties. You can not get such ColumnHeaders directly from datasource other than range. For me copying data to a sheet is a problem because there are hundreds of listboxes in my app an I would prefer to fill my ColumnHeaders directly from database
@@aNDy-qh1em Yes but that's my point, The way I show is the only way to do it. But the point I'm making is that you make writing to a range part of your process, I can see that if you've got hundreds of list boxes that this might be a problem, but are the 100's of list boxes all database grids?, because you only need to do this if you want them to look like database grids or tables.
My point is that the worksheet range would be on a hidden sheet or sheets and you have code that handles the writing to the sheet behind the scenes such as a get data function where you write the sheet handling code that does it for all your calls, I've done this for clients and it works and for many years I've used the artificial headers with textboxes method, this one, however, is much better because headers and data stay aligned and it's easier to have code that writes to the sheet first.
Say you have an array of 10,000 records, write the header of the array once to say range A4:F4 then you could page through the array with a next button on the form that writes say 20 rows of the array at a time to the hidden sheet, a memory variable in the form keeps the last row number you've processed and you could page up and down using that method, it requires being good at coding VBA, but that's what I teach. ;-)
@@BusinessProgrammer , all 100's of listBoxes are database grids alas. This is a corporate legacy tool (excel+mysql) refering to numerous business essences. Whatever, thank you for your tutorial, could be helpful for some specific 'degraded mode' solutions (when disconnected from db).
Hi. I didn't know about the 0 width to hide a column. Very nice, thank you. About the column widths, wouldn't be better to construct a function that would iterate over all column items, get the 'longest string' and recalculate it into width automatically? The only problem is it can't be a constant multiplier because for example 'i' has a different width than 'x'. And we don't always want to use monospace font. Is there a way to work around that?
Sure that's possible, however when doing tutorials one needs to try to keep to one subject, otherwise things can get confusing real fast. No one solution works for all situations, for example you may not want a particular column to go to max width as it may mess up the "look" of your grid.
But if that doesn't worry you yes you could write a functions that counts the characters, but you also have to take into account the font type as all fonts are not proportional.
@@BusinessProgrammer Thank you for your reply. One other thing - did you ever experiment with interfaces / implements (statement). I cant find a good video tutorial for that and mainly usage when that is preferable. Or advanced creation of user forms for example: I've got data in worksheet. My Form1 loads them into listbox. That form has "edit selected row" button, when pressed, Form2 should appear with prefilled data, being able to edit them, click on save, that should "refresh" the listbox and when pressed save on Form1, it should update the worksheet. Any change you would create something like this here?
@@songokussj4cz I've looked into implements in the past, but didn't like it, mainly as a workaround for vba not having Object Oriented inheritance, I found the interface code to be too bulky. Yes I use forms as objects (hint) all the time, maybe I should create a video on that or ... put it in a paid for course ;-)
@@BusinessProgrammer More complicated usage with forms - video - would be fine ;-)
Sir please send the link of this excel file thanks
If you watch the video there's a link to the businessprogrammer page where you opt into the mailing list and the file will be mailed to you.
OMG...you freaking trainers...stop telling me what doesn't work, and stop telling me what i should not do. just tell me what i should do, and just tell me what works...i don't care about your trial and errors...it reaks of narcissism; however, i will subscribe for a couple of reasons. First, you started with the end result...i was sold at that point; and two, it worked. Earning a thumbs up.
Alexius, what doesn't work is useful for beginners so they don't go down the wrong path. Sometimes people need to know why not to do something, lots of different people have different learning styles.
You like to learn a particular way, then you're free to skip the parts that don't work for you, meanwhile creators like me will continue sharing free content that you obviously benefited from.
For beginners both the dos and dont's are important.
I found both what works and what doesn't very helpful.
Well explained......Your accent is a mixer of British and American.....is there any specific reason???
Blend is more likely Irish, English and American in that order. :-)
Please excel file download link
I show how to get it in the video