Run time error on list box?

Folks,
I keep getting this error:
Run time errrorThere's no particular pattern
I've attached the file.
I'm to the point of completely re-coding this in a new workbook.
AnalysisToolPakFinancialScientif.xlsm
Frank FreeseAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

maybe try instead of ListTopic.ListFillRange = "Analysis"

        strAddress = Evaluate("=ADDRESS(ROW(Analysis),COLUMN(Analysis))&"":""&ADDRESS(ROW(Analysis)+ROWS(Analysis)-1,COLUMN(Analysis)+COLUMNS(Analysis)-1)")
        lstTopic.ListFillRange = "Topics!" & strAddress

Open in new window

to see if it evaluates the Named Range correctly

Regards
0
 
Rgonzo1971Commented:
HI,

Where does the error appear?

Regards
0
 
Frank FreeseAuthor Commented:
Let me give you an example because it is inconsistent.
When the workbook opens it goes o the first item under category. From there  you can go to whatever category you choose (right now I have only the first category and first topic almost completed. You can go to any sheet by double-clicking on the Topic or clicking on the Go To Selection, Description, or Function Name. That will take you to the desired worksheet - OK to here. Now let's say I wanted to launch the Analysis ToolPak. At that point the error appears as seen below:

Error
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
Martin LissOlder than dirtCommented:
There's no Data tab and no data to fill the listboxes.
0
 
Frank FreeseAuthor Commented:
Martin,
How do I fix this? I've been using the code you provided from the first workbook that's and that's confusing me now.
Thanks,
Frank
0
 
Frank FreeseAuthor Commented:
Rgonzo1971,
Thanks - I'll look at this
Frank
0
 
Martin LissOlder than dirtCommented:
Hmmm. I closed the workbook and then reopened it and the data showed up. Let me see if I can reproduce the error.
0
 
Frank FreeseAuthor Commented:
Thanks...again, this does appear when I try to load the Analysis ToolPak but then sometimes it does not. When the Analysis ToolPak loads and I fill out the ranges - bingo - error?
0
 
Martin LissOlder than dirtCommented:
OK I don't have the Analysis Toolpak. Do I install "Analysis Toolpak" or "Analysis Toolpak VBA" or both?
0
 
Frank FreeseAuthor Commented:
Load the Analysis ToolPak add-in. Not the Analysis ToolPak VBA
0
 
Martin LissOlder than dirtCommented:
OK I've done that and I see that it fails once in a while. I'll try to figure out why, but I don't have a lot of confidence that I'll be able to help.
0
 
Frank FreeseAuthor Commented:
Truly frustrating....thanks Martin
0
 
Frank FreeseAuthor Commented:
Thank to all...
Rgonzo1971,
I entered your code and tried many times to see if the error reappeared. I closed the workbook, reopened it, tried again and again and NO ERROR. I'll need to better understand what you did but for now thank you
0
 
Frank FreeseAuthor Commented:
Folks,
Something strange happened in paradise today. Rgonozo1971 code solved the error problem but created a new problem in how the Analysis ToolPak works by returning to the first tab and selecting it's own data set. After I removed the range and went to the second tab I correctly put in the ranges. The problem I got was that the displayed data was not where it was suppose to be.
So I rem out that new code and returned to what I originally had. Now everything seems to be working as expected. The error is gone and the Analysis ToolPak is functioning as designed.
Go figure! Thanks again
0
 
Martin LissOlder than dirtCommented:
I thought I posted this already but apparently not. :(

In any case yesterday while I was testing I found that the formula for Named Range 'Analysis' was wrong. It should be

=OFFSET(Topics!$A$1,0,0,COUNTA(Topics!$A:$A),1)

and not

=OFFSET(Topics!$A$1,0,0,COUNTA(Topics!$A:$A) +1 ,1)

When I made that change the wb no longer crashed.

To make that change go to Formulas|Name Manager and edit the formula. Note that most of the others need changing too. There's one that has +10 rather than +1 and I don't know what that is all about but you may either have to remove that +10 or change it to +9.
0
 
Frank FreeseAuthor Commented:
I replaced your code - I got the same error. I'm wondering if there is something else? I don't know what? Since I am not very far into this I thought I'd recode everything keep only what I need at this time regarding modules.
Before I do that could you send me your workbook that is not crashing please?
Thanks
0
 
Martin LissOlder than dirtCommented:
See post ID: 40008950
0
 
Frank FreeseAuthor Commented:
Let me open another question and start afresh.
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.