Multipage/Treeview Expansion problem

Greetings. I posted this issue on another forum a couple of days ago but got no replies, so I thought I would see if anyone here could help.

I'm developing a userform with a multipage object on it with four tabs. Three of them have treeview (tvw) objects. I noticed a delay in switching from one tvw tab to the other, but didn't think much of it until I switched to the 4th tab, and then back to one of the others. In that case, all of the nodes in the tvw showed up as expanded, though they hadn't been before.

To find what was triggering that, I put debug stops on the tvw Expand and Collapse events. I found that when I switch from one tvw to another, for each node that was collapsed it fires the Expand event and then the Collapse event. For each node that was expanded, it fires the Expand event twice. This explains the delay I had noticed, and happens when I go from one tvw tab to another. When I go from the non-tvw tab to a tvw tab, it does the same thing, except instead of expanding and then collapsing the collapsed nodes, it expands them twice also, so they end up expanded.

I can't find anything in my code that would trigger these events, and can't account for the difference in which tab I'm coming from. I looked at the call stack during the events, and they show [<Non-Basic Code>] below the event. I suppose I could live with the delay, but I don't want the nodes expanding themselves.

All of this happens before the Multipage Change event fires.

Any ideas? Thanks in advance!

interesting. Can you attach a sample and I will have a look.

I meant of the form and the code

hi Natefarm,

Originally Posted by natefarm

Greetings. I posted this issue on another forum a couple of days ago but got no replies, so I thought I would see if anyone here could help...

Can you please upload a link to your crosspost (both in this thread & the other Forum)?
(see Rule 8)
- It may be unlikely, but it is still possible that you may yet get replies in the other Forum & it could save Tony some work

Rob

I'll try and get a look tomorrow

hi Nate,

Edit: Thanks for posting the link to your other thread /end edit

I've got a couple of suggestions & then I'm going to leave you in Tony's hands as I suspect he'll be able to help you out more effectively than I can.

Originally Posted by natefarm

...To find what was triggering that, I put debug stops on the tvw Expand and Collapse events. I found that when I switch from one tvw to another, for each node that was collapsed it fires the Expand event and then the Collapse event. For each node that was expanded, it fires the Expand event twice. This explains the delay I had noticed, and happens when I go from one tvw tab to another. When I go from the non-tvw tab to a tvw tab, it does the same thing, except instead of expanding and then collapsing the collapsed nodes, it expands them twice also, so they end up expanded.

My 2 cents:
- Try scattering

Please Login or Register  to view this content.

through your code to see if it prevents the "double firing" and don't forget to reset it with

Please Login or Register  to view this content.

- I'd recommend limiting the code in the Userform module to code that relates to the userform and moving all the other code into a normal module. See Chip's page for more explanation: http://www.cpearson.com/excel/codemods.htm

Here are some suggestions which could take a bit more work (eg a bit of a re-write!):
- Try to limit your "hits" on the spreadsheet, by pulling an entire range into VBA as an array and then iterating through the array, rather than getting info cell by cell. Here are some general links/examples:
http://www.excelforum.com/excel-prog...ml#post2289689
http://www.excelforum.com/2289718-post139.html & Shg's example later in the thread: http://www.excelforum.com/excel-prog...ml#post2289718
- In fact, there are good comments right throughout the thread.
- Try to remove any ".select" or ".activate" lines in your code and act on the object directly without selecting it first. For example:

Please Login or Register  to view this content.

hth
Rob

Rob,
Thanks for the good suggestions. I'm always looking for ways to code more efficiently.

Regarding the EnableEvents idea -- excellent suggestion. I hadn't used that method before but can certainly remember times when I could have used it. I doesn't seem to help in this case, though, because when you click the tabs, the Expand and Collapse events get fired before Multipage1_Change occurs, and if I turned off EnableEvents when no code is running, I wouldn't be able to click the tabs or do anything else, right?

For a clear example, click the Users tab, click the Show button, then click the Updates tab, and the Users tab again. All the nodes will be expanded.

hi Nate,

Sorry, I'm busy this weekend so I may not get to this until Monday. Hopefully Tony or others can help out. Keep working at it & if you make any changes based on my links or suggestions, can you please throw a new version into the thread for us to look at?

hmmm... The more I think about this, the more I get a slight feeling of deja-vu. I think I may have had some issues with events multi-firing when I set up a userform a while ago. I'll see if I can find the code...
I think that I got around it after a lot of [F8]ing through the code, with very careful placement of

Please Login or Register  to view this content.

, and including an optional boolean variable that I flipped as needed & then tested within each event macro.

Goodluck
hth
Rob

Nate,

I'll have a go at placing some of the disable events snippets when I can open the file but at the moment...
When I open the spreadsheet I see a few errors relating to "Spreadsheet1" (see below) as the Workbook_Open macro tries to load the userform. I can't find any declaration for this object (is it set as Reference?) & I don't want to incorrectly guess what it is meant to be. What is "Spreadsheet1" meant to refer to/ how is it defined in your actual file?

Please Login or Register  to view this content.

----------------
I've dug out some code from my old file to show you how I used a switching principle because I probably didn't explain it very well. Although the code isn't any clearer you may understand the concept better by seeing it in action. I can't upload the file as it belongs to my old company (plus it was about 100hrs of work with lots of learning as I went!).

The following code snippets show the use of some boolean flags:
- "ini" is a global variable which identifies if the code is being "called" in response to the Userform being activated.
- I also used the Tag property of a combobox (SummaryUF.SUFLineNum.Tag) as another flag.
All the code is unchanged from my original file except for where I have marked some of the uses of the boolean flags with "'****". Note this means I have some of my own historical doubts included as "'###" comments.

Please Login or Register  to view this content.

hth
Rob

hi Nate,

Originally Posted by natefarm

Rob,
The Spreadsheet1 object is a Microsoft Office Spreadsheet 10.0 control. It fills the right half of the userform (multipage is on the left), and is used to display detail information on any node that they click in the treeviews. It is irrelevant to this issue, so any related code can be commented out, as can any references to ssObj, which is the variant used to reference Spreadsheet1.
-Nate

Sorry, it looks like I can't help because I can't load the form^.
I'm running Office 2007 & I can't see "Microsoft Office Spreadsheet 10.0" in my available references + when I comment out all the code relating to "Spreadsheet1" & "ssObj" I hit a new compile error. The new erros relate to the Word related constants (eg wdStory & wdBlack ect) & I assume this is because I also need another reference to a "MS... 10.0" library.

Originally Posted by natefarm

I've used code similar to yours in the past. It is useful to prevent looping when the code in an event triggers the same event. I'm not seeing the relevance here, when the triggering mechanism is unknown. I'd be happy to be proven wrong, though. It happens a lot

Ahhh, that's good - I wasn't sure how I could try & explain it any better.
I think the key fact here is that the trigger mechanism is unknown*, so anything could be relevant - couldn't it?

Originally Posted by natefarm

...
I can't find anything in my code that would trigger these events, and can't account for the difference in which tab I'm coming from. I looked at the call stack during the events, and they show [<Non-Basic Code>] below the event. I suppose I could live with the delay, but I don't want the nodes expanding themselves.
All of this happens before the Multipage Change event fires.
Any ideas? Thanks in advance!

*It may seem dumb, but it took me quite some time to "know" what the trigger was when I was coding for my project. If I uncomment the "Stop" at the start of the Private Sub OUFLineNum_change() & look at the call stack, I also see "[<Non-Basic Code>]" & that was confusing.

^ If you'd like to recode/use late binding & upload a new file (or alternatively teach me where to get the References from) so that I can load the form, then I'll have a go at investigating further - otherwise I guess we'll have to hope someone else can help you...

Sorry I don't like admitting defeat.
Rob

Rob, I have attached a new version that has all references to Spreadsheet1 and the Word app removed, as well as one of the tabs related code, just to simplify things.

I did quite a bit of experimenting with adding events to the treeview and multipage objects, and putting in stops & msgboxes to track down the trigger, but I'm still baffled. I did confirm again that the expand/collapse events are firing before the MultiPage1_Change event:

Please Login or Register  to view this content.

The DisplayItemCounts subroutine, called above, displays the number of visible & non-visible nodes in the tvObj variable.

