Runtime Error 9, open debug and macro completes

Hi all

Got some code which in a nutshell checks workbook name, opens another and sends out some emails.
Part of the code I'm using gets the 1st workbook name, pastes it into a cell of the 2nd (which has an on change event to call the 2nd macro) which the code uses to set as the workbook:

Please Login or Register  to view this content.

Open second workbook

Please Login or Register  to view this content.

When the code gets to Workbooks(snm).activate, I get Runtime Error 9 but the code finishes and the emails are sent anyway.

Does anyone know how I can get around this please?
Preferably without Application.DisplayAlerts = False or On Error Resume Next

Thank you

Which workbook are you trying to activate and how are you trying to activate it?

Also, where's the code that opens the 2nd workbook?

What happens if you use this?

Please Login or Register  to view this content.

Not sure I follow.

In the original code is this line, which is the one I'm suggesting you replace, meant to trigger something that sends the emails?

Please Login or Register  to view this content.

Why not create a reference to the WSR book and pass that to the sub in the GC workbook that sends the emails?

I'm not 100% sure because I don't know when, or how, you are setting the value of WorkbookName.

Actually, I should have asked where/when you are opening the workbook that you want to refer to using WorkbookName.

So you open the WSR workbook and this calls the code to open the other workbook and send the emails?

Please Login or Register  to view this content.

If that's right you should pass the WSR workbook as an argument to OpenGCWB.

To do that you would change the header of the OpenGCWB to something like this,

Please Login or Register  to view this content.

and you would call it like this.

Please Login or Register  to view this content.

You would also alter OpenGCWB to refer to wbWSR.

What I'm really trying to suggest is that instead of using a cell with the workbook name to refer to the workbook you actually pass the workbook.

How you would do that exactly I'm not sure because I've not seen all the code.

For example, where's the code for sending the emails.


PS Why not open the GC workbook, then open the WSR workbook?

So how are you opening the WSR workbook?

You actually have a reference to the WSR workbook in the code, it's wb that's being passed to app_WorkbookActivate.

I would pass that reference to the OpenGCWB sub

Then I would call the WelcomeNEW sub from the OpenGCWB sub and when calling pass the reference to the WSR workbook.

Something like this perhaps, which is of course totally untested.

Code in the Personal.xlsb.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

In the GC workbook.

Please Login or Register  to view this content.

It's probably for some other reason.

Where in the GC workbook is the sub WelcomeNEW located?

It should be in a standard module, not a worksheet or workbook module.

The code works for me when I create a mocked up GC workbook with the code in it.

Does this make any difference?

Please Login or Register  to view this content.

Can you upload the GC workbook, without any sensitive data?

Is the real name of the GC workbook 'Claims Guild.xlsm' and is the sub actually called 'ClaimsWelcomeNEW'?

If so, have you changed the code to take that into account?

Can you post the changed code?

It was actually the code to call ClaimsWelcomeNEW I wanted to see.

Mind you seeing the code for that sub I notice you haven't changed the heading to include the argument wbWSR.

To include that it would look something like this.

Please Login or Register  to view this content.

Try this.

Please Login or Register  to view this content.

You did make the change I suggested in post #34?

Please Login or Register  to view this content.

Can you upload all 3 files?

Thanks for uploading the files, can't look at them right now but I'll have a proper look later.