Macro not trigger on cell value change

**jeetusaini85** · 02-05-2015, 02:33 AM

Dear Friends,

The below mentioned code does not run when a cell value change/update/enter by another macro in a range but working fine when i manually enter "Yes" in mentioned range and shoot the mail with corresponding data related to that row. I am stuck here and almost spend a week to find solution on this.

I am sure someone will help me here to solve this as i have limited knowledge of VBA.

Here is my code which shoot call another macro to send email.

Note : The value "Yes" entered by another macro in the range, after that this code detect and shoot the mail.

Please Login or Register  to view this content.

**:) Sixthsense :)** · 02-05-2015, 02:47 AM

Originally Posted by jeetusaini85

The below mentioned code does not run when a cell value change/update/enter by another macro in a range but working fine when i manually enter "Yes"

That macro may be turning off the events

**jeetusaini85** · 02-05-2015, 03:35 AM

Hi Sixthsense,

Thanks for your reply. As i said i have limited knowledge of VBA, can you please guide me to correct this code as per my requirement.

How can i turning on the events in the codes.

Thanks.

**:) Sixthsense :)** · 02-05-2015, 03:46 AM

Please post your another macro code for doing correction

**jeetusaini85** · 02-05-2015, 05:02 AM

Sixthsense,

The another macro is mentioned below. This macro basically for update the status records.

Please Login or Register  to view this content.

**:) Sixthsense :)** · 02-05-2015, 05:08 AM

Originally Posted by jeetusaini85

The another macro is mentioned below. This macro basically for update the status records.

I don't see any line of code to enter text "yes" in cell in your another macro.

Then how the worksheet change code will run?

**jeetusaini85** · 02-05-2015, 05:15 AM

Sixthsense,

The below line is entered / update by this macro in sheet "PartsData".

Please Login or Register  to view this content.

"UpdateEntry" is named range of data to be update from sheet "Input" to sheet "PartsData".

Hope, i clear myself on this.

**:) Sixthsense :)** · 02-05-2015, 05:24 AM

These 3 conditions needs to be fulfilled to trigger the worksheet change event.

Please Login or Register  to view this content.

Meaning of the below line - Change should happen in one cell otherwise it will exit the change event

Please Login or Register  to view this content.

Meaning of the below line - Change should happen in the range R1:R5001 otherwise it will exit the change event

Please Login or Register  to view this content.

Meaning of the below line - Changing Cell (Single Cell) VALUE should be lower case "yes" otherwise it will exit the change event

Please Login or Register  to view this content.

**jeetusaini85** · 02-05-2015, 05:27 AM

Yes, u r right but why this not running?

I want to change only 1 cell at one time in mentioned range. What is wrong in this? Now let me clear myself on this again.....

I have an excel sheet in which I have two sheets 1 is "Input" and 2 is "PartsData".
In "Input" sheet I have a form in vertical position which fill by the user and on click a buttone a macro run and enter this filled details to "PartsData" by copy paste method with transpose. Now if any records needs to update the user find that recrod by its unique number from sheet "Input" by Workheet_Change event (another macro) and all the details releted to that number visible in fields and after updation of recrod my 2nd macro which i have provided to you update the same(with extra field of "Yes" and "No"). If "Yes" found in coloum range then i want to execute my mail macro.

**:) Sixthsense :)** · 02-05-2015, 05:45 AM

Still you are not understanding what I am trying to say...

Please read my Post #6 content once again to get clarity

**jeetusaini85** · 02-05-2015, 05:52 AM

ok, i got your point...

"Yes" is a part of data name range "UpdateEntry". When this macro runs, data under "UpdateEntry" range copy from sheet "Input" and paste into sheet "PartsData".

I am sorry if i am not clear but i am stuck and i think i am bothering you.

**:) Sixthsense :)** · 02-05-2015, 05:58 AM

Run the code in break mode and check in which line it fails.

Or

Attach a sample workbook

**jeetusaini85** · 02-05-2015, 06:26 AM

Can you please guide me how to attach a workbook here?

**:) Sixthsense :)** · 02-05-2015, 06:27 AM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**jeetusaini85** · 02-05-2015, 06:45 AM

its not allowing me to attach file as file size is exceeded the limit. 5.56 MB...

**:) Sixthsense :)** · 02-05-2015, 06:49 AM

Add it in Zip and attach it. Since Zip have 10 MB capacity

**jeetusaini85** · 02-05-2015, 07:02 AM

Hi,

Please check.

Hope this will help you to resolve the issue.

auto macro run example.zip

**:) Sixthsense :)** · 02-05-2015, 07:23 AM

Your codes and references:-

Set myCopy = inputWks.Range("UpdateEntry")

UpdateEntry=Input!$C$3:$C$18

myCopy.Copy

UpdateEntry is a single Cell? It consist 15 cells.

Change event code

Please Login or Register  to view this content.

According to above line Pasting should happen in single cell, but your copy range consist 15 cells and the if statement will end the code.

**jeetusaini85** · 02-05-2015, 07:25 AM

Ok, so how can i resolve this?

**:) Sixthsense :)** · 02-05-2015, 07:44 AM

Replace your current (PartsData) worksheet Change Event with this one

Please Login or Register  to view this content.

**jeetusaini85** · 02-05-2015, 07:51 AM

I have tried your code and it gives me error "Run-time error'1004':Application-defined or object-defined error" at below mention line:

Please Login or Register  to view this content.

also after updating the records its not clear contents of the form field.

**jeetusaini85** · 02-05-2015, 08:21 AM

R u there buddy?

**:) Sixthsense :)** · 02-05-2015, 08:28 AM

I am replying from mobile device now so i am unabe to edit your code.

Replace all target with Target.cells(16) in the below code.

Please Login or Register  to view this content.

**jeetusaini85** · 02-05-2015, 08:37 AM

Sixthsense,

Heads off to you, you are a life savior. The code works perfect as expected. Thank you very much for your time and resolution.

Thanks,

Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Re: Macro not trigger on cell value change

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Cell change to trigger Macro

[SOLVED] Cell value change to trigger macro (worksheet change event?)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions