Solved: treeview in worksheet

[crush]

hi, i have a treeview working in a user form. Rather than open a user form, I would like to position the treeview contol directly within a worksheet, so when the workbook is opened, the treeview control is populated.

is this possible?

i'm attaching an example of what i have working in the userform... i would like to have the same funcationality directly within the worksheet...

[tstom]

Sure. From the Control commandbar, select "More Controls" and browse for the treeview control. After selecting it, simply draw it out on your worksheet.

[crush]

thanks tstom, the problem is when i try to populate the tree, i don't know how to 'refer' to the treeview in the worksheet.

for the user form, i use the following:

With frmActivities.tvActivityTree
.Nodes.Clear

when i try the following, i get errors. i've tried different things but keep running into errors. do you have any clues?

ActiveSheet.Shapes("tvActivityTree").Select
With ActiveSheet
.Shapes("tvActivityTree").Nodes.Clear

[tstom]

These are just some of the ways to grab a reference to your control...


'using the ActiveSheet and WorkSheet class' OLEObjects collection
ActiveSheet.OLEObjects("tvActivityTree").Object.Nodes.Clear

'using the codename of the worksheet and tvActivityTree as a public property
'this is the one I usually prefer
Sheet1.tvActivityTree.Nodes.Clear

'using Worksheets collection and tvActivityTree as a public property
Worksheets("Sheet1").tvActivityTree.Nodes.Clear

'using Sheets collection and tvActivityTree as a public property
Sheets("Sheet1").tvActivityTree.Nodes.Clear

[crush]

excellent, that's what i was looking for, thank you very much.

i noticed that when i save the workbook, all the tree branches expand for some reason?

also, when add a new item to the tree, there is a lot of flashing and blinking as the tree 'reloads'

do you know of any tricks to stop the expanding tree on file save, and the flickering when i reload the tree data?

[Ken Puls]

I haven't used a treeview, so can't answer the expansion part, but for the flashing, try:

Application.ScreenUpdating = False

'Treeview code here

Application.ScreenUpdating = True

HTH,

[crush]

Hello Ken,

i have these statements in my code, but that does not seem to have any effect on the treeview control behavior.

i'm attaching an example i'm working with, if you open this and then save the file you'll see what i'm trying to stop happening. The same issues occurs when excel decides to autosave the file?

i'm not sure why all the nodes expand in this way - i'm hoping someone has noticed the same issue and can help me solve this... i can see this being a little annoying to the end users of this tool.

[Ken Puls]

Hi crush,

I took a look at your file, but can't see anything in the object browser that appears to control this. I googled, but the only related examples I found were in .Net, which I also am ignorant of.

Sorry I can't be of any help to you here. :(

[tstom]

Download the file to see the resultant behavior...
You may beed to edit the before save procedure.


'Replace this
Worksheets("Activities").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = TechnologyComboBox.Text
ActiveCell.Offset(0, 1).Value = ActivityTextBox.Text
'With this
With Worksheets("Activities").Range("A2").End(xlDown)
.Offset(1, 0).Value = TechnologyComboBox.Text
.Offset(1, 1).Value = ActivityTextBox.Text
End With
'Commented out
'Sheets("Activities").Select
'Added this
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False
Cancel = True
Me.Save
LoadTree
Application.EnableEvents = True
End Sub
'you will need to clean out the Select Statements in the following sub
Sub btnDelete

[Ken Puls]

Sorry, I should have quoted in my reply above. My comments were strictly related to the issue of the expanding treeview portion. I didn't look in to the flashing, as I figured it was less of an issue. I'm glad tsTom addressed that at least. (In truth, it never really flashed all that much on my PC at all.)

Tom, you have more experience with treeview than I do. Any thoughts on why the Treeview expands when you click the save button? It seems to do it in the file you uploaded as well.

[tstom]

Ken.
I don't know. I ran SPY++ on the treeview window and there were about 5000 user-defined window messages sent and recieved during a single save operation. Since these messages are user-defined, I have no clue as to what they are doing.

