No data appears in ranges

Very strange! When a cell on the AnovaSingleFactor sheet is changed, Excel things that lstCategory on the Menu sheet has been clicked!

Aha!!! Remember the AnalysisToopak bug I talked about in the other thread. Well it occurred to me that given this problem that maybe it was the name of the listbox that was the problem. In other words the AnaysisToolpak may have something internal by that name. So I changed the name of the listbox to lstCat and did a mass-change in the code to reflect that change and no more disappearing text.

After making the change I bet you can now remove the dangerous workaround I put in the code in our other thread.

This code is being triggered when you enter anything in that range.

Public Sub ResetIndicators()
ActiveSheet.Range("A10:A" & LAST_MENU_ROW).Value = ""
End Sub

Select allOpen in new window

It clears column A from row 10 to LAST_MENU_ROW.

I think this code is being called by the Click event of a listbox called lstTopic on the sheet 'Main Menu'.

That listbox is populated from a range on the 'Topics' worksheet.

I'm still trying to figure out why a change on the 'AnovaSingleFactor' is causing this behaviour.

MartinLiss

When I look at the Call Stack it seems to be the click even of lstTopic that is triggering the ResetIndicators Sub

When I look at the Call Stack it seems to be the click even of lstTopic that is triggering the ResetIndicators Sub

Yes I know but I bet if you remove the AnalysisToolpak add-in that that doesn't happen.

I don't have the Analysis Toolpak installed.

OK, I lose that bet but changing the listbox name solved the problem.

Weird, I tried changing both listbox names, on the sheet and throught the code but the range was still cleared.

Here's a working workbook.
Analysis-Tools-Revised-4-22b.xlsm

Martin

Even weirder, that workbook still clears whatever I type in the range on 'AnanovaSingleFactor'.

I must be doing something wrong.

When you newly open the workbook what do you see in cell B22 on the AnovaSingleFactor sheet?

It says 'proof', is that what you expect?

Yes. I was able to add that after changing the name of the listbox.

I thought the problem was in A19:A29 (or A9:A29)?

In the attached workbook go to the second tab range in the range A19:A29 and enter anything - on my system it simply disappears.

Oops! Back to the drawing board.

This works. I copied sheets Main and AnovaSingleFactor, deleted the original ones and renamed the new sheets to match the old ones.
Analysis-Tools-Revised-4-22c.xlsm

Martin,
I downloaded your workbook, saved it on my local drive, closed Excel and re-opened your saved file. On the AnovaSingleFactor worksheet I re-entered information from A20:A23 and A25:A28. Great! Went back to the Main tab and reset the rows frozen from row 8 to row 10. Added a double-click event on the Topic and added the code you had when the workbook opens then saved the file and closed Excel.

Re-opened the workbook and got this error from Workbook Opened: "Run-Time error 438 at ".lstCategory.ListIndex = 0"
Clicked through the error then selected the first Topic and got this error:

MsgBox "The 'Go To Selection' sheet name for your selection needs to be added to sheet 'SheetNames'".

Clicked on the Topic "test" then double clicked on the first topic. It went to the AnovaSingleFactor worksheet. When I put some text in A19 and pressed enter everything that was in A20:A23 and A25:A28 was now gone.

The changed file is attached.
Analysis-Tools-Revised-4-22c.xlsm

My apologies. You are experiencing problems because I changed the name of lstCategory to lstCat. You can change it back or just change your new code. If you change it back you need to change both the name of the control and all the references in the code.

Apology not necessary - will that take care of the missing text also?

Ok - got it changed, however, when I type in my sheet name "AnovaSingleFactor" on line 10 of SheetNames it disappears, thus I get the error that I need to add a sheetname. Could this be related to the other missing text problem for Column A that I still have?

Try doing what I did with the other sheets. copy the SheetNames sheet, delete the old one and rename the new one to match the old.

OK...I though that the last file you submitted had that done. No problem. This has been a nightmare for you but thank you. I'll let you know if problems are not resolved.

No nightmare. No problem at all.

Martin,
Let me open a new question. I did what you suggested regarding lost data "This works. I copied sheets Main and AnovaSingleFactor, deleted the original ones and renamed the new sheets to match the old ones." thread # 40016341. The text in "Sheet Names" did not disappear. I then put in the text for A19:A29 and those remained. It was when I loaded the Analysis ToolPak did A19:A29 was deleted, however the text in "Sheet Names" was still there.
Are you OK with that?
Frank

I'm sorry but I don't understand what you are asking. I particularly don't understand this part.

It was when I loaded the Analysis ToolPak did A19:A29 was deleted, however the text in "Sheet Names" was still there.
Are you OK with that?

My wife tells me the same thing..
There was the problem that text entered in the "AnovaSingleFactor" worksheet range A19:A29 disappeared as well as the text in "SheetNames" in Column A.
I copied the worksheet "Main" and "AnovaSingleFactor" to a new workbook, deleted them from the original workbook then moved them back.  In Design mode I then entered text in the "AnovaSingleFactor" from A19:A29 as well as "SheetName" at A10 "AnovaSingleFactor". At that point everything look great! (I've attached that workbook before going any further).
Here's problems:
Open the attached workbook and select Design mode. Then open the Analysis ToolPak select  Anonva: Single Factor and fill in the ranges and click OK. Everything is still there.
Disable Design mode and select any cell. Enter some text and press enter. Everything from A19:A29 disappears. I'm not sure the Analysis ToolPak has anything to do with the problem.
Secondly, when I select a topic from the Main tab to take me to a worksheet the GoToSelection and reports that
"The 'Go To Selection' sheet name for your selection needs to be added to sheet 'SheetNames'".
Revised-Analysis.xlsm

I removed the toolpak and it still happens so it doesn't have anything to do with the toolpak. I'll continue to look at it.

Thnaks

Debugging shows that the listboxes on Menu are being clicked and it bugs me that I can't figure out why that's happening, but by adding my workaround to lstTopic_Click (and improving it in both places) it seems to resolve the problem. All changes are marked with 4-23a.
Revised-Analysis-4-23a.xlsm

What a bear this has been. The one outstanding issue remains the error I get when I try to go to a topic. See below

In that picture it appears that you have not made a Topic selection but I'll look into it.

Understood -
A topic was selected or I there would not have been an error. I also selected Go To Selection command button. One of the things I'm trying to do before I ask help is step through the program to better understand the error, but I've got a long way to go.

One of the things I'm trying to do before I ask help is step through the program to better understand the error...

That's the best way to learn. Are you familiar with mymy article about debugging?

I've looked through it and have now printed it out to go over it step by step. Looks great, but I'm not surpised

Thanks. Could you please attach one of the workbooks from before you did the Anova/Analysis Toolpak stuff because I think some things in the current workbook have been changed and I'd like to see how it looked before?

The attached workbook is the oldest I have
AnalysisToolPakFinancialScientif.xlsm

Well my good man - I tried to beat it to death and it's ALIVE! No runs, not hits, no errors. Great job!!!
I hate spelling errors so I apologize for putting you through that.
I'm between jobs at this time so this project helps with my time. I'm excited about this workbook and the one I'm working on "Balanced Scorecards".
Martin, please accept my many thanks - I appreciate your hard work. I'll be back.
Have a great week!
Frank

Just a fantastic job! You're a true trooper and I am very grateful for all your hard work.

Thanks. I have some other ideas that I'm working on and if they work out I'll post them here.

First idea. Replace the Workbook_Open code with this. The modification automatically adds the names of the data sheets (like AnovaSingleFactor) to SheetNames so as to avoid future spelling mistakes and to make maintenance easier. If you add a new data sheet and you want to update SheetNames you can either close and re-open the workbook or just manually run the Workbook_Open sub.

Private Sub Workbook_Open()

Dim intIndex As Integer
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
With Worksheets("Menu")
    For intIndex = 10 To .UsedRange.Rows.Count
        .Cells(intIndex, 1).Value = ""
    Next
    .Activate
    .lstCat.ListIndex = 0
End With
ActiveWindow.ScrollRow = 10
Range("A1").Select
Sheets("Menu").lblDescription.Caption = ""
Sheets("Menu").lblDescription.Caption = ""
'******* 4-23b Start *******
Sheets("Menu").OLEObjects("GoToSelection").Enabled = False
'******* 4-23b End *********
'new
Dim lngIndex As Long
Dim intNextRow As Integer

intNextRow = 10
With Sheets("SheetNames")
    ' Clear any existing sheet names on SheetNames
    .Columns("A:A").EntireColumn.ClearContents
    For lngIndex = 1 To Worksheets.Count
        Select Case Sheets(lngIndex).Name
            Case "Menu", "Topics", "SheetNames"
                ' skip these
            Case Else
                ' Add the name of the sheet to SheetNames
                Application.EnableEvents = False
                Sheets("SheetNames").Cells(intNextRow, 1) = Sheets(lngIndex).Name
                intNextRow = intNextRow + 1
                Application.EnableEvents = True
        End Select
    Next
End With
End Sub

Select allOpen in new window

The second idea is more involved and so will take longer.

Do you want me to post another question?

Question: I do not understand the redundancy in the above code for lines 15 and 16

No. I owe you from previous questions where my accepted answers didn't work out.

When you and I were working on a version of your workbook that had several hundred sheets and forms, etc it was very slow to open and close. Do you happen to remember the change we made that speeded up opening and closing?

We went to the xlsb format
And instead of having hundreds of solution forms reduced it to one and et up a solutions tab with ranges.

Any chance you have a copy of the resulting workbook? If you know what question it was you could point me there because I probably attached a workbook.

No problem.
The question as posted was "Slow loading of Large workbook"
The thread was 39722267
The final distributed file is attached
Excel-Formulas-and-Functions.xlsb

I'm thinking about modifying the Menu tab but that workbook, being so large, is still too difficult to work with. Do you have a workbook that is smaller but is still fully functional with multiple categories and multiple topics?

You bet!. It saved as an xlsb but much smaller
See attached
Additional-Charting-in-Excel-201.xlsb

OK, in your current workbooks we use ActiveX controls on the Menu sheet to drive the processing. The problem with them is that they aren't designed to be used on a sheet and although we have code to "keep them in line" they're still a problem. We could use Forms controls instead, but there's two problems with them and that is that they lack a lot of functionality as compared to the ActiveX controls and in the case of the Listbox the text is very small and it can't be changed).

