Frank Freese
asked on
Run time error on list box?
Folks,
I keep getting this error:
There'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
I keep getting this error:
There'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
ASKER
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:
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:
There's no Data tab and no data to fill the listboxes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Rgonzo1971,
Thanks - I'll look at this
Frank
Thanks - I'll look at this
Frank
Hmmm. I closed the workbook and then reopened it and the data showed up. Let me see if I can reproduce the error.
ASKER
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?
OK I don't have the Analysis Toolpak. Do I install "Analysis Toolpak" or "Analysis Toolpak VBA" or both?
ASKER
Load the Analysis ToolPak add-in. Not the Analysis ToolPak VBA
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.
ASKER
Truly frustrating....thanks Martin
ASKER
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
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
ASKER
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
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
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,CO UNTA(Topic s!$A:$A),1 )
and not
=OFFSET(Topics!$A$1,0,0,CO UNTA(Topic s!$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.
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,CO
and not
=OFFSET(Topics!$A$1,0,0,CO
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.
ASKER
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
Before I do that could you send me your workbook that is not crashing please?
Thanks
See post ID: 40008950
ASKER
Let me open another question and start afresh.
Where does the error appear?
Regards