Method 'ListFillRange" of object IMdcList failed

Folks,
I am very grateful to all that have tried to resolve a continuing problem I've been having regarding "Run-time error'-21474178484/80010108'
Here are screen shots of the events as they appear when I'm trying to load the "Analysis ToolPak"
runtime errorI open  Debug  and see this:
error lineThen I close and see this:
stopped
I'm truly at a loss. I've paired down the workbook to bare essentials. The first attached file is the workbook. The second is a larger view of the error.
I have looked across the Internet for help and there is so little  that relates to my problem. I know many of you have helped and I really appreciate it. The workbook maybe corrupted and I'll need to recode in a brand new workbook. But I'd like to avoid that if possible.
Thanks to all
Analysis-Revised.xlsm
code-with-error.png
Frank FreeseAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Not part of that problem but in Developer|Macros the 'Macros in:' choice should be set to 'This Workbook' not 'All open workbooks' and you have "population" spelled incorrectly as "peopulation", and in the heading of the Menu sheet you have "Statistics" spelled incorrectly as "Stastistics".

I'm attaching a workbook that works but still has problems. Look in the code for the several places that have been marked with 'new.

The main problem I think was that both the code name and the sheet name for the AnovaSingleFactor were wrong. In case you are not aware, the code name of a sheet is the what you see on the left when you look at the VBAProject window. For example 'MainMenu' is the code name while 'Menu' is the sheet name (which is what's on the tab). In the case of AnovaSingleFactor, when I got your file the code name was AnovaSingleFactor1 (note the "1" at the end). To fix that I added the Reset_Sheet_CodeNames Sub and ran it manually. The bigger problem was that the sheet name had a space at the end so when the GoToSelection_Click code was run, that name (with its space) could not be found on the SheetNames sheet.

Other changes:
1) I added Public intCatFactor As Integer in modVisible(it could have been in any code module). I saw that you added a Dim for it in lstCategory_Click but it has to be global in scope. It's important to remember that variables defined in a Sub or Function can only be used  in that Sub or Function.

2) It was necessary for me to add an On error Resume Next statement in MainMenu Worksheet_Activate. I don't understand why that was needed but sometimes you just have to be pragmatic.
Analysis-Tools-Revised-by-Marty.xlsm
0
 
Martin LissOlder than dirtCommented:
So are you saying that even with both versions of the Analysis Toolpak installed, you still have a problem?
0
 
Frank FreeseAuthor Commented:
I'm afraid so.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Martin LissOlder than dirtCommented:
OK but just in case, please verify that in your Excel that you do in fact have both installed.
0
 
Frank FreeseAuthor Commented:
I uploaded what I sent in this thread and saw where both versions of Analysis TookPak are checked. The uploaded one crashed. I don't even understand why this problem is showing up at all.
Should I recoded into a new workbook and see what happens?
0
 
Martin LissOlder than dirtCommented:
In the workbook you just posted you are missing the module that contains the RefreshControlsOnSheet macro so the workbook is hard to use.
0
 
Frank FreeseAuthor Commented:
After church I'll send you a workbook with that module. Sorry about that
0
 
Martin LissOlder than dirtCommented:
No need to do that since I was able to get it from a previous workbook.
0
 
Frank FreeseAuthor Commented:
Martin,
Thank you so very much. I truly appreciate your help, as always.
Frank
0
 
Martin LissOlder than dirtCommented:
I'm working on it and if I actually fix it this time I don't want any points.
0
 
Martin LissOlder than dirtCommented:
Ignore this.
0
 
Frank FreeseAuthor Commented:
I was anyway!
0
 
Martin LissOlder than dirtCommented:
Try this. Add lines 3 and 16 and run it with just the one 'Analysis Toolpak' add-in installed and see if it fails any more.

Private Sub lstCategory_Click()

    Application.EnableEvents = False
    
    Select Case lstCategory.ListIndex
    
    Case 0
        lstTopic.ListFillRange = ""
        lstTopic.ListFillRange = "Analysis"
        Sheets("Menu").lblDescription.Caption = ""

         intCatFactor = 10
    
    End Select
    
    Application.EnableEvents = True
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
Copy and replaced new code
Same error - same location
Not using VBA Analysis Pak.
0
 
