Solved

No data appears in ranges

Posted on 2014-04-22
68
300 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 46

Expert Comment

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

Expert Comment

by:Martin Liss
ID: 40016209
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
ID: 40016216
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 33

Expert Comment

by:Norie
ID: 40016218
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 46

Expert Comment

by:Martin Liss
ID: 40016222
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
ID: 40016239
I don't have the Analysis Toolpak installed.
0
 
LVL 46

Expert Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

by:Norie
ID: 40016271
Martin

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

I must be doing something wrong.
0
 
LVL 46

Expert Comment

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

Expert Comment

by:Norie
ID: 40016288
It says 'proof', is that what you expect?
0
 
LVL 46

Expert Comment

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

Expert Comment

by:Norie
ID: 40016308
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 46

Expert Comment

by:Martin Liss
ID: 40016325
Oops! Back to the drawing board.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40016341
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
ID: 40016467
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 46

Expert Comment

by:Martin Liss
ID: 40016499
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
ID: 40016510
Apology not necessary - will that take care of the missing text also?
0
 

Author Comment

by:Frank Freese
ID: 40016529
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 46

Expert Comment

by:Martin Liss
ID: 40016534
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
ID: 40016566
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 46

Expert Comment

by:Martin Liss
ID: 40016611
No nightmare. No problem at all.
0
 

Author Comment

by:Frank Freese
ID: 40017787
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 46

Expert Comment

by:Martin Liss
ID: 40017822
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
ID: 40018045
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 46

Expert Comment

by:Martin Liss
ID: 40018092
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
ID: 40018143
Thnaks
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40018167
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
ID: 40018349
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 46

Expert Comment

by:Martin Liss
ID: 40018473
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
ID: 40018532
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 46

Expert Comment

by:Martin Liss
ID: 40018666
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
ID: 40018731
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40018741
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
ID: 40018755
The attached workbook is the oldest I have
AnalysisToolPakFinancialScientif.xlsm
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40018934
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
ID: 40018971
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
ID: 40018974
Just a fantastic job! You're a true trooper and I am very grateful for all your hard work.
0
 
LVL 46

Expert Comment

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

Expert Comment

by:Martin Liss
ID: 40019000
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
ID: 40019029
Do you want me to post another question?
0
 

Author Comment

by:Frank Freese
ID: 40019032
Question: I do not understand the redundancy in the above code for lines 15 and 16
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40019034
No. I owe you from previous questions where my accepted answers didn't work out.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40021470
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
ID: 40021615
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 46

Expert Comment

by:Martin Liss
ID: 40021625
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
ID: 40022625
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 46

Expert Comment

by:Martin Liss
ID: 40023118
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
ID: 40023141
You bet!. It saved as an xlsb but much smaller
See attached
Additional-Charting-in-Excel-201.xlsb
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40024072
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
ID: 40024650
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 46

Expert Comment

by:Martin Liss
ID: 40024656
lol
0
 

Author Comment

by:Frank Freese
ID: 40024684
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 46

Expert Comment

by:Martin Liss
ID: 40024689
I'll give that a try.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40024693
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
ID: 40024738
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 46

Expert Comment

by:Martin Liss
ID: 40024813
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
ID: 40024878
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
ID: 40025113
In you post # 40019000 line 15 & 16 are the same so I have not made that change here.
0
 

Author Comment

by:Frank Freese
ID: 40025123
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 46

Expert Comment

by:Martin Liss
ID: 40025124
Yes, there should be only one of them.
0
 
LVL 46

Expert Comment

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

Author Comment

by:Frank Freese
ID: 40025133
OK...thanks and enjoy your weekend now
You've been very helpful, as always
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40025140
Thanks.
0
 

Author Comment

by:Frank Freese
ID: 40030335
Martin,
I've got a question to post. Do you have time?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40030356
Sure.
0
 

Author Comment

by:Frank Freese
ID: 40031930
Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

821 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