Using Worksheet Change event on different worksheet

[jakestyeen7059]

Hello,

I have been having hard time finding answer to the problem I am facing.

The situation is, I export data into .csv everyday and that needs to be customized, and then sends out as an excel report.

Because it is an export file, which the name would change every time, I have a macro that resides somewhere else. But in the middle of macro, I would need to manually input some missing data before going forward, thus I have separated out into three macro; main one till the point I have to manually enter data, worksheet change event that would call the third macro when data is filled, and the macro that will finish its job.

Each macro works perfectly but the problem I have is the connection between the first and the second. Because worksheet change event is stored in the worksheet1, it does not get triggered by me filling data in the different excel. I was wondering if there are any way to make this work.

This is the code i have for worksheet change event,

Please Login or Register  to view this content.

I know I have a few alternative option like
1. copying all data to the macro excel sheet, do its job, then copy it back,
2. Use msgbx to stop one big macro in the middle then click "ok" to keep going,
3. eliminate the second one and use hotkey to activate my third macro "Mymacro".

But I was really hoping to eliminate extra steps even a click, to make my job easier.

I would appreciate any help you may have.

Thanks,
Jake

[scott.s.fower]

definately possible, but i can't visualize what you are doing, is there a way that you could upload a sample workbook? and describe the process in more detail?

[jakestyeen7059]

hmm i will be probably quicker to describe, if that is okay.

the export file for this would come from company's program but similar to what the exported excel looks like when you export your statement. It contains various name, account #, ID, amount and so on.

The file is organized in the way if could be reuploaded thus for our report purpose, we have to eliminated it. At the end of all those thing, the main macro would bring a range filled with necessary information, with some blanks in Column A which is an ID. Some ID are not complicated so it is within the main macro but some changes every time, thus needs manual verification then input.

The third one would sort the data by column A, subtotal column D at each change of column A, with page breaks in between.

This is when the second one was brought up so that I (actually my manager) do not need to assign a hot key (i.e. Ctrl + h) to move on, instead if the column A has no blank, then run the third one so all I need to do is print to our different sharing software.

Right now I tried call Worksheet_change with no luck. I also tried sheet1.Worksheet_change but nope... I also tried moving the private macro within module but again, nah. I was even thinking of writing code to write code in this exported excel but figure there should be an easier way, before I waste my next several hours.

Please let me know if any question, and I really appreciate your help

[jakestyeen7059]

anybody?

I will try to create a dummy workbook as well.

[jakestyeen7059]

Attachment is the dummy.

At the end of my first macro, it will look like this.

From here, I will manually type in the ID in empty cell of Column A, then run the third marco...

Please Login or Register  to view this content.

To trigger this last macro, I currently have to manually activate but I was hoping someone could help me figuring out how to use event macro on exported excel sheet.

Thank you in advance for any help and info.

[jakestyeen7059]

bumping up...

[JosephP]

add a new class module to your code workbook and add this code to it

Please Login or Register  to view this content.

at the top of the current normal module in your macro workbook add

Please Login or Register  to view this content.

then in your processing code at the point you need to wait for input use

Please Login or Register  to view this content.

[jakestyeen7059]

JP,

Thank you for your help. I will like to try it out but beforehand one question.

I presume the worksheet, sheet name, is my exported excel sheet's name, but it changed randomly. Is there any way to go around it besides adding code at the top to change its sheet name?

Thank you!
Jake

[JosephP]

I don't really know what you're asking but perhaps you can just use

Please Login or Register  to view this content.

if you don't know the sheet name

[jakestyeen7059]

JP,

That didn't do anything unfortunately. I looked over your code but somewhere within there, it needs to say once ColumnA has no empty cell, then run move on.

At this moment, it stops at the point I fill in the ID, and nothing happens.

Jake

[JosephP]

I don't know what you're doing or how you used the code-I only translated what you posted. can't really comment further without knowing the detail

[jakestyeen7059]

JP,

Thank you for trying.

As I mentioned in the first post, I have an exported excel sheet and macro is stored in different normal module on different excel.

I currently have the first one which brings to the attached dummy, then I use short cut key to run the next one which finishes its job.

What I was hoping to do is, use worksheet change event so if all cell in Column A when there is a value in column B, run the next macro to finish automatically.

But because event macro needs, in my understanding, to be stored in the sheet, which in this case would not work, hence my question was if the event macro could be used in the general module, or what can be done to get around this.

[JosephP]

maybe replace the class module code with

Please Login or Register  to view this content.

[jakestyeen7059]

JP,

That is what I thought too so I did try it but still didn't work.

I did try putting my macro "Mymacro" in different module too but didn't work

[JosephP]

I need more information than "didn't work"-what happened?

[jakestyeen7059]

JP

Sorry for being unclear. Didn't work meaning it didn't start the next macro "Mymacro" after filling data in Column A.

[JosephP]

can we see the macro workbook including my code?

[jakestyeen7059]

I unfortunately cannot include all of them but here is what I have,

Please Login or Register  to view this content.

Then this is the macro it needs to call, stored in same module

Please Login or Register  to view this content.

Now in class module, I have

Please Login or Register  to view this content.

Attachment is the same thing.

[JosephP]

I said that

Please Login or Register  to view this content.

should be at the top of the module-ie before all procedures and not within one

[jakestyeen7059]

JP

Thank you so much! I really appreciate it! That worked perfectly and I learned more than I could imagine doing by myself.

I am marking this as solved.

Appreciation,
Jake