Also, IMO, the GoToSelection button and the "====>" are unnecessary and only add to code complexity.

So.... here's a version that doesn't use either ActiveX or Forms controls on the Menu sheet. To scroll to the first topic in a category you just select one of the items under "Scroll to a Category" and to go to the associated sheet you just double-click any cell under the "Topic", Description" or "Purpose" headings.

Note that the little images that you had for the topics got moved incorrectly but I didn't try to fix that.

Finally, I ran into a problem in that on the Topics sheet column "D" was supposed to be the "Dashboards" Named Range but it would not work properly until I removed the green color. Very strange.

All the changes I made, and there a lot of them, are marked with "Concept".
Concept.xlsm

Interesting - let me "chew" on this - a lot of work has been done by you, and hopefully, you've enjoyed it.
Do you mow yards? Mine needs it today

lol

Ok, so you don't do lawns.
Question now: On the Menu worksheet does it present a problem to replace "Scroll to a Category" with "Click on a Category" with 3 small command buttons directly below that takes the user to the first topic in that category?
It appears much faster though.

I'll give that a try.

You know if you just change the text from "Scroll to a Category" to "Click on a Category" that "clicking" one of the categories already takes you to the first topic in the category. Do you sill want buttons?

You sure you don't do lawns?
I made the suggested changes.
I like it - really I do like it a lot.
I'll go with it - any other suggested changes?

Well in Workbook_Open, just after the call to InsertCategories I add the descriptions to the Menu sheet automatically and I made a note that you could also do the same for Topics and Purpose which I think we're doing manually now. However, unless your are frequently going to be changing any of those 3 or the categories then you probably shouldn't do it automatically because it does take some small amount of time during the opening of the workbook. So actually you might want to consider commenting out everything after the Dim c As Range in that Sub or just put an Exit Sub there. In either case add a comment about what's going on.

OK...got the backyard mowed and after lunch and I will mow the front, then look at your suggestions.

In you post # 40019000 line 15 & 16 are the same so I have not made that change here.

Comment on ID: 40024813
In this workbook I am not expecting any more changes to Category or Topic Or Description but in the future I'm sure this will come into play. Observation noted

Yes, there should be only one of them.

I should have mentioned that you no longer need the SustainControlSettingsFunctions module.

OK...thanks and enjoy your weekend now
You've been very helpful, as always

Thanks.

Martin,
I've got a question to post. Do you have time?

Sure.

Thanks