none
Problem Executing Macro from Ribbon, Runs fine when called from intermediate, or 'normal' butons RRS feed

  • Question

  • Hi,

    I've come across a very strange problem, we've been using some fairly simple macro's within excel for the last few months, I've recently come into a strange problem.

    I decided to make things easier by putting commands onto the ribbon and creating an addin, as this code is reused by most of our workbooks, and having thousands of files with this code in seems like an awesome way to fill up our drives :)

    Basically the code seems to just ignore the saveas command when run from the ribbon, code runs, ends saying successful, in the debugger stepping over the code it appears to step over the saveas command as appears that it's been excused without fail, however no file gets created, workbook does not get saved and it keeps its original book<x> caption

    here's a quick example of my code, I have a class module registering application commands

    the ribbon button i'm using is just a print button, using the application print which then calls this before print function

    this is the code that fires from the ribbon, we always print out 2 copies i'm not sure if this can be simplified?

    If Application.Workbooks.Count = 0 Then Exit Sub
    If CheckRangeExists("BVSID") = 1 Then
            ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, IgnorePrintAreas:=False
    End If

    Using CTRL P or File Print this works fine, BeforePrint triggers each time, but within Save Invoice it calls the Application.SaveAs function, when this print is fired from the ribbon this simply seems to get ignored

    Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
    'check worksheet belongs to bvs plugin
    If CheckRangeExists("BVSID") = 1 Then
        'code to save
        If SaveInvoice = 0 Then Cancel = True
    End If
    End Sub

    incase there is something weird i'm missing, this is the line that throws up no errrors, simply doesn't work, more annoyingly the SaveInvoice function returns no error, which in turn prints out the document, and its only until after you check for the file you would realise it didn't save, I've since put a check in to check the file has saved, so far everysingle time it does not save from the ribbon, pressing CTRL P or clicking File Print will always save the file?

    ActiveWorkbook.SaveAs Filename:=Path & Range("Invoice").Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    before I have Application.DisplayAlerts = False and after Application.DisplayAlerts = True

    As the code is within an Addin my probably overly basic idea is to create a hidden range with the name BVSID, with the idea the events only fire with the sheets I've created

    Finally, If I type the

    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, IgnorePrintAreas:=False

    in the debug window the code also runs, triggers the before print and saves the files

    any help would be most appreciated :)

    Friday, November 14, 2014 3:28 PM

