trigger event in excel when user clicks Save as

[nickjar2]

I have a master spreadsheet with code on the ThisWorkbook. The user opens this spreadsheet and chooses Save as. How would i get the code on the thisWorksheet to get called when the user saves as?

cheers Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[psudeke]

I would put the code in the On Save event and place an if statement to test to see if the file name that is being saved is the same as the main file.

 

[nickjar2]

Or better still....

On doing a Save as, can I have the whole code in the thisWorkbook to delete. The reason for this is as follows:
each spreadsheet that gets saved, gets compared to all the previous saved sheets. To do this I open each workbook (in code) and do a search for specific values. For some reason, as soon as I open the workbook, the actual workbook opens up for the user to see. Previoulsy, it was opening invisibly, i could do the processing, and then close it, all oblivious to the user. Now the user sees it open, and gets prompted to whther they want to save it or not.
I am using the Excel object to do all this ie.
excel.application and excel.workbook and excel.worksheet

here is a sample:

Set xlApp = New Excel.Application

' Need to check how many (if any) previous invoices need to be
' checked against
iNoOfPrevInvoices = UBound(pArInvoices)

For iCount = 1 To iNoOfPrevInvoices

' Open workbook
Set xlBook = xlApp.Workbooks.Open(pArInvoices(iCount - 1)) Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[nickjar2]

Thanks psudeke,

I will give that a go. I posted my second Q before seeing your response. Any ideas??

Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)