change event not triggering macro

**cmccabe** · 06-26-2014, 03:10 PM

I have a macro that when run reformats data and a dropdown box is put into cell A2. Th user then selects the case number and this change is supposed to trigger another macro (calculations), but it does not. If I manually run calculations I get a result, but can not get the change_event to work.

Here are the codes I have tried:
VB

Please Login or Register  to view this content.

VB

Please Login or Register  to view this content.

The way it is supposed to work is the user selects case 64 from the dropdown and the vlookup value is "Comprehensive Epilepsy". This change in A2 triggers the calculations macro (the formula is not working as expected either, but that is another issue). Thank you.

**daffodil11** · 06-26-2014, 03:18 PM

You don't need to state the target range twice. Try this:

Please Login or Register  to view this content.

**cmccabe** · 06-26-2014, 03:34 PM

I changed the code to that with the same result. I can manually call calculations but the change event does not seem to work. Thank you.

**daffodil11** · 06-26-2014, 04:28 PM

I've used a very similar procedure in the past, including the user of a dropdown menu. Maybe try just using a simplified targeting, instead of intersect:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**cmccabe** · 06-26-2014, 04:40 PM

I attached a copy of the workbook. I changed the code and put a breakpoint in on the first line of Sub Calculations and it turned red.

The change in A2 produces a vlookup value. That value has a select case forumula that is supposed to run. In the attached if you run the classify macro and then in the dropdown in A2 select case 1, the vlookup will be Comprehensive Epilepsy. The Calculations sub is suppose to run automatically but currently I manual go in and run it. Thank you.

**daffodil11** · 06-26-2014, 05:41 PM

Really had to go line by line and test all the expression in the immediate window, but I think I found the issue.

Case 1 is working as intended, but Case 2 has a tiny little typo.

Const myMarfan As String = "=IF(SUMPRODUCT(--(Panel!$B$25610:$B$29333=$Q$5),--(Panel!$C$25610:$C$29333<=$R$5),--(Panel!$D$25610:$D$D29333>$R$5)),VLOOKUP($R$5,Panel!$C$25610:$E$29333,3,1),""No"")"

**cmccabe** · 06-27-2014, 11:25 AM

The change event doesnt seem to be working (triggering calculations) with either of the two codes, but I can run calculations manually. Thanks.

**daffodil11** · 06-27-2014, 11:38 AM

I'm not sure what's supposed to be happening, other than the autopopulation of formulas in AQ.

Every time I clear column AQ, change A2, and go back the VLOOKUP formulas have reappeared. Is something else supposed to be happening?

**Speshul** · 06-27-2014, 11:44 AM

Change the code to Public, instead of Private?

I just did something on Worksheet_Change and it worked when I changed it to Public. Also I noticed the Worksheet_Change wouldn't work in a separate sub, had to be in the Sheet. I could be missing something though, I'm bad at VBA.

**daffodil11** · 06-27-2014, 11:45 AM

I was even able to force Case 2 to go off as well, by changing CG to Marfan Disorder, and changing A2 again.

Sure, a lot of AQ turns into errors because the sample dataset doesn't support Case 2 but the formulas are correct for Case 2's output.

**cmccabe** · 06-27-2014, 11:55 AM

Here is the change event:
VB

Please Login or Register  to view this content.

I think maybe the problem is calculations doesnt exist on the sheet, rater in PERSONNAL.XLSB!.

Does it need to be called a different way? Your description of what should be happening is correct. Thanks.

**daffodil11** · 06-27-2014, 12:22 PM

Ahhh, the missing information. If the macro you're calling doesn't even exist in the same workbook as the change event you'll have to call it an entirely different way.

Googled: Excel vb call macro from another workbook

Result:

Application.Run ("PERSONALL.xlsm!Calculations")

This will only work if the workbook you're calling it from is open as well.

Applied:

Please Login or Register  to view this content.

**cmccabe** · 06-27-2014, 12:55 PM

I moved the Calculations macro into the this workbook and the change_event is on the annovar sheet (where the A2 change happens): but I have to run calculations manually to get the values. Thanks.

VB

Please Login or Register  to view this content.

**cmccabe** · 06-27-2014, 01:15 PM

It is working now, I had the Personal macro open, once I hide that everything is fine. Thank you for your help

.

change event not triggering macro

LinkBack

Thread Tools

Rate This Thread

Display

change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Re: change event not triggering macro

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Prevent change event triggering when clearing contents

Worksheet Change Event not triggering with cells containing formula

RTD value changes not triggering worksheet change event

[SOLVED] Validation not triggering Change event in Excel XP

[SOLVED] Validation not triggering Change event in Excel XP

Bookmarks

Bookmarks

Posting Permissions