Thread: 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:

Private Sub worksheet_Change(ByVal Target As Excel.Range)

    Select Case Target.Range("$E$1").Value
        Case "Draconien"
            Call Draconien_init
        Case "Fenrae"
            Call Fenrae_init
        Case "Third macro Name"
            Call Third_Marco
        Case "Fourth macro Name"
            Call Fourth_Marco
    End Select
End Sub
End Sub

- 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

If Not Intersect (                        ) Is Nothing Then

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:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$E$1" Then Sheets("sheet1").Range("A15:E16") = Sheets("sheet4").Range("A3:E4").Offset(3 * Application.Match(Target, Split("Draconnien;Fenra;Hab Jai;Hideu(bossu);Hideu(colosse);Hideu(Teigne);Hideu(Tryclope);Hobgebelin;Humain;Sylvestre;Tiefling;Torcanien;Zirath", ";"), 0)).Value
End Sub

Based on the example you posted this may be simpler:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$E$1" Then Sheets("sheet4").columns(1).find(target,,xlvalues,xlwhole).currentregion.offset(1).copy Sheets("sheet1").Range("A15") 
End Sub