Martin LissOlder than dirtCommented:
Sorry, but I'm out of ideas. I have no problem if you start a new question so as to attract other people to the problem. I'll stay out of the sway.
0
 
Frank FreeseAuthor Commented:
Thanks....I know you worked hard on this.
This may sound strange but I think I'll recode the workbook in a new workbook. There's not a lot here to code.
I'll let you know
0
 
Martin LissOlder than dirtCommented:
Just in case, please post the workbook with the most recent changes so I can take a look at it.
0
 
Frank FreeseAuthor Commented:
Here you go
Analysis-Revised.xlsm
0
 
Martin LissOlder than dirtCommented:
What I see is this
Private Sub lstCategory_Click()
Select Case lstCategory.ListIndex
    '******* Q1 Start *******
    Case 0
        lstTopic.ListFillRange = ""
        lstTopic.ListFillRange = "Analysis"
        Sheets("Menu").lblDescription.Caption = ""

         intCatFactor = 10


End Select


End Sub

Open in new window


which doesn't include the changes I mentioned in post ID: 40011869.
0
 
Frank FreeseAuthor Commented:
You code has been updated but there is something very confusing happening. Open the image below.VBProject
What I see are two similar objects when I open he VBA Editor in the Project window - two worksheets modules and two workbook objects under the same Project but labeled different. For example There's "ThisWorkbook" and "ThisWorkbook1". The same holds true for the worksheets. If you open "MainMenu1" there all the code. If you open "MainMenu" there's no code (everything that has a 1 added to the name has code).
I'm not sure how this happened.
0
 
Martin LissOlder than dirtCommented:
Sometimes when an error happens in Excel and it has to be shut down, the VB code module doesn't get deleted. If you completely close Excel and open it again you should be OK.
0
 
Frank FreeseAuthor Commented:
Guess what - did that and no change. I even restarted the pc.
0
 
Martin LissOlder than dirtCommented:
After closing Excel and opening it up again without selecting a file, do you still see ThisWorkbook1? If not then open up the bad workbook and export all the sheets and modules and then import them into a new workbook.
0
 
Frank FreeseAuthor Commented:
OK
0
 
Frank FreeseAuthor Commented:
Martin,
I loaded the original workbook into my Office Excel 2103 and guess what! No runs - no hits - no errors.
I'm slowly rebuilding a new workbook on my Excel 2010 to see if the error appears. This may be nothing but a bug in 2010
0
 
Frank FreeseAuthor Commented:
Martin,
I've re-typed the code into a new workbook but for the life of me I cannot get the topic  description to appear. I've checked and recheck my code and my ranges and sheets again and again. For those 500 points can you get this part corrected so I can continue coding, please, so I can go to the worksheet to test if I still am getting the error on loading the Analysis ToolPak.
Analysis-Tools-Revised.xlsm
0
 
Martin LissOlder than dirtCommented:
I'm looking at it now.
0
 
Frank FreeseAuthor Commented:
Sorry I'm so late in getting back. The doctor said I have kidney stone and it is very uncomfortable. I've looked at your notes and I can't believe I missed the obvious. After checking code I did not know what to do. After some rest tonight I'll look at everything.
Thank you Martin - I really am grateful.
0
 
Martin LissOlder than dirtCommented:
As I understand it, kidney stones are a lot more than "uncomfortable". I hope you can get ultrasound treatment or something to break it up.
0
 
Frank FreeseAuthor Commented:
The ultrasound indicated it should pass in a day or two - but it is indeed more than "uncomfortable"
Sitting up in bed I looked at what you had done - sometimes you look at something so long you cannot see it.
I'm accepting the work you did in repairing the workbook - BUT.....the error still exists.
This is the only link I found on the error:

http://www.dailyfreecode.com/forum/runtime-error-2147417848-80010108-25652.aspx

If you want to bow out I truly understand and will re-post.
Thank you Martin
0
 
