VBA UI UX-16: How to use powerful TreeView Control in Excel UserForm

[@scotolivera8207]

God bless you, I can only imagine how much time I took you to learn all this trick and skills

[@brenthalbach3645]

This is absolutely awesome! I have tried to find an example of this for a long time with no luck. You have explained it very well in your video, I think I finally have a basis to start on a Treeview. Thank you for all your amazing video content.

[@VBAA2Z]

Thank you!

[@ExcelForFreelancers]

Great training. Thanks so much. I am thinking Customers: Invoices: Invoice Items. I will try to put it together for a training. Thanks again

[@VBAA2Z]

Thanks for visiting sir!

[@hericklenin]

Genius! Sometimes we can find gold in youtube and your videos are gold. Thanks! I would like to learn how to parse Excel formulas like that. Are you going to discuss that?

[@VBAA2Z]

Thanks for your kind words. Sure we can discuss that formula parse in upcoming videos. If you haven't, please do not forget to subscribe to my channel for upcoming videos. Regards.

[@alessandrolazzarini270]

Hi Lung Pamai! Thank you for your videos, they are helping me a lot! I'm solving how to click on parent checkbox for having all children checkboxes clicked? There is a video you handled this topic? Many thanks.

[@VBAA2Z]

Glad to hear!

[@wikamdiopekiang4972]

Easy to understand and very helpful - thank you

[@VBAA2Z]

Great to hear!

[@williamarellano9586]

Hello, it's a great explanation, thanks, sorry the link provided is no longer available?

[@VBAA2Z]

Thanks! Please refer to 024 VBA UI UX-16: How to use powerful TreeView Control in Excel UserForm vbaa2z.blogspot.com/2020/12/you-can-support-vba-a2z-by-simply.html

[@williamarellano9586]

@@VBAA2Z thanks 🙂

[@VBAA2Z]

@@williamarellano9586 Most welcome, thanks for watching.

[@djunabug]

Amazing! You are a wizard, and a generous one at that :)

[@VBAA2Z]

Thank you kindly!

[@andreasmusic1222]

Very helpful tutorial! I believe the way it's set up now however is that if there's a city with the same name in another country, it'll only add it once. What do you suggest as a workaround for this? I thought about setting the key to the full path of each item, e.g. the key for New York in USA would be "North America\USA\New York City", and the key for New York in another country would be "North America\Canada\New York City". It's obviously a not realistic bad example but I hope you know what I mean. Let me know your thoughts.

[@VBAA2Z]

Good point. perhaps create a unique ID with Country, City and ZipCode and display text can be duplicated

[@manfredphol3732]

The Video is very helpful, it would be nice if there was a description when an action is executed when child is clicked

[@VBAA2Z]

check Private Sub TreeView1_Click() in project.

[@manfredphol3732]

@@VBAA2Z Sorry, I can't find the source code / download the project 😢

[@VBAA2Z]

@@manfredphol3732 be sure to check your spam folder

[@octaviopinto2809]

Excellent examples and explanation. Easy to follow

[@VBAA2Z]

Glad it was helpful! thanks for watching.

[@Petrick-ri3sq]

another great episode thanks!

[@VBAA2Z]

Glad you enjoyed it

[@brenthalbach3645]

Do you have any videos on loading a Treeview directly from a sql recordset?

[@VBAA2Z]

no, not in channel yet

[@Penbam]

Wow your channel is just amazing.

[@VBAA2Z]

Thank you so much 😀

[@atingnewmai3680]

Achi you are GENIUS.

[@bnancy3699]

This is really informative. thanks for sharing your knowledge.

[@VBAA2Z]

Glad it was helpful!

[@gustavobarella3940]

Thanks for the exemple!

[@VBAA2Z]

My pleasure!

[@gustavobarella3940]

@@VBAA2Z i requer this example from your site, could you please send to me? Thanks

[@JuribSunn]

Its a great explanation. Thank you.

[@VBAA2Z]

Glad it was helpful!

[@Evadisa-q8h]

thanks again Pamai

[@VBAA2Z]

you're welcome

[@octaviogomespinto3383]

Excellent. Thank you very much

[@VBAA2Z]

Glad it was helpful! thanks for watching.

[@astigxtutorials]

Hi, is it possible to select a node then it will use the selected node as a filter to another table? Then it will show for example the sales of that node.

[@VBAA2Z]

yes you can. Transfer all items for selected node and transfer to array and use it to apply filter.

[@greglovern4160]

