Compile Error VBA error code attached

[cgibson92]

When new users open the file they receive a "Compile error: Can't find project or library" error. Below is my VBA written to open up a worksheet when the field is selected and and also is a multi select drop down box data validation. Any help would be appreciated....

Also when looking at my script can you help me make the multi select appear in its own cell when selected?

Please Login or Register  to view this content.

Thanks
Cindy

[TMS]

Hi Cindy, and welcome to the forum. Please take a few moments to read the forum rules and then 1) change your thread title to something more descriptive, 2) add Code Tags to your code excerpt, and, ideally, 3) post a sample workbook.

I suspect that it is a Missing Reference on the other computers.

Regards, TMS

[cgibson92]

We have checked to see if something is missing and found nothing. when they receive the error is highlights the code below and the word "Right".

Please Login or Register  to view this content.

[TMS]

There's nothing wrong with the code highlighted, as far as I can see.

But this is typical of errors generated by missing references.

I can't really test the code by copying it into an empty worksheet. Can you upload a sample workbook?

Regards, TMS

[cgibson92]

i did have a popup calendar added and removed the code but it was still checked in my "tools --> References". I have unchecked that box and saved my file, could that have been causing the error?

Thanks

[protonLeah]

title updated

[TMS]

@Cindy: now that Ben has also picked up on your thread title, I can no longer continue with the discussion until you edit it.

[cgibson92]

I have updated my thread title on my original post. sorry for the confusion.

[TMS]

I believe that the Calendar Control isn't available in all versions of Excel. If your original code required a specific reference, that may still be present in your copy but be MISSING in your colleague's.

I would first check for MISSING references and, if found, uncheck them.

As I said, posting a sample workbook that demonstrates the problem might accelerate diagnosis of the problem.

Regards, TMS

[cgibson92]

Attached is a test file. If you could also help with when selecting from the incentive type they would appear in separate cells to the right of the multi select box and when selecting an incentive area the tabs previously select disappear.

TEST.xlsm

Thanks
Cindy

[TMS]

OK, I've tested the workbook and had no problems. No MISSING references, it seems.

So, I've made some changes. Removed the three empty User Forms. Removed Module 1 containing a copy of the Worksheet Change event handler. Restructured the Drop down lists worksheet. Created Dynamic Named Ranges (DNRs) for all the lists ... much easier if the lists are in separate columns (and I tend to separate lists with a blank column). And applied the DNRs in the relevant cells on the Summary sheet.

Everything seems to work as it should, although I have to say I'm not a fan of Application.Undo ... but it does seem to generate multiple entries in the cell (interesting approach).

So, not sure what else I can say. It seems to work as far as I can see. Though whether it works to your satisfaction, or not, is another matter.


Regards, TMS

[cgibson92]

Thank you TMS... just one more question...

Is there any way to make the items in the multi select boxes to also appear in the cells to the right of that box (individually)? And when i select an "incentive type" it opens up the correct worksheet, but when selecting an "incentive area" the worksheet disappears?

Thanks again!!!
Cindy

[TMS]

Hi Cindy. Although you have said "thank you", you haven't said whether or not the problem has been resolved.

As to the additional questions, anything is possible ... but "make the items in the multi select boxes to also appear in the cells to the right of that box (individually)" is not very specific. I'm not sure how you indicate that you want to start the process, that is, clear cell B36 (Incentive Type) and, end it. How do you know whether or not to keep adding Incentive Types? Which leads on to, when and when not to display of hide Incentive sheets. And, is there a limit to how many Incentive Types to be stored in cells? Which cells? When do you clear the stored entries.

And cell B47 (Incentive Area) is also a multi-selection cell; so when do you hide sheets?

I think you need to think through the logic and start a new thread with a clear explanation.

In the meantime, ...


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[cgibson92]

TMS thank you again and yes it worked i no longer receive the error. I will post my additional questions in a new post.

thanks
Cindy

[TMS]

You're welcome. Thanks for the rep.