0

Before asking a question I reviewed many options and nothing helped me!

I have in PERSONAL.XLSB some code. This VBA Saves and closes all books after clicking on the red cross. It works without any problems:

Private WithEvents xlApp As Application

Private Sub Workbook_Open()
    Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    xlApp.ScreenUpdating = False
    xlApp.DisplayAlerts = False
    xlApp.EnableEvents = False
    For Each Wb In xlApp.Workbooks
        Wb.Save 'Wb.Close True
    Next
    xlApp.Quit
End Sub

I have another code which I want to work, when I open the Excel. Separately, this code works, but I do not know how to combine? This code opens my own tab on Ribbon:

Private Sub Workbook_Open()
    SendKeys "%BP{F6}", True
End Sub

Want to work when I open any Workbook.

rediffusion
  • 65
  • 1
  • 10
  • so.... why not just combine them? You already have a Workbook_Open event, just add the code of one event to the other? – Tim Stack Jun 27 '19 at 14:57
  • @TimStack Listen i try to put `SendKeys "%BP{F6}", True` before and after this string `Set xlApp = Application`. Not helped. Any idea? – rediffusion Jun 27 '19 at 15:05
  • Does the line even get executed? Put a break on it so you'll see if it does – Tim Stack Jun 27 '19 at 15:06
  • 1
    Putting a break on `SendKeys` is never a good idea...Thought you could explain us why the SendKeys, is a shortcut for another procedure? Because you could call it directly instead using the shortcut like this. – Damian Jun 27 '19 at 15:15
  • Select `xlApp` from the left-hand dropdown at the top of the code pane. Then select `WorkbookOpen` from the right-hand dropdown. That will create a `xlApp_WorkbookOpen` handler that will run whenever a workbook is opened by the `xlApp` application instance... which is *exactly* how you should have come up with this `WorkbookBeforeClose` handler (or did you paste it from some website instead?) – Mathieu Guindon Jun 27 '19 at 15:27
  • What's that `SendKeys` for anyway? 99.99% of the time, you don't want to use `SendKeys`. – Mathieu Guindon Jun 27 '19 at 15:28
  • @Mathieu Guindon ··· 1) I used this `xlApp_WorkbookOpen` not helps (i did paste it from [some website](http://www.cyberforum.ru/vba/thread561320.html#post13682495) instead). Can you post complete `code` with this action (maybe I'm doing something wrong)!? 2) 0Key Mathieu i see you have better option than `SendKeys`, please if so feel free to share with me, thx! – rediffusion Jun 27 '19 at 16:11
  • If we don't know what you're using SendKeys for, then how are we supposed to be able to suggest better alternatives? – Mathieu Guindon Jun 27 '19 at 16:16
  • Don't paste event handlers from websites. **Have the VBE create them for you**. That way you *know* they're understood as event handlers and not just some random useless procedure that isn't getting invoked by anything. – Mathieu Guindon Jun 27 '19 at 16:16
  • 1
    Ok, I think you're using `SendKeys` to activate a custom Ribbon tab. If that's the case then you could modify the XML to specify an `onload` handler and invoke the `IRibbonUI.ActivateTab` method - [like here](https://stackoverflow.com/a/36113728/1188513) – Mathieu Guindon Jun 27 '19 at 16:21
  • @MathieuGuindon ··· It's me again. Thx for info but i have `Office 2019` This method I have tried and it does not work. About this there is [post](https://stackoverflow.com/questions/56623675/vba-iribbonui-activatetab-not-working-in-office-2019). – rediffusion Jun 27 '19 at 17:03

1 Answers1

0

Finally i solve this issue!

So if we have the codes then just shove inside and that's it:

Workbook_Open()

As for this line the problem was in the version of Office:

SendKeys "%БП{F6}"

In Microsoft Office 365 2019 everything works well!

rediffusion
  • 65
  • 1
  • 10