Dropbox choice runs macro Error ??

Hi there. I want to make a dropbox choice run an independant macro for this choice. I've looked on forums , everywhere And i just can't get it to work. When i run the macro from the Sheet code, it works. But the minute i try it , it pops the error : Sub or fonction not defined..


My macros are placed on a module but the one for the dropbox is on sheet1. Heres my code:

Please Login or Register  to view this content.

- The first line is highlighted when the error pops.
Can someone explain to me what i'm doing wrong?? Thanks!

Hi Eliann and welcome to the forum,

It would be easier for us to read you code if you Edit your message and then click on "Go Advanced". Highlight your code and click on the "#" above the Advanced Message area to put Code Tags around it.

After doing the above. Your code has two End Sub lines and no End Case. I wonder if that hurts. Do you have Option Explicit at the top of each module to help discover problems before you try to run them? Also, I've seen a "As Range" but never a "As Excel.Range". I wonder if that is causing propblems.

Here is some good info on Event Code http://www.cpearson.com/excel/Events.aspx

Hi,

I was thinking you would want a

Please Login or Register  to view this content.

type of construct.
Read it at http://www.ozgrid.com/VBA/vba-intersect.htm

To accomplish what I think you want, you may need some VBA code.

You can attach a workbook by
Clicking on "Go Advanced" and then on the Paper Clip Icon above the advanced message area.

Hi,

See the attached. I hung the code on a Selection Change instead of Worksheet Change as it would loop continuously. I believe the quotes in the CASE statements were messing things up. I've removed them. I also believe you can't use parenteses in the CASE statement as it looks like a parameter in a function call. I think there were problems with English vs your language character set for me.

Try the Easy Words in E1 to see if they work. Set a breakpoint in your code and step through it one line at a time to see what is happening. http://www.cpearson.com/excel/Debug.htm

Some of the words in the attach will work and some won't. See the VBA Code to see what words I've changed and perhaps select better dropdown selections without parins or single quote marks so the Case statements will work.

Please find.
Your denominations were pretty inconsistent in terms of spelling.

I'm afraid I do no fully understand what you mean.
Is it working at your computer, or you cannot make it work?

First: do not use merged cells.

You won't need all those macro's.
If you structure the data in sheet4 systematically you only need 1 line of code.
This will suffice:

Please Login or Register  to view this content.

Based on the example you posted this may be simpler:

Please Login or Register  to view this content.

Okay! Nice , I think i was trying too hard , now my list on sheet 4 is clean, separated, and it works! I was able to get it to paste in cells a:15 to G:16(from sheet1) the only probleme is to get from A3 to E4 on sheet 4 , it copies the first column only . I see where it goes but when i try to change it the syntax never works! I can see the end of it and the understanding of this code, If i can just get this right ill be able to reproduce it and finish my project!

You could consider to use the help in the VBEditor and read what's written about currentregion.
It means that in sheet4 you have to fill column B, C and D to get the full currentregion; space will suffice.
The 'currentregion' is the most flexible approach.
An alternative could be:

Please Login or Register  to view this content.