This is the 32-bit Treeview control from Common Controls which dates at least back to VB6 (1998), possibly as early as VB4 (1995). It's been included in Windows for many years, but there is no 64-bit version and no plans to make a 64-bit version, so it doesn't work in 64-bit Excel. 64-bit Excel first appeared in version 2010 and although 32-bit Excel is still available, most people today have 64-bit Excel installed not 32-bit. So if you have 32-bit Excel and set this up and send it to other users, it likely won't work for many of them.

[@wennychez5606]

Great tutorial. Thank you so much

[@VBAA2Z]

Glad you enjoyed it!

[@Noune-fp2qr]

Thanks for another very useful video

[@VBAA2Z]

You're very welcome!

[@reeggiee]

Hello, thank you for the tutorial. I can't seem to be able to find it on your website. Could you please PM me the link?

[@VBAA2Z]

Most welcome. Here's the link vbaa2z.blogspot.com/2020/12/you-can-support-vba-a2z-by-simply.html

[@BloominOnion1]

hmmm what if I have two keys of the same name, but under different parents? It's throwing an error. Thanks for the great help.

[@VBAA2Z]

Key must be unique in collection

[@holieh5301]

Brilliant! Thanks

[@VBAA2Z]

Glad you liked it!

[@gustavogoncalves8337]

Hi Lung Pamai! First of all, thank you so much for your high quality videos! It's helping me a lot in my projects. They are motivating me to start programming and I am really enjoying it =) I have one question regarding multiple users in a single UserForm in Excel. Is it feasible to have around 4 users o the same userform to input, update and delete data from the same workbook? I did not test it yet but it could be a problem according to some search in internet. In my case, all users have the 365 license. Thanks a lot!

[@VBAA2Z]

That is feasible, create/keep frontend ( Userform ) and database/workbook separate. Users can open the frontend from their respective computer.

[@edwardpaulbangura]

This is superb.🥰😍

[@VBAA2Z]

thanks

[@ricardoevandropereiradeoli9028]

Excelente conteúdo, muito obrigado por compartilhar o seu conhecimento.

[@brayanangelpalmafrias109]

Good afternoon, please post a video hoy I can search for a data within a treeview

[@dssabnis]

great code.. good contribution.

[@VBAA2Z]

Thanks!

[@jademitchell4992]

Download for this item does not appear to be available?

[@VBAA2Z]

It should be in the list

[@Faizht]

Where can I download the Formula Treeview utility you have showed at the start of the video? This could be really useful! Keep up the great work, your videos are a great learning tool!

[@VBAA2Z]

I'll try and cover how to create one in upcoming videos.

[@TyiZeak]

@@VBAA2Z Have you made this video yet, As I cannot find it in your videos.

[@TyiZeak]

​@@VBAA2Zany update?

[@brentmawhinney5703]

Excellent video and explanations. Likewise the Formula Treeview Utility form build would be a very worthy item to have. Please do an addendum to this video and show the build for the Formula treeview. For me it would worthy of download from your website. I checked there to see if it was available, but no luck. I've subscribed to keep an eye on the VBA work you've done and are doing.

[@firstcommenter202]

what is the maximum number of nodes this control can have?

[@klinkmanuel]

thank you

[@VBAA2Z]

You're welcome

[@simonnartpok6802]

Hello sir, 'm looking for .NET c# or vb how to add child to parent by specifying the parent id or name. please guide. thanks.

[@VBAA2Z]

it is not straightforward as it is VBA. try below. vbaa2z.blogspot.com/2020/07/net-treeview-add-child-node-to-existing.html

[@JonathanFox-kl1lu]

I'm attempting to apply this to my own workbook, but each time I do it, re-do it, lookup new methods, no matter what I try I seem to keep getting this "User-defined type not defined" compile error. I've followed the video step for step with the only real difference being the number of sheets I have in my file and the columns I am using (in this case columns A through C). I'm currently sitting at time frame 24:34+ after entering the sub name, trying to run it the exact same way he did. Is this a common issue for anyone else? Code: Option Explicit Public EntityOwnershipData() As Variant Sub laodTvDATA() Dim dic As Scripting.Dictionary Dim dkey As Variant Dim ArrIndx As Long Set dic = CreateObject("Scripting.Dictionary") EntityOwnershipData = Range("A2:B121") UserForm1.TreeView1.Nodes.Add , , "Primary", "Primary" For ArrIndx = 1 To UBound(EntityOwnershipData) If Not dic.Exists(LCase(EntityOwnershipData(Arr.Indx, 1))) Then dic.Add LCase(EntityOwnershipData(Arr.Indx, 1)), Nothing UserForm1.TreeView1.Nodes.Add "Primary", tvwChild, EntityOwnershipData(ArrIndx, 1), EntityOwnershipData(ArrIndx, 1) End If Next ArrIndx End Sub Command button code: Private Sub CommandButton5_Click() With Me.TreeView1 .Appearance = ccFlat .CheckBoxes = True .LineStyle = tvwRootLines .Nodes.Clear End With laodTvDATA End Sub Let me know if anyone else is having this issue or if you need further information about the file to understand my situation.

