Debugging VBA Code

Spikenaylor

Board Regular
Joined
Apr 14, 2013
Messages
115
Office Version
  1. 365
Platform
  1. Windows
We are trying to Debug/verify code as part of our validation process

simple spreadsheet

code to auto lock on open and auto lock and save on close
Code:
Option Explicit


Private Sub Workbook_Open()


    Dim i As Long
    ThisWorkbook.Unprotect SheetPasswrd
    Application.WindowState = xlMaximized
    ThisWorkbook.Protect SheetPasswrd


    For i = 1 To Sheets.Count
        Sheets(i).Protect SheetPasswrd
    Next i


End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    
    Dim i As Long
    For i = 1 To Sheets.Count
        Sheets(i).Protect SheetPasswrd
    Next i
    
    ThisWorkbook.Protect SheetPasswrd
    Application.ScreenUpdating = True
    ActiveWorkbook.Close Savechanges:=True
    
End Sub

We can run through the debugging (F8) for the Workbook_Open

but cannot get the dubugging to run for the Workbook_BeforeClose

any idea why?

Regards
Thanks for looking
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
a couple of the subs I find tend to need a trigger so i would try

Code:
Sub Test
Workbook_BeforeClose
End Sub
 
Last edited:
Upvote 0
Put a breakpoint (F9) on this line of code,
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
and then when you close the workbook you should go into break mode where you can debug.
 
Upvote 0
Any sub that contains parameters the part inside the () cannot be run directly.
You either have to call it from another sub, as per mole999
Put a breakpoint, as per Norie
or put Stop as the first line of code.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top