Working with ListView Control in Excel VBA

[Music] hey guys welcome back to another video from vba a to z my name is long tomai and in today's video i'll be showing you step by step on how to use the powerful list view control in user phone if you missed our prior videos you can find them from the playlist called professional ui slash ui so before we begin please do not forget to subscribe and hit the bell icon for upcoming videos a list view can be used to display a collection of items in a controlled way it provides several ways to arrange and display items and are much more flexible and powerful than the list boxes for example additional information about an element or an item can be displayed with an icon and the labels and captions not all the properties are accessible in the user form without extending it through apis like grouping items but if you're interested to read more about it i'll leave a link in the video description so you can go through it whenever you have time and i'm happy to answer if you have any questions if you do not see list view control or image control in your user form toolbar please find a link to install from the video description before we jump right into it i'll give a quick demo of some of the work i've done loading the first phone this is um you know like the list view that i've added to the user form as you can see there's a grid line there's a hover effect on the selected item for the amount you can see the amount lesser uh in negative or in red you know like positive ones in blue with the formatting i think the header clickable header buttons and so on and then second view this one basically is demonstrating that you can add a checkbox to the column and then once you check an item you can see you know what has been checked and what is the status so you can read that uh you can also enable multiple selection like the list box and then you can look through and get item that has been selected here you've seen like in the first view that the row height by normal comes in this in this fashion so if you have to increase the height of the row there is no property to set that but there is a small way to accomplish that so this this particular view basically try to show you that and once you click on an item you'll see that you are able to read an element like this okay so here you can see the first column second column value and target value and so on going to the next one this one again this basically all the icons that you can add within the list view you know this is just a demonstration these icons actually does not symbolize anything at the moment is this a way to demonstrate that you can add those in this fashion so with the icon the label uh just the text text and then here just icon icon text and then here is an icon with the text and here you see is the text and so on okay so we'll go about you know how to create that as well and loading this up this one here is a you know like again the list view but in this list you can drag an element you know like within the list view you can move an alignment like this in this fashion and the last one i think uh basically here is the height of the row that you can set and then these are similar to the ones we have looking in the first one let's start creating a new form insert a new form and here we're going to insert a list view and if you do not see this list view control right click on the you know like on the toolbox and then go to your additional controls and then you find um you scroll through and find uh microsoft list view here it is so you find this the version could be different for you so check this box and then click on ok so once you have it you draw you select that and draw it into your phone so we'll start with something uh basic here you can rename the you know like like any other controls you can select the item and then you can set uh the name to it let's call this maybe um this view one something like that and the appearance could be flat and then the border style you can fix it you can put it to none check box you can set all these properties from the code as well so i will not go too much into it for now you can set the background you can set the picture and so on but you will not be able to set um the row colors the differentiation and all this though you can set the four color okay let me add in one button here and we'll say add items something like that so if i load this up now nothing happens now we'll start creating uh adding some items okay we'll say list item as um and list item itself okay and then we'll say with me dot list lv1 so that's the name we've already put and here you can see you can specify the view type it could be uh we'll start with the report with the default layout and then you can specify the grid lines basically we're trying to now accomplish the our first view like this okay so going back to the code and here you can see height column headers to false so that the columns are visible so if i do this now just the grid lines no headers yet so now let's add a header i'll replicate this block i'll tell you why in some time so let's say here um column headers dot add uh here index and key something you can leave it as default so that the control will take care of that so comma comma and then within the text you can maybe say um something like first name and here you specify the word um something like 79 and then replicate the block this can be the last name so and then the same with so load it again so here first name last name that is a column okay so now you know how to add uh set a view of some properties has been assigned and then here you're adding a column now let's add the list item okay so talking about the list item list item are in the most layman term you can say it's like a rule uh within a table okay so with the list view uh let's add an item we'll say set or you know like whatever we declared earlier here we'll say equal to dot uh list items dot at and then within here you can specify um the index again the key will just leave it as default the first value could be uh my name like this for example leave it like that and then the second value could be um this has to be list sub items dot at and then here you can again leave the default for index and the key and then the text that you want to add could be for my like this this is a demo so let's try to load this up and run this so long for my has been added okay so let's say that uh you want to do more blocks you can of course you can you know like uh replicate the block in this fashion and then you can say sharon something like that okay instead of doing that now we'll try to look through and then add items okay so now i'm gonna make this comment so that later on you can play around and you can still have a reference to what we've learned you can download this from the video description okay now we'll declare more variables let's call this theme uh r to symbolize our row number which will be a number type and replicate this and we'll call this two variables c and r now we'll use c to loop true and add columns okay i'll say for c is equal to zero to the column starts from 1 to 11 now let's say 10 and then next okay one thing to note here is that the the list column starts from zero but the cells you know like or sheet this you know like the worksheet starts from one so here we're looking from zero to ten and then instead of the first name which is at the moment uh static value will say the header is going to be cells and then it's going to be the first row and then whatever the column is here so c plus 1 that will be the first row first first row first column all right and then the width could be whatever the cell width is at the moment like this so let's try to run this so you see it has added all the headers let's expand this that's better okay going back so now similarly we'll do this by looping through all the you know cells and then we'll add more items so i'm gonna copy it down here for you so that you can replicate it if you want okay i'm gonna remove this and instead of the static values we'll start adding values by looking through them so here we have four design r will be a number as the demonstration will look from row number two till 97 so r is equal to 2 to 97 and then this is going to be next r again this this item is like a row num row representing a row within the the list view then here we'll say whatever is the r at that moment and then um let's see one column number one sales is supposed to be and then here i'm gonna say so this is from column number one column number two so the the code is gonna loop through each row like this one two and so on and at that time i'm assigning a value from each column like this so so far only two let's try to load this and see if it works so i just added you know all the values so we replicate this for all of them so you can uh loop through and do this one as well similar to what we've done here but for simplicity say again i'll just you know like do this replicate the blocks four five six seven eight nine ten and the last one is eleven okay so run this and then you can see this edit loaded the whole table from the back end to your list view now you can see the numbers are still you know like in the format um you know like it's not set yet let's try to set that so here i can say um format and then here you can set a specific format so let's see what format has been set here ctrl 1 will bring up this window and let me copy this and put it here if i run this now the you know like all the you know formatting is done without the four column yet uh let me just get rid of this that looks better so one quick thing we'll try to use um this uh the list of items you know like uh i will say theme we'll just call this list cell or you know like this basically lists up item something like that as list sub item okay and then down here i'm gonna say set this item equal to um you could specify whatever list has been added up here so we sub item and then you can specify the index so i'll just say then that will be starting from 0 till 11 so that's going to be 10 for the last okay and then here i can say this dot for color is equal to vb blue okay i think i misspelled it sorry so if i run this now all of them are blue uh now we can put a validation we can say that if this value or we can just declare one more variable we can say dim amount as currency is fine then we'll say amount is equal to this format this okay now we can utilize that so we don't have to keep on specifying cells all the time and here we can say if amount is lesser than zero sorry then we can say we want to have it as reds else gonna be blue okay so let's try this so now it looks okay so this is our first view again remember that this is the the raw height is standard you cannot you know like increase or decrease that and um the headers the background of this particular role cannot be changed only the selected items can be you know like highlighted okay so now let's quickly dive into how to add checkbox so we can just say this dot check box equal to true okay so if i load this now there's a check box so you can only add a check box to one of the column within the list view but let's say that you want to have the check box somewhere else not in the first column so you can move around this particular um column okay so let's try that this view on dot uh column headers and then you can specify which column so you'll have to specify only when this is already assigned and here we'll see the first column we want to move assign a position maybe to fifth something like that and let's run this so you can see the this particular username which was in the first column has now moved here okay so i think this covers our first view and our second view okay one small thing we want to see when the item is checked or unchecked we want to be able to read that right so coming back here once you double click on this this is the list view name and then you can have you know access to these events so let's select the item click so from here we can say that um when the item is clicked we can return the you know like the value to some text box let's add the text box very quickly maybe just change the appearance a little bit okay so let's say that um me dot set the text box value to whatever item was selected something like that item.txt text so when it is checked it is showing us the value um and we want let's say that we want to read the status as well we can also use the clicked if we use this it's always going to return us we can also specify which item has been clicked uh first let's see the status item.txt and item dot checked i think that should do so it says true and then it says false whatever item has been selected here this part is the status okay so maybe i'll just bifurcate this just for now something like this so this username has been checked unchecked okay that's how you'll reach here so by default you are not able to set the height of the you know like of the row so what i did was um first i created a small icon here i added uh something like something like this really doesn't matter but so here i took um like an image okay so that i'll set the height of the row base on the image after adding that to the list view so let's say this is the height let's say you know like it could be anything it could be as uh as per your requirement let's say i want the height of the uh the row to be this tall so you can take it in this fashion and i'll export it here as row height something like this okay and coming back here one this is running i can specify that uh that to you know like to the properties so before we can do that we'll have to load the image to our um to the image control so let's bring in the image list so this image list is basically here you can load the images but you will not be able to see it directly from here but you can utilize this uh you know like object from other controls which is in this example from the list view so going back to our code here we'll say that this shows small example dot image list dot list images and then we'll add and then index and the key will just leave it as it is or you can even specify the the key this could be like image one or something like this um and then here you can specify you cannot directly put in the link you have to use this um function load picture then here you specify the part so this workbook dot spot and then whatever list was jpg is it so this one jpg this is what we want to add and so basically here we have added one image to this particular control okay and we're we're gonna now assign um this to the list view so here down here i can here we specify that uh you can set it to the image list okay so let's try to load this now we'll not put in the index we'll just put in the key and then the picture let's try this so now the raw height has increased based on the height that was you know like said for the for the icon okay now going back let's say that uh we reduce the height a little bit and try it more one more example so let's make this a little bit shorter something like this this time we'll say this too go back to the code suggested too so now it just becomes draw highest decrease a little bit okay so this is how you can set the height of the the list view as per your requirement okay that is one way to do it i will start looking at uh we already started in a sense but i'll show you how to add all these icons to the list view that we have right now so before going that you know like as usual i've gone to web page we went through in the first video and i've downloaded all these icons and these are like icons that are uploading there so first again you can if you have many icons you can look through here for example in this code i'm looping through all the items within the you know within the folder that is ending with jpg but you know like it's totally up to you how you want to do it for now i'm gonna manually add this um items and share that this is uh unique like this so let's add four image to this and i'm gonna rename this to one so this one and then in the bracket one two three four so that i can easily identify them so here i'll say ico so again for simplicity sake i'm gonna do this um in the you know like in a simpler fashion so that you are able to follow better okay so now basically i've loaded um to this image i've loaded four images okay and then i'll try to bring these images to this particular list view now here i'm saying that my view is gonna be this and then the icons are going to be coming from this particular image list okay and then coming down here let's try to set uh an icon to this one so here for example uh this is the list item and then this is the sub item we're adding and then this is again we've already gone through this index key and then this is text and then right after that there is an icon so maybe you can specify uh index number for the image here you can even specify the name that you've added to these images okay so well let's try this once first so i'm saying just add the first image from the image list so you can see all this artic so this is as simple as uh it can be to use this let's say that for the third column we want to add another image let's say this is this is first i think let's say we want to add this second image again this can be dynamically set based on the value we have already seen an example here that you can specify what will be you know like um uh you know like uh the item that we declared earlier for sub uh list sub item and similarly in this fashion you can um access you know like the properties for the sub items so let me run this again you can see these are all the icons that have been added what else can we do here so this is the way to edit i think you can also edit uh yeah you can add just the icon without without the text let me show you how to do that okay so this one again is very simple let's say that for the third column we want to just assign an icon not the text so you can simply just get rid of the text parameter here the text person i'm just going to remove it and then here i'm going to see for for example so our fourth icon is um this i'm gonna want this so here just added the item okay i think this is because we moved this item the index must have changed but you get the idea this is just icon without the label okay that's how you can do it so before we end the video let me quickly show you how to read an item that has been selected now let me add this additional text box so going back um let's go to click even so when an item is clicked let's try to get what item has been clicked at that particular point of time so to do this we can use um selected item property let's try to do this we can say me dot xbox one dot value this will be our from here we want to find the selected item and then we can use the sub item whatever is the first column for now let's start with that okay so you can see it's showing us the first column value at the moment and zone two and so on like this so similarly you can assign multiple you know like for multiple columns just replicate the block the text box if i'm not mistaken with edit tree box so textbooks one two and three so column number one column number two column number three value whatever is selected at that point of time okay hold on i think we missed the xbox one let me just get rid of this for now text box okay there is no text for this particular column that's why this is blank okay so let's make it four maybe yeah so fourth column is now let me also quickly show you how to look through all the items within the list view and then check if the item has been selected or not so you might want to you know like uh based on the selection you might want to do certain things so this will become very handy let me add another button here for now and we can say uh get selection or something like that okay here we'll just write a small loop again we'll look from um zero that is i think one starts from one to list item.com okay first we'll try to print this out in our immediate window okay here we'll say um just get this one again and the one reverse the index at that point of time dot text maybe um so if i do ctrl g and run this now if i run this now it's basically just gonna just give me all the names right now with the name we want to get the status of the selection so we can print this to the sheet as well but for now let's say that uh list item dot selected should do i think like that so if item is selected this part will return true else false so let's select um this one this one and so i've set the multi select property to true for this one and now i'm gonna click on now there are too many items but you can see say nice here so all the items that have been selected will return true so there are too many items at the moment that's why it's not able to print it here so maybe we'll write this value to the worksheet so instead of printing here we can say cells now we want to print it out here we don't want to overwrite sheet1 data so we'll just call this output for one moment and we'll print the value from there to that sheet okay sheets and this is gonna be output okay so i is gonna increase this looping from one so i think it should be okay or we can just make it start from row number two and close this so the value of you know like um the list view sub item and then this is the status of that so let's run this again so i'm going to select um this this this facility man offers me another done so if i go back here you'll see that all this have been printed to true okay so that's how you can you know like read through all the items you already know that uh from here that how to get for each column you can use this sub item and then you can specify the column that you need so that's all i have for you in this video in following videos i will start looking on how to create a dashboard and explore more controls that you can add to your uh interface you guys have been so great and so supportive so thank you so much for your support thank you for watching if you found this video informative please do not forget to leave a like and subscribe for upcoming videos i'll see you guys in the next video bye [Music] you

Channel: VBA A2Z

Views: 24,679

Keywords: how to, work, with, listview, using, vba, how to add listview in userform, listview table, how to add icon to listview, control, how to write to listview, how to read to listview, listview properties, listview windows api, listview sub items, listview items, listview colors, listview background color, listview checkbox, set listview source using vba, listview source, Create, Advanced, Userform, VBA UserForm Design, Develope UserForm, create form, excel ui, form effects

Id: U1sQ1-Oa0fs

Length: 33min 15sec (1995 seconds)

Published: Sat Dec 05 2020