Frank FreeseAuthor Commented:
You really stay with a problem and that why Expert Exchange is so good.
Thank you
0
 
Martin LissOlder than dirtCommented:
As I have a chances will continue to work on this but please verify for me that you.added the Application.EnableEvent lines and that it still failed.
0
 
Frank FreeseAuthor Commented:
Good morning,
I checked the code in the MainMenu Worksheet_Activate section:

Private Sub Worksheet_Activate()
Application.EnableEvents = False
'new
On Error Resume Next

Application.Run "RefreshControlsOnSheet", Sheets("Menu")
Application.EnableEvents = True
End Sub

Open in new window


Feeling like a horse kicked me in the left side - the kick I don't mind, the location should have been lower to the rear for some of the stupid things I've done on this project
0
 
Martin LissOlder than dirtCommented:
OK I really think I've fixed it this time.

As I'm sure you are aware, the program was failing in the lstCategory_Click event. When I used Debug and looked at the callstack to see how the code got there I saw that the previous procedure was this one which is in the code for the Analysis Toolpak add-in.
Sub ShowATPDialog(control As IRibbonControl)
    Application.Run ("fDialog")
End Sub

Open in new window

Why that should trigger the the lstCategory_Click event I have no idea and I think it must be a bug in the Analysis Toolpak. I can't be sure of that but I added a workaround.

Here are the changes I made which are all marked with "4/22a".
1) I removed the refreshControlsOnSheet procedure that I had added to modControls. It was a duplicate of what was already in the SustainControlSettings module and it was I believe the cause of the #2 problem I mentioned in post ID 40013500.

2) I removed the Application.EnableEvents code that you had added to the MainMenu Worksheet_Activate event. If you look at post ID 40011869 you'll see that that code should have been added to the lstCategory_Click event, but that's OK it since the code really didn't work in either place.

3) And finally, and this is the workaround, I modified the lstCategory_Click event to look like this.

Private Sub lstCategory_Click()
'new
'Dim intCatFactor As Integer
'******* 4/22a Start *******
If lstCategory.Text = "Analysis ToolPak" And lstTopic.List(0) = "Anova: Single Factor" Then
    Exit Sub
End If
'******* 4/22a End *********

Select Case lstCategory.ListIndex
Case 0
    lstTopic.ListFillRange = ""
    lstTopic.ListFillRange = "Analysis"
    Sheets("Menu").lblDescription.Caption = ""
    intCatFactor = 10
End Select

End Sub

Open in new window

The purpose of the lstCategory_Click code is to fill lstTopic when lstCategory changes. The problem was that this was being called by some unknown reason by the Add-in and it would fail again for some unknown reason when it got to line 13. Since at that time lstTopic is already filled there is no reason to fill it again and the code in lines 5 to 7 handles the situation. What it says is that if lstCategory is "Analysis ToolPak" and lstTopic is already filled with the right data as indicated by the first entry (List(0)) being "Anova: Single Factor" then exit the sub. That seems to work but it's dangerous because if you ever change "Analysis ToolPak" to anything else or you add a topic before "Anova: Single Factor" then the code will fail.

If you have any questions about this please ask.
Analysis-Tools-Revised-by-Marty.xlsm
0
 
Frank FreeseAuthor Commented:
Just 2 things
1) When I select topic there's no description in the label box
2) On the second tab (and this on really baffles me) from A19:A29 is where I have the instructions and explanation (you'll see those under column R). Every time I put anything there is disappears. I've checked the cell formatting and assigned it text - vanished. Assigned it general - vanished.
However, when I start Excel in the Safe mode there is no problem.
Everything else seems ok
Analysis-Revised.xlsm
0
 
Martin LissOlder than dirtCommented:
I'll look at it later today.
0
 
Frank FreeseAuthor Commented:
I deleted the rows and inserted new ones so the second problem has been solved.
Thanks
0
 
Martin LissOlder than dirtCommented:
It works for me as shown in the attached, but it's probably because I added a second topic. Note that I also froze the first 8 rows on MainMenu.
Analysis-Tools-Revised-4-22b.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.