VBA UI UX-16: How to use powerful TreeView Control in Excel UserForm
Вставка
- Опубліковано 27 сер 2024
- This series covers how to design a Professional User Interface using Office Apps.
Subscribe, Like, and Comment. 🙏Thanks for watching.
You can now support by buying any of the Projects or Source Code.
📥Downloads: pamaitech.com/...
👍 Support: paypal.me/pama...
✉️ Contact: contact@pamaitech.com
📌What to watch next:
Playlist: www.youtube.co...
Videos: www.youtube.co...
#VbaA2z
![[Treeview VBA] - Plano de Contas](http://i.ytimg.com/vi/KNKfJDkhJGY/mqdefault.jpg)
God bless you, I can only imagine how much time I took you to learn all this trick and skills
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.
Thank you!
Great training. Thanks so much. I am thinking Customers: Invoices: Invoice Items. I will try to put it together for a training. Thanks again
Thanks for visiting sir!
thanks again Pamai
you're welcome
Easy to understand and very helpful - thank you
Great to hear!
Wow your channel is just amazing.
Thank you so much 😀
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?
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.
Amazing! You are a wizard, and a generous one at that :)
Thank you kindly!
Thanks for another very useful video
You're very welcome!
another great episode thanks!
Glad you enjoyed it
Achi you are GENIUS.
Excellent examples and explanation. Easy to follow
Glad it was helpful! thanks for watching.
Thanks for the exemple!
My pleasure!
@@VBAA2Z i requer this example from your site, could you please send to me?
Thanks
thank you
You're welcome
Thanks
No problem
This is superb.🥰😍
thanks
Excellent. Thank you very much
Glad it was helpful!
thanks for watching.
This is really informative. thanks for sharing your knowledge.
Glad it was helpful!
Brilliant! Thanks
Glad you liked it!
The Video is very helpful, it would be nice if there was a description when an action is executed when child is clicked
check Private Sub TreeView1_Click() in project.
@@VBAA2Z Sorry, I can't find the source code / download the project 😢
@@manfredphol3732 be sure to check your spam folder
Its a great explanation. Thank you.
Glad it was helpful!
Great tutorial. Thank you so much
Glad you enjoyed it!
Hello, it's a great explanation, thanks, sorry the link provided is no longer available?
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
@@VBAA2Z thanks 🙂
@@williamarellano9586 Most welcome, thanks for watching.
Excelente conteúdo, muito obrigado por compartilhar o seu conhecimento.
great code.. good contribution.
Thanks!
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.
Good point.
perhaps create a unique ID with Country, City and ZipCode and display text can be duplicated
Great tutoria
Thanks!
excellente
Thank you, Cheers!
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.
Glad to hear!
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!
That is feasible, create/keep frontend ( Userform ) and database/workbook separate.
Users can open the frontend from their respective computer.
Good project Sir
Thanks
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.
Key must be unique in collection
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!
I'll try and cover how to create one in upcoming videos.
@@VBAA2Z Have you made this video yet, As I cannot find it in your videos.
@@VBAA2Zany update?
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.
With all your UI prowess, have you found a way to change colors of TreeView Controls?
So sure I understand. Any specific control?
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.
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
Download for this item does not appear to be available?
It should be in the list
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.
Please be sure to add a reference.
@@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!
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?
Most welcome. Here's the link vbaa2z.blogspot.com/2020/12/you-can-support-vba-a2z-by-simply.html
Hi, that was very generous of you,, thank you for your effort. Is there a way to auto extend the nodes without clicking on them one by one?
I think it is already demonstrated in the video
Do you have any videos on loading a Treeview directly from a sql recordset?
no, not in channel yet
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.
yes you can. Transfer all items for selected node and transfer to array and use it to apply filter.
Good afternoon, please post a video hoy I can search for a data within a treeview
what is the maximum number of nodes this control can have?
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.
it is not straightforward as it is VBA. try below.
vbaa2z.blogspot.com/2020/07/net-treeview-add-child-node-to-existing.html
Nice Lung Pamai! Is it supported both in 32 and 64 bit Excel?
Yes it does. I use 64bit Excel / O365 WIn10
Perfect! Also I was afraid of painull registering with regsvr for calendar control and it was producing error on user's pcs.
Como fazer Treeview interagir com multipage?
not sure I understand
@@VBAA2Z how to use treeview with multipage?
are you not able to add TreeView under multi page?
@@VBAA2Z I already got it, thanks you some much
goodone
thnks Amit
I need source please
please check video desc.
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?
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.
I'm sorry. You're explaining to yourself not to others
I get that sometime
thank you
You're welcome
Thanks
Welcome
Thanks
Welcome