DisplayItemCounts is also called from the node Collapse/Expand events so when a user collapses/expands a node, the visible item count will be updated on the display. However, since the Collapse/Expand events are being fired from an unknown source prior to the Change event above, the tvObj variable has not yet been reset, and DisplayItemCounts gets called twice for each node in the previous value of tvObj, and then once by the above code after tvObj gets updated, as intended. Since a user could either click to expand/collapse a node, or click a tab (triggering the multiple expand/collapse events without giving us a chance to intervene), I don't know where we could set a boolean that could be used to circumvent the event in the case of a tab click.

I did narrow down what (but not why) causes the nodes to all expand. If I comment out the line in the ParentsAllExpanded function where the function calls itself, then the nodes stay as they were, though it still fires the expand & collapse events for each node.

Sorry for the lengthy post. Don't pull your hair out over this. It's probably not worth it. But I do appreciate your help.
-Nate

hi Nate,

I love a challenge

I think you forgot to attach your new version of the file...?

Edit: can you please fix your last post to include Code tags?

hth
Rob

hi Nate,

I'm having problems with the new file - it's crashed my excel (2007) three times so far & is giving me a "Path/File access error" which (when I try to debug) appears to relate to no userform existing. However, Excel doesn't give me the chance to actually check if a userform exists in the file.

Do you want to try again?

Rob

hi,

I opened the file with macros disabled & could see the userform. When I tried to compile ([alt + D + L]) the project I needed to dim 2 Word related variables & comment out a few lines that were trying to call some of the deleted subs. Unfortunately, it was to no avail, as when I reopened the file with macros enabled Excel crashed again.

Can you please fix post # 18 to include Code tags?

I'll see if I can get anyone else to check your file out...

Rob

Thanks for fixing the code tags Nate

The xlsm version is much better for me - it works!

I've got a few other bits & bobs to do so I probably won't check this out again until tomorrow... Plus, before I saw your xlsm, I sent a request out to some of the more knowledgeable guys & fingers crossed that in the mean time, they may be able to give you a quick comprehensive answer.

Rob

A few questions and clarifications:
1. Office 2007 discontinued the OWC which is probably why Rob is having issues here.
2. If your users mostly have 2003, is there a reason for using the Spreadsheet10 rather than 11?
3. Application.Enableevents has no effect on userforms. You have to use the boolean variable method.
4. I will download the workbook and see if I can see anything that leaps out, but it may not be today. Fear not, though, as the bat signal has been sent out by Rob to several people!

Edit: I tried your 2007 version, and saw no issues with it. I downloaded the 2003 one, fixed some declarations and calls to non-existent subs, then ran Code Cleaner on it. When I reopened it, I didn't see any issues with the nodes expanding when switching tabs. It did however initially crash Excel for me as it did for Rob but seemed fine after cleaning. Perhaps that will work for you?

Thanks Romperstomper

You've certainly clarified some stuff for me.

Rightio, I'm back to teaching myself some ppt VBA for my new job.
Then, guess what...
Tomorrow I'm doing a training course for Access!
As I think you've said before, the 2007 Access UI is not flash, but because I haven't touched Access since Uni, it's all new to me anyway.


Nate, here's a link to the Code Cleaner.

Rob

OK. I have recreated the issue.
I'm not 100% sure of the cause but I think it is down to the control being completely redrawn as you change tabs. I haven't tested, but you may be able to solve it by using a tab strip instead of a multipage, and simply showing/hiding the treeviews as required.
However, possibly simpler to implement is the Boolean variable trick - we are actually aided by the fact that the events trigger before the Multipage_Change event. What you need is to add:

Please Login or Register  to view this content.

to the declarations, then alter the Multipage_Change code to:

Please Login or Register  to view this content.

then alter your Expand/Contract events:

Please Login or Register  to view this content.

for the Users tree and:

Please Login or Register  to view this content.

for the Groups one.

Only did a quick test but it seems to do the job.

Glad to help - that was quite fun!

Phew!

I'm pleased I sent out the bat signal - Thanks RomperStomper

Nate, can you please mark the thread as solved?
Also, it would be great if you could link through to the VBAX Forum to let them know the problem is solved.
Now, like you, I just need to follow the logic & learn from RS's code.


Thanks
Rob