Crush.
Sometimes the LockWindowUpdate API function offers an improvement over Application.ScreenUpdating. In this case, the results are negligible for the most part. Nevertheless, there was a bit less flashing using this code in your workbook class.


Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
LockWindowUpdate Application.Hwnd
Application.EnableEvents = False
Cancel = True
Me.Save
LoadTree
Application.EnableEvents = True
LockWindowUpdate False
End Sub

[crush]

hey chaps, sorry for the late reply... and thanks so much for the help. i tried the methods you gave and now when i click Save, the tree no longer expands. there is a new problem though... now i cannot use Save As? when i choose save as, the model behaves like a save. is there anything i can do to make the save as work?

[tstom]

Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
LockWindowUpdate Application.Hwnd
Application.EnableEvents = False
Cancel = True
If Not SaveAsUI Then
Me.Save
Else
Application.Dialogs(xlDialogSaveAs).Show
End If
LoadTree
Application.EnableEvents = True
LockWindowUpdate False
End Sub

[malik641]

Hey Tom,

When I try closing the workbook, it asks me if I want to save even after I pressed save. At first I said "oh, ok. He forgot Me.Saved = True at the end of the BeforeSave procedure (maybe because of the 'LoadTree' call)"...but even with ThisWorkbook.Saved = True when I pressed close, it still asked me if I wanted to save and if I pressed save it will repeatedly ask that.

That's really strange to me, what could be causing the workbook to change the Saved property back to False? (I checked the event in real-time after setting the property to True, and it displayed False)

I managed to come up with this example. It takes care of the save problem. I've also added a procedure that stores the expanded nodes in a new worksheet "Tracking" and when you save it will reload the tree to keep them from all expanding and then run a procedure called "SetNodes" to keep the data looking like it was when you saved / closed the workbook. Remove it if you like, but I feel it's more user-friendly like this...people save workbooks knowing they won't close it for a while, so I'm sure they'd like to keep seeing what they were looking at before they saved it. Not to mention when you add a item to a node it will expand that node on the rebuild to show the new data.

Don't mind the following code in the ThisWorkbook module:
Application.DisplayScrollBars = False
Application.DisplayScrollBars = True When I opened the workbook (before this code) the treeView had HUGE font sizes in it for some odd reason....but if I scrolled down it would be fine. So I used this just to make the screen repaint itself through code (couldn't find an Application/Sheet/Workbook property to repaint the Sheet).

Hope this helps :)

[crush]

tstom and malik, thank you so much for your help on my tree view questions. i'm really impressed with the response i've had from you - your help has been outstanding!

i think the treeveiw is working better than i expected! kindest regards
chris

[malik641]

Chris,

:thumb No problem! Glad to help.

Give it some test runs to make sure it's everything you need. And if this thread is solved, don't forget to mark it as such by using 'Thread Tools-->Mark Thread Solved-->Perform Action' when inside the thread.


I noticed you used Select a lot in your code. Just so you know you don't need to use Select for what you are doing. And by removing it, it will speed up your code as well. Take a look at this article by Johnske to help speed up your code Optimize Your Code (http://vbaexpress.com/forum/showthread.php?t=9882) (your code is fast enough, but it's a great source of good programming practice and for future reference).

:)

[crush]

Hello Malik thanks for the code practice tips - yes you are right, i'm quite new to programming and sometimes i do the only thing i can get to work. i probably do a lot more unecessary stuff than i really need to - i think the tips will help me in some cases.

the only thing that doesn't seem quite right now, is the 'autosave' feature. when autosave kicks in the nodes of the tree expand again?

[malik641]

Hey Chris,

I've noticed a few errors since your post about Autosave...please give me some time to work some things out. Excel is acting strange when it comes to saving the TreeView.

I hope it can wait.
Regards,
Joseph

[crush]

Hey malik, i'm glad you saw this happening too... this treeview control certainly behaves weird when it comes to save and save as? i am working on many other parts of my larger project, if you do find out any way to solve this that would be so great! thanks for all your help with this.

kindest regards
chris