[@VBAA2Z]

Please be sure to add a reference.

[@JonathanFox-kl1lu]

@@VBAA2Z Alright so I found and corrected the issue. For those interested, I decided to dim dic as and object then set dic = a scripting dictionary. Sub laodTvDATA() Dim dic As Object 'Here Dim dkey As Variant Dim ArrIndx As Long Set dic = CreateObject("Scripting.Dictionary") 'And here (already included) EntityOwnershipData = Range("A2:B122") However, I did run into some more issues/wants that I couldn't immediately figure out just after some brief internet digging. Now when I attempt to add my third column data it gives me an "Out of range" error. From what I can gather it may be due to the formatting of the data being percentages? I'm not really sure, but if you know where I could go to solve this issue or better yet place the percentages alongside the companies in my dataset it would work wonders. For context: ABC Co. owns XYZ Co. ownership % is 30% ABC Co. (parent) -XYZ Co. 30% (child) Also, another feature you might incorporate in like a short video or something would be a way to expand all of the boxes instead of having to manually open each one. I would find this very helpful for my case. Otherwise, you've made great and helpful content and by far the most inclusive/relevant video I could find on the web. Thanks a ton!

[@SuperDavidFz]

Como fazer Treeview interagir com multipage?

[@VBAA2Z]

not sure I understand

[@SuperDavidFz]

@@VBAA2Z how to use treeview with multipage?

[@VBAA2Z]

are you not able to add TreeView under multi page?

[@SuperDavidFz]

@@VBAA2Z I already got it, thanks you some much

[@dinawangjkiu9712]

excellente

[@VBAA2Z]

Thank you, Cheers!

[@imranbhatti8580]

Nice Lung Pamai! Is it supported both in 32 and 64 bit Excel?

[@VBAA2Z]

Yes it does. I use 64bit Excel / O365 WIn10

[@imranbhatti8580]

Perfect! Also I was afraid of painull registering with regsvr for calendar control and it was producing error on user's pcs.

[@jaspeeg]

Great tutoria

[@VBAA2Z]

Thanks!

[@lehuynhuc6492]

Good project Sir

[@VBAA2Z]

Thanks

[@cigalahdn4676]

Thanks

[@VBAA2Z]

Welcome

[@djunabug]

With all your UI prowess, have you found a way to change colors of TreeView Controls?

[@VBAA2Z]

So sure I understand. Any specific control?

[@djunabug]

Your thumbnail for this video appears to show a TreeView with a purple background. Is this correct? And was it set using code? I can’t find any color settings via the control properties window.

[@VBAA2Z]

Thumbnails are for representational purposes only. For custom TreeView we can use labels and images to create one like that however it'll not be dynamic as actual TreeView control

[@momosolo630]

I need source please

[@VBAA2Z]

please check video desc.

[@amitshah1008]

goodone

[@VBAA2Z]

thnks Amit

[@MartinRosenkildeJensen]

Hi great video, and amasing what you can do with treeview. But i get the Error Run-Time Error '35603' Invalid Key every time. Sub GetPVData() Dim dic As Scripting.Dictionary Dim dKey As Variant Dim ArrIndex As Long Set dic = CreateObject("Scripting.Dictionary") PVData = Range("C4:e500") UserForm1.TreeView1.Nodes.Add , , "PV", "PV" For ArrIndex = 1 To UBound(PVData) If Not dic.Exists(LCase(PVData(ArrIndex, 1))) Then dic.Add LCase(PVData(ArrIndex, 1)), Nothing UserForm1.TreeView1.Nodes.Add "PV", tvwChild, PVData(ArrIndex, 1), PVData(ArrIndex, 1) End If Next ArrIndex End Sub Can someone maybee assist what is wrong?

[@VBAA2Z]

The "Run-Time Error '35603' Invalid Key" typically occurs in VBA when you're trying to add a key to a Dictionary that already exists. In your code, you are attempting to add keys to the Dictionary dic, and it seems that there may be duplicate keys in your data, causing this error.

[@ArezkiKacer-w2k]

I'm sorry. You're explaining to yourself not to others

[@VBAA2Z]

I get that sometime

[@JoshKingiu]

thank you

[@VBAA2Z]

You're welcome

[@BachXuanHien]

Thanks

[@VBAA2Z]

Welcome

[@ekilunewmai7033]

Thanks

[@VBAA2Z]

No problem