All replies

  • Hello,

    What exactly do you need to implement?

    In case if you need to intercept the built-in Ribbon control, see Temporarily Repurpose Commands on the Office Fluent Ribbon .

    It is not possible to override Backstage UI commands from the custom UI XML markup or by using the object model. A possible solution would be to consider hiding the built-in UI and rebuilding it fully with custom commands. You can read more about that in the Introduction to the Office 2010 Backstage View for Developers article.

    Saturday, November 15, 2014 2:09 PM
  • Hi ITStead,

    >>but within Save Invoice it calls the Application.SaveAs function, when this print is fired from the ribbon this simply seems to get ignored<<

    I suggest that you debug the code to step into the SaveInvoice function to see whether the code was executed.

    >>I decided to make things easier by putting commands onto the ribbon and creating an addin, as this code is reused by most of our workbooks, and having thousands of files with this code in seems like an awesome way to fill up our drives :)<<

    Did you create add-in with Visual Studio? If I understood correctly, we can easily custom the ribbon to run the macros in Excle like figure below:

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 17, 2014 3:19 AM
    Moderator
  • Thanks for the replies, the addin is created within Excel

    I've created a custom ribbon using the CustomUI Tool,

    Debugging the code in excel, it doesn't throw up any error when processing the save as command, just simply doesn't do it, the code gets executed fine, I've put in debug messages before and after the SaveAs command for testing when not debugging manually.

    The files get saved to a network share, I've tried testing with a local path but still get the same strange behaviour.

    Like I said, if I pop this into the debug window, it works as intended

    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, IgnorePrintAreas:=False
    

    I just tried adding it using the ribbon commands, but the menu commands I've written don't show up

    Apologies if i'ts not very clearn, just not sure how to explain it, if I run this command from anything other than using the ribbon button it works and saves the invoice


    Monday, November 17, 2014 8:51 AM
  • Hi,

    just to update, I've just used the normal print command now on the ribbon,

    after a bit more testing printing with the printout command just seems to ignore the saveas command, I thought it worked in the debug window when typing it in before but it doesn't.  Tried it on a few work pc's and my own machine at home (all running windows 8.1 Pro x64 with office 2013, all latest updates installed)

    just using in the ribbon happily works with the before print function on the workbook, its only one extra click to click ok, and in the long run maybe better as you get to select the printer

    <button idMso="FilePrint" imageMso="FilePrint" size="large"/>	
    Thanks for the help :)

    Tuesday, November 18, 2014 10:39 AM
  • Hi ITStead,

    Thanks for the detail explaintation for this issue.

    >>Debugging the code in excel, it doesn't throw up any error when processing the save as command, just simply doesn't do it, the code gets executed fine, I've put in debug messages before and after the SaveAs command for testing when not debugging manually.<<

    If I understood correctly, the issue is that you when you click a print button which added by CustomUI Tool, the before print event was fired but the save as function in this event didn't execute.

    I am trying to reprduce the issue however failed. Here are my steps:
    1. custom the ribbon

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    	<ribbon startFromScratch="false">
    		<tabs>
    			<tab id="customTab" label="Custom Tab">
    				<group id="customGroup" label="Custom Group">
    				
    <button idMso="FilePrint" imageMso="FilePrint" size="large"/>	
    				</group>
    			</tab>
    		</tabs>
    	</ribbon>
    </customUI>

    2. Add a before print event:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveWorkbook.SaveAs "C:\Users\UserName\Desktop\Book1_Copy.xlsm", XlFileFormat.xlOpenXMLWorkbookMacroEnabled
    End Sub

    Could you reproduce the issue using steps above?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 19, 2014 9:13 AM
    Moderator
  • Thanks for the time!

    it works fine when using the normal print button on the custom ui, its when using a macro combined with

    ActiveWindow.SelectedSheets.PrintOut 


    I've just created a blank workbook, with a class module to capture the before print, I've put 2 buttons on the custom ribbon, one using the default command, and the other to call a macro to reference the printout command

    before print gets activated on both tries, however the saveas command gets ignored, seems to run, but not do anything when called using the .PrintOut command.

    Here's the code I've used to test

    This Workbook code

    Private XLApp As CExcelEvents
    'set applications to be avaliable in the cexcelevents class module
     Private Sub Workbook_Open()
         Set XLApp = New CExcelEvents
     End Sub

    Module for custom button (not actually sure if this needs to be in a module but this is to mimic the same behaviour i'm getting with the addin

    Sub PrintInvoice(control As IRibbonControl)
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, IgnorePrintAreas:=False
    End Sub

    Class Events CExcelEvents

    'get application events
    Private WithEvents App As Application
    
    Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
    ActiveWorkbook.SaveAs "C:\Test\Book1_Copy.xlsm", XlFileFormat.xlOpenXMLWorkbookMacroEnabled
    End Sub
    
     Private Sub Class_Initialize()
         Set App = Application
     End Sub
    
    
    Private Sub Class_Terminate()
        Set App = Nothing
    End Sub
    
    

    CustomUI Code

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> 
    <ribbon startFromScratch="false"> 
    <tabs> 
    <tab id="customTab" label="Custom Tab"> 
    <group id="customGroup" label="Custom Group"> 
    	<button idMso="FilePrint" imageMso="FilePrint" size="large"/>
    	<button id="btnPrint" label="Print" imageMso="FilePrint" size="large" onAction="PrintInvoice" supertip="Saves Invoice Then Prints to Default Printer" enabled="true"/>
    </group> 
    </tab> 
    </tabs> 
    </ribbon>
     </customUI>
    the button id btnprint is what I've been testing with so I copy pasted that button from the customui code I had

    I can't see away to add the workbook as an attachment, if needed I can upload it here too, this workbook was just created from scratch just incase its the addin I've got making some weird error
    • Edited by ITStead Wednesday, November 19, 2014 11:51 AM additional info
    Wednesday, November 19, 2014 11:48 AM
  • Hi ITSTead,

    I am trying to reproduce the issue but failed. The beforePrint event was not fired when we click the second button using printOut function.

    I also noticed that the two dialogs after we click the two buttons on the ribbon were different. And the code below worked for me to save the workbook, please let me know wheterh it worked for you:

    Sub PrintInvoice(control As IRibbonControl)
    Application.CommandBars.FindControl(ID:=4).Execute
    End Sub
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 20, 2014 7:39 AM
    Moderator
  • Hi,

    using the following line to print it does trigger the before print function and saves for me,i'm guessing that's doing the same thing as calling the command using? '<button idMso="FilePrint" imageMso="FilePrint" size="large"/>'

    Application.CommandBars.FindControl(ID:=4).Execute

    I've uploaded the book to one drive here http://1drv.ms/1HpiOhZ if it helps

    I think you need to close and restart excel for the app events to take over, at least that's what seems to work for me, not sure if that's the correct way to do it mind, I'm mainly self taught so I'm aware I could be doing it wrong!


    Thursday, November 20, 2014 10:48 AM
  • Hi ITStead,

    Thanks for the sample workbook, I am able to reprodcue the issue as you metioned.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day!

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 21, 2014 8:40 AM
    Moderator
  • Thanks for looking into it!

    Monday, November 24, 2014 11:32 AM
  • Just wondering if there was any news?

    Friday, December 5, 2014 11:17 AM
  • Hi,

    I did a research on the issue. I am able to reproduce the issue.

    At this point I am under the impression that this need deep-down analysis. Because of its complexity, your question falls into the paid support category which requires a more in-depth level of support.  If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. Please visit the below link to see the various paid support options that are
    available to better meet your needs. 
    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone


    Tuesday, December 23, 2014 6:07 PM
  • Thanks for the reply,

    unfortunately I'm not an IT professional, paying that much for the support is not an option, i'm just a bit of a hobbyist who's trying to make life easier for us in the office :)

    Tuesday, January 13, 2015 6:31 PM