Excel VBA: How to quickly close multiple code windows in the VBA Editor
HTML-код
- Опубликовано: 19 окт 2024
- // FREE Excel E-book "Record Your First Macro"
→ www.launchexce...
// Recommended Excel Courses //
1. Launch Excel Macros & VBA School: go.launchexcel...
2. Excel Campus Elevate Program: go.launchexcel...
3. My Online Training Hub Dashboards: go.launchexcel...
4. Coursera Excel Specialisation: go.launchexcel...
5. Coursera Everyday Excel: go.launchexcel...
6. Coursera Excel Data Visualisation: go.launchexcel...
// Recommended Excel Templates //
If you are looking for pre-built, easy to use spreadsheets, check out Simple Sheets.
go.launchexcel...
They have Excel templates covering cash flow planning, invoices, expense reports, personal budgets, and more!
// Notes //
Here's the code to copy and paste:
------------------------------------------------------------------
Sub Close_All_VBE_Windows() 'CR v5207
'// Source: access-program...
'// Posted: Apr 18, 2017
'// Thanks to: Colin Ridders (access-program...)
'
'// Modified by Victor Chan Dec 15, 2017
'// PURPOSE: Closes all VBE windows except this one
'
'// Note: Needs library 'Microsoft Visual Basic for Applications Extensibility'
'// 1. Click on Tools ... References in the VBE
'// 2. Scroll down and tick the entry for Microsoft Visual Basic for Applications Extensibility 5.3
On Error GoTo Err_Handler
Dim vbWin As VBIDE.Window
For Each vbWin In Application.VBE.Windows
If (vbWin.Type = vbext_wt_CodeWindow Or _
vbWin.Type = vbext_wt_Designer) And _
Not vbWin Is Application.VBE.ActiveWindow Then
vbWin.Close
End If
Next
Exit_Handler:
Exit Sub
Err_Handler:
'CR 02/02/2016 - added error handling to fix issue in 64-bit Office
If Err.Number = 424 Then Resume Next 'object required
MsgBox "Error " & Err.Number & " in Close_All_VBE_Windows procedure: " & Err.Description
Resume Exit_Handler
End Sub
------------------------------------------------------------------
--
// EPISODE NOTES
Please follow these instructions:
▸ Copy and paste the code into a Personal Macro workbook module
▸ Click on Tools ... References in the VBE
▸ Scroll down and tick the entry for Microsoft Visual Basic for Applications Extensibility 5.3
▸ Run the code (F5) and it will close every code window except the active window
--
// REFERENCES
The VBA code was from the following page:
access-program...
Thanks Colin Ridder for sharing!
--
// VISIT MY WEBSITE
For more tutorials on Microsoft Excel and VBA visit the website
→ www.launchexcel.com
I recommend you paste this code into your personal macro workbook.
If you're totally new to Excel Macros & VBA... get my FREE e-book at this link:
DOWNLOAD EBOOK → launchexcel.lpages.co/first-macro-ebook
Leave comments with any questions you have...
And click on the LIKE button if you found this video helpful.
Thanks for supporting my channel!
Thank you very much Victor.
It is very interesting
Like!
Thank you so much! it works fine.
Glad it helped
Is there anyway to assign this to a shortcut or menu button within the vbe?
Use the Application.OnKey method. Actually, this is the method that should be used in VBA projects that will be locked with the Unviewable+ Ultimate level of protection. Here are some demo files (unlocked source code & Unviewable+ VBA locked)
spreadsheet1.box.com/shared/static/at1640ly58xiey474ona0xv39a39ynh4.zip
msdn.microsoft.com/en-us/vba/excel-vba/articles/application-onkey-method-excel
Hi there,
I have copied the code and activated the library, however, the code returns the error in message box. The loop doesn't seem to work. Any idea?
I figured it out...!!!
It had to do with macro settings:
File > Options > Trust Center > Trust Center Settings > Enable 'Trust access to the VBA project object model'
If the above is not enabled, you will always receive the error in message box.
Thanks all excel users.......
Great... that's good to know.