Solved

No data appears in ranges

Posted on 2014-04-22
68
291 Views
Last Modified: 2014-04-30
Folks,
In the attached workbook go to the second tab range in the range A19:A29 and enter anything - on my system it simply disappears. By disappearing I actually mean that what I typed in vanishes. I've checked the format properties of the cells, the font color used and the format painter from other cells that work and nothing. However the information shows in the formula bar when I first type then it disappears when I go to another cell. This is only true for A9:A29
Analysis-Revised.xlsm
0
Comment
Question by:Frank Freese
  • 34
  • 27
  • 7
68 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Very strange! When a cell on the AnovaSingleFactor sheet is changed, Excel things that lstCategory on the Menu sheet has been clicked!
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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

Open 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.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
MartinLiss

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

Expert Comment

by:Martin Liss
Comment Utility
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.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
I don't have the Analysis Toolpak installed.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
OK, I lose that bet but changing the listbox name solved the problem.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Weird, I tried changing both listbox names, on the sheet and throught the code but the range was still cleared.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Here's a working workbook.
Analysis-Tools-Revised-4-22b.xlsm
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Martin

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

I must be doing something wrong.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
When you newly open the workbook what do you see in cell B22 on the AnovaSingleFactor sheet?
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
It says 'proof', is that what you expect?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Yes. I was able to add that after changing the name of the listbox.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Oops! Back to the drawing board.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
0
 

Author Comment

by:Frank Freese
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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.
0
 

Author Comment

by:Frank Freese
Comment Utility
Apology not necessary - will that take care of the missing text also?
0
 

Author Comment

by:Frank Freese
Comment Utility
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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.
0
 

Author Comment

by:Frank Freese
Comment Utility
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
No nightmare. No problem at all.
0
 

Author Comment

by:Frank Freese
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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?
0
 

Author Comment

by:Frank Freese
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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.
0
 

Author Comment

by:Frank Freese
Comment Utility
Thnaks
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
0
 

Author Comment

by:Frank Freese
Comment Utility
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

go to selection
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
In that picture it appears that you have not made a Topic selection but I'll look into it.
0
 

Author Comment

by:Frank Freese
Comment Utility
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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?
0
 

Author Comment

by:Frank Freese
Comment Utility
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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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?
0
 

Author Comment

by:Frank Freese
Comment Utility
The attached workbook is the oldest I have
AnalysisToolPakFinancialScientif.xlsm
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
The name on SheetNames for AnovaSingleFactor was spelled wrong.  I also made a couple of other minor changes and marked them with 4-23b
Revised-Analysis-4-23b.xlsm
0
 

Author Comment

by:Frank Freese
Comment Utility
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
0
 

Author Closing Comment

by:Frank Freese
Comment Utility
Just a fantastic job! You're a true trooper and I am very grateful for all your hard work.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Thanks. I have some other ideas that I'm working on and if they work out I'll post them here.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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

Open in new window



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

Author Comment

by:Frank Freese
Comment Utility
Do you want me to post another question?
0
 

Author Comment

by:Frank Freese
Comment Utility
Question: I do not understand the redundancy in the above code for lines 15 and 16
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
No. I owe you from previous questions where my accepted answers didn't work out.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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?
0
 

Author Comment

by:Frank Freese
Comment Utility
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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.
0
 

Author Comment

by:Frank Freese
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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?
0
 

Author Comment

by:Frank Freese
Comment Utility
You bet!. It saved as an xlsb but much smaller
See attached
Additional-Charting-in-Excel-201.xlsb
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
0
 

Author Comment

by:Frank Freese
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
lol
0
 

Author Comment

by:Frank Freese
Comment Utility
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I'll give that a try.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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?
0
 

Author Comment

by:Frank Freese
Comment Utility
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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.
0
 

Author Comment

by:Frank Freese
Comment Utility
OK...got the backyard mowed and after lunch and I will mow the front, then look at your suggestions.
0
 

Author Comment

by:Frank Freese
Comment Utility
In you post # 40019000 line 15 & 16 are the same so I have not made that change here.
0
 

Author Comment

by:Frank Freese
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Yes, there should be only one of them.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I should have mentioned that you no longer need the SustainControlSettingsFunctions module.
0
 

Author Comment

by:Frank Freese
Comment Utility
OK...thanks and enjoy your weekend now
You've been very helpful, as always
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Thanks.
0
 

Author Comment

by:Frank Freese
Comment Utility
Martin,
I've got a question to post. Do you have time?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Sure.
0
 

Author Comment

by:Frank Freese
Comment Utility
Thanks
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now