I added my macro's to the quick access toolbar but the buttons disappear when I run the code

[Jurg55]

Hi,

I added my macro's to the quick access toolbar but the buttons disappear after the code is finished.
I go through the code with F8 but the buttons disappear only after the code is finished (not during the code in a certain rule). So, I don't think the code itself is causing this. But still it could be caused by the code because other codes does not remove the quick access toolbar buttons.
The strange thing is that sometimes the buttons stay in the toolbar and sometimes they disappear when I run this particular code.
As a workaround I tried to add the macro's to the ribbon instead, but also these buttons disappear when I run this code.

How can I solve this?
Or are there other ways to make a quick acces button.

Thank you!

 

[mumps]

You could insert a shape on your worksheet and assign the macro to it.

 

[Jurg55]

You could insert a shape on your worksheet and assign the macro to it.

No, I cannot adapt the workbook because it is a workbook I export from another software. I need a button on the ribbon or the access toolbar, so I can start the code for every file I open in Excel and not for only one particular worksheet.

 

[My Aswer Is This]

You said:
I added my macro's to the quick access toolbar but the buttons disappear after the code is finished.

How did you: add your macro's to the quick access toolbar?

Did you select File then options then quick access tool bar

 

[Jurg55]

Hi,

I know now what the problem is, but I still have no solution.

I show you the following code:

Sub OpenFile01()

    Dim wb100 As Workbook, wb200 As Workbook

    Application.ScreenUpdating = False
    
    Dim PathEquip As String
    PathEquip = "C:\Users\Desktop\EquipmentList\EquipmentList.xlsx"

    Dim EquipFile As String
    EquipFile = "EquipmentList.xlsx"
    
    Set wb100 = ThisWorkbook
    Set wb200 = Workbooks.Open(PathEquip)
    
    Workbooks(EquipFile).Sheets("Blad1").Copy After:=Workbooks("Werkblad uit Basis (1)").Sheets(2)
    
    wb200.Close
    
    Application.ScreenUpdating = True

End Sub

The line wb200.Close is causing this issue I described.
If I remove the close workbook line. the problem is solved. The macro's do not disappear from the quick access toolbar.
But I really want to close "EquipmentList.xlsx", so i need to use the following line:
Workbooks("EquipmentList.xlsx").Close SaveChanges:=False
But this line does not work, because this line ensures that the macro's disappear from the quick access toolbar.
Here you can see the quick access toolbar and the button is not there in the second picture.

I do not run the macro from EquipmentList.xlsx. I run the macro from thisworkbook. So what I basically want is to open EquipmentList.xlsx then copy a sheet and paste it in thisworkbook and then close EquipmentList.xlsx. But it looks like my code is not right or there is some bug.

How can I close a workbook without having this issue?



Thank you

 

[My Aswer Is This]

Sorry I have no answer

 

[MARK858]

I do not run the macro from EquipmentList.xlsx. I run the macro from thisworkbook.

ThisWorkbook is just a module within a workbook (for instance ThisWorkbook is a module within workbook Book1 in the image below), so what workbook is the macro actually in?

 

[My Aswer Is This]

You never answered this question from my previous post.

How did you: add your macro to the quick access toolbar?

Did you select File then options then quick access tool bar

 

[Jurg55]

You never answered this question from my previous post.

How did you: add your macro to the quick access toolbar?

Did you select File then options then quick access tool bar

Yes