listbox control source causing error

[racudd]

I was wondering if anyone can tell me why this is causing an error upon opening the file. This workbook has a userform with a listbox (single list, single selection), and when I have the ControlSource property set it causes an error upon opening the userform (which opens when the file is opened), whereas if I do not have the ControlSource property set to anything, it seems to work fine.

Interestingly, if I set the file up to not open the userform with the file, and instead have the userform open when a worksheet control such as a command button is clicked, it doesn't seem to matter if the ControlSource property is set or not. Very confusing. All help is greatly appreciated. The workbook in question is attached.

[Leith Ross]

Hello racudd,

Your workbook is damaged. Copy the code, form, and module into a new workbook. If that doesn't fix the problem then post the new workbook. It is not a good practice to post damaged files. Excel crashed 3 times while I was attempting to determine the problem. I have quite a few safeguards in place to protect my system, others may not.

Sincerely,
Leith Ross

[racudd]

My apologies - I had no idea that the file was damaged, just thought there was some issue with my programming. I did as you suggested and created a new workbook, but it behaves just the same. I've already run a detect and repair recently, which apparently has not fixed the issue. What would you suggest is a next logical step? Certainly feel free to delete my attachments if you feel it necessary (I couldn't see how I would be able to do that). Thanks very much for your time.

[Leith Ross]

Hello racudd,

Are you experiencing this problem only with this file? How about any new file you create? Do your old workbooks open and run correctly? What version of Excel are you using?

Sincerely,
Leith Ross

[broro183]

hi all,

Leith, you can find a smidgin more background for racudd's problem by looking at the below link (to one of Racudd's previous threads) which is likely to/does have the same root cause...

http://www.excelforum.com/excel-prog...c-project.html

Rob

[racudd]

Mr. Ross - running excel 2003. Any new files created are having the same difficulty. I haven't noticed any older files behaving strangely, but I'm not sure they use the same functionality I'm trying to currently use. I have a tool that I am working on that for the first 6 or 7 weeks worked perfectly. I began receiving rather sporadic errors such as those previously mentioned, and they've just become more and more frequent. I have actually just finished a complete uninstall/reinstall of Office to include all Office updates. I am, however worried about whether I should even try to use any of my recently created files. Could they continue to cause problems? I do have a rather large tool that I need to be finishing up next week, so I'm a little unsure as to how to proceed. What are your thoughts?

[Leith Ross]

Hello racudd,

If the reinstall of Office has fixed the problem, I don't believe running the corrupted files will corrupt Excel. It would be best to copy the information from those files and recreate them. Destroy the files once they have been copied. I did not receive a virus or malware alert when I downloaded your workbook, so I believe your Excel program simply became corrupted.

Sincerely,
Leith Ross

[racudd]

Thanks very much for the advice. I will do as you suggested. My big question would be how in the world did excel become corrupted in the first place? I really didn't think this was even a realistic possibility, which is why I spent a ridiculous amount of time trying to figure out what I was doing wrong. Anyhow, I guess I'll take it up with Bill Gates. Thanks again for your time. I'll let you know how it turns out.

[Leith Ross]

Hello racudd,

Here is a link you may find useful should this happen again.

Repairing Corrupted Files in Excel

Sincerely,
Leith Ross

[racudd]

Let me pose this question. I've got three different laptops (two run Excel 03 and one has Excel 02), and on every one of them, if I create a userform that loads when the file is opened (i.e., is called by the Workbook_Open event, or the Workbook_Open event calls another procedure that then loads that userform), and that userform has a control with the ControlSource property linked to one of the worksheets, I get a path/file access error most of the time. Why in the heck doesn't this work? I've reloaded Office and done all sorts of diagnostics, but I've about decided that this is some limitation with Excel.

I've discovered that in all of the programming I've done, I've never set one up just like this until now. Previously, I have had the file open, and then used another control (usually with a click event) to open the required userform. Or, if I had the userform load when the file opened, I didn't have the ControlSource property set for any of the userform's controls. Does anyone have any insight regarding this?

[Leith Ross]

Hello racudd,

Try activating the worksheet that the form is linked to first in the Workbook_Open event module.

Example

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[racudd]

Tried it - no luck. Are you able to make this work on your computer?

[Leith Ross]

Hello racudd,

No, I haven't. Excel keeps crashing. Like you,I have looked for any articles on this particular problem, and have come away empty handed.

Sincerely,
Leith Ross

[racudd]

That is the craziest thing. It seems like from a functionality perspective, this would be something that would definitely work. I have also searched the Microsoft site and not found anything addressing this specifically. I have an 800 number for their VBA support people - do you think they would able to shed any light on this?

[Leith Ross]

Hello racudd,

Wow, there's an 800 number ?! Definitely give them a call. I would love to know the reason.

Sincerely,
Leith Ross

[racudd]

The thing that is most frustrating is that I designed this tool two months ago, and for a long time it worked fine. Of course, now I'm into the testing phase and I've discovered that this is a problem. You can do things that will make it work - for example, if you run codecleaner on it, the next time it is opened it works fine. I can simply redesign it so that upon opening the user goes to a worksheet, and the userform for all data entry is then opened from that worksheet. I've never had a problem when doing it that way. I wonder if this works better in excel 2007?

[racudd]

I was given an 800 number, but I have yet to call and find out if it is something that might actually be useful. I'll let you know what I find out.

[racudd]

The 800 number they gave me was for Microsoft Professional Support, and as you can imagine, they want to charge for their help. I decided that it wasn't worth $259 to get my question answered, since I'm skeptical that they actually no more than the folks on this forum anyway. Maybe one of the other forum contributors has seen this problem before and can chime in. At any rate, I know how to work around this issue, so I'll just do a quick redesign. As always, thanks very much for your time and help. You have no idea how much it helps to know that this forum is available when I run into these problems, so you and all the forum moderators and contributing members are all greatly appreciated.

[racudd]

After doing just a little more research, I saw that this exact same issue came up on a different forum:
http://www.ozgrid.com/forum/showthread.php?t=61489
Dave Hawley and royUK provided some feedback on this, but from looking at all of the posts, I was unclear as to whether or not this problem had actually been resolved. The last suggestion I saw was to create a named range for the rowsource property. I tried this but still get the same error. I'm hoping that maybe royUK recalls something about this and might have a suggestion. Thanks.

[Leith Ross]

Hello racudd,

I don't believe Roy is online right now. I will ask him if he remembers this problem and ask him to look at this post.

Sincerely,
Leith Ross

[racudd]

Much appreciated!

[Andy Pope]

Clear the listboxes ControlSource property and set it in code instead.

Please Login or Register  to view this content.