Solved

Method 'ListFillRange" of object IMdcList failed

Posted on 2014-04-20
38
699 Views
Last Modified: 2014-04-22
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
0
Comment
Question by:Frank Freese
  • 19
  • 19
38 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40011691
So are you saying that even with both versions of the Analysis Toolpak installed, you still have a problem?
0
 

Author Comment

by:Frank Freese
ID: 40011697
I'm afraid so.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40011746
OK but just in case, please verify that in your Excel that you do in fact have both installed.
0
 

Author Comment

by:Frank Freese
ID: 40011763
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40011764
In the workbook you just posted you are missing the module that contains the RefreshControlsOnSheet macro so the workbook is hard to use.
0
 

Author Comment

by:Frank Freese
ID: 40011777
After church I'll send you a workbook with that module. Sorry about that
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40011782
No need to do that since I was able to get it from a previous workbook.
0
 

Author Comment

by:Frank Freese
ID: 40011845
Martin,
Thank you so very much. I truly appreciate your help, as always.
Frank
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40011847
I'm working on it and if I actually fix it this time I don't want any points.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40011854
Ignore this.
0
 

Author Comment

by:Frank Freese
ID: 40011863
I was anyway!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40011869
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
 

Author Comment

by:Frank Freese
ID: 40011880
Copy and replaced new code
Same error - same location
Not using VBA Analysis Pak.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40011906
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
 

Author Comment

by:Frank Freese
ID: 40011909
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40011912
Just in case, please post the workbook with the most recent changes so I can take a look at it.
0
 

Author Comment

by:Frank Freese
ID: 40011935
Here you go
Analysis-Revised.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40012057
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
 

Author Comment

by:Frank Freese
ID: 40012500
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Martin Liss
ID: 40012507
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
 

Author Comment

by:Frank Freese
ID: 40012511
Guess what - did that and no change. I even restarted the pc.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40012560
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
 

Author Comment

by:Frank Freese
ID: 40012639
OK
0
 

Author Comment

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

Author Comment

by:Frank Freese
ID: 40013424
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40013500
I'm looking at it now.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40013677
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
 

Author Comment

by:Frank Freese
ID: 40013776
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40013829
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
 

Author Comment

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

Author Closing Comment

by:Frank Freese
ID: 40013914
You really stay with a problem and that why Expert Exchange is so good.
Thank you
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40013970
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
 

Author Comment

by:Frank Freese
ID: 40014735
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40015336
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
 

Author Comment

by:Frank Freese
ID: 40015649
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40015706
I'll look at it later today.
0
 

Author Comment

by:Frank Freese
ID: 40015743
I deleted the rows and inserted new ones so the second problem has been solved.
Thanks
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40016172
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel - Scroll Speed 3 25
Populate data based ona  criteria 6 19
TT Auto DashBoard 4 26
Response to Macro Filter Fix 2 0
A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

706 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

15 Experts available now in Live!

Get 1:1 Help Now