vba, code execution stop when macro is started from custom tab, or activeX button, but not when sheet form button

0
votes

I've been searching for hours but I can't find any answer. I hope someone can help me. I have a workbook with all the data and macros let's name it "wbCurrent", but I use a macro in the PERSONAL.XLSB workbook to save the wbCurrent with another name, let's say 06-12-2016, in other location. Then I close it(the rename one) and reopen the original wbCurrent again. This macro works perfectly when I call it from a button in the sheet. But recently I modify the excel with custom tab and buttons, but when I call the macro from there, the code stop without error when I close the wbCurrent. I try using an ActiveX button and the problem shows again. I put a form control button and it run ok. What could this be? I really want to use my pretty custom tab. This is what the macro do essentially, remember it is located in PERSONAL

Sub New()
' do some changes in wbCurrent
wbCurrent.Save
oldwb = wbCurrent.SaveAs (different location, diferent name)
' Do some change
oldwb.Save
' here is where it stop executing only when call from activeX or custom tab
oldwb.Close
wbCurrent=nothing
wbCurrent=Open FileName.....
'Do some change
wbCurrent.save
end Sub

Thank u in advance Barby PD the macro is called from wbCurrent via Application.Run "PERSONAL.XlBS!New"

update I made this test to be sure of the problem I put a macro in personal.xlsb with 2 lines of code: 1 close the workbook that call the macro, and 2 shows a message. In an empty workbook I call that macro from 3 different places: control form button in the sheet, activeX button in the sheet and a button in a custom tab created with an editor. You can only see the message when you call the macro from the first choice, in the others the execution simply ends when the original wookbook is closed, despite the personal workbook is open all the time. Ah, you also see the message when you execute it directly from vba editor. It seem to be something related with who has the control or ownership of the macro, but i can't find anything in Application object that seem to be related to that. Any ideas, please!

1
excelvba

1 Answers

0
votes

There is no solution to that: if you execute a macro and that macro closes the workbook that started the macro, the lines after closing will not be executed. So, as I was working with several workbooks, the right way to go was to create and Addin, so that way the macro is not called from my workbook, but from the addin, and because the addin is open all the time I could safely close the workbook and the macro will continue. Thanks to Andy Pope with the idea and for the visual ribbon editor.