I'm using the "Quick Access Toolbar" to add a button, the button is a macro in a macro holding document, I then can open other documents, press the button and it will do the macro even though they are .xlsx files.
For some reason the ThisWorkbook.Close (False) isn't fully closing the document, it disappears of the screen but it doesn't get closed because if I run the macro again it says that the file is in use and only available in read-only / notify.
I've tried to find a way round it by Nothing the workbook object for example: Set TargetWorkbook = Nothing but it's not working.
After many many runs and edits to the code, I've found the offending line that is causing the bug in Office 2010 (unsure about other versions)... How do I fix it and get around it?:
Sub macrofromtoolbar()
' Call Optimize
Call Optimize
' Declare Workbooks
Dim TargetWorkbook As Workbook
' Set Target
Set TargetWorkbook = ActiveWorkbook
' Copy the Sheet
ThisWorkbook.Sheets("TEST").Copy After:=TargetWorkbook.Sheets(1) ' <--- This is the line that is causing it to hang/get stuck in excel 2010
' Nothing... just to try and fix it :/
Set TargetWorkbook = Nothing
' Call Deoptimize
Call Deoptimize
' Force Close... but it doesn't fully close, it hangs on exit (not visible but the file is still being used)
ThisWorkbook.Close (False)
End Sub
Function Optimize()
'Optimize'
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
End Function
Function Deoptimize()
'Deoptimize'
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Function
Bookmarks