Jaafar Tribak
Well-known Member
- Joined
- Dec 5, 2002
- Messages
- 9,621
- Office Version
- 2016
- Platform
- Windows
Hi all,
As you may all know, setting DisplayAlerts=FALSE doesn't prevent the warning message from poping up when attempting to edit/modify a protected worksheet.
The only two hacks that I know of are either using a Windows Timer or installing a CBT Windows hook that monitors the application for any window that is about to be created via the HCBT_CREATEWND Hook ID.
Unfortunately,although these two methods do work for stopping the sheet protection popup, they have problems of their own which outweigh their benefits .
Recently, By mere accident, I noticed that if you reset the VB Project by going to the VBE, clicking on the debugger Stop Button then pressing the Reset Button and finally executing Application.DisplayAlert=False in your code, if you follow these steps then the Sheet Protection waring popup no longer shows up and DisplayAlerts=False seems to take effect !
I don't know if this just the case with the excel edition that I am using (Excel 2010 64bit) but you can try it with your own and see if it is the same.
I just thought I would share this excel/vba peculiarity with you here but, at the same time I am hoping this might be a clue to how this can be accomplished in case anybody can build up on this and mke it work obviously without the need to programmatically (and brutally) reset the VBE as that would cause other serious issues such as losing variables,dealing with the VBE security restraints ... etc .
Regards.
As you may all know, setting DisplayAlerts=FALSE doesn't prevent the warning message from poping up when attempting to edit/modify a protected worksheet.
The only two hacks that I know of are either using a Windows Timer or installing a CBT Windows hook that monitors the application for any window that is about to be created via the HCBT_CREATEWND Hook ID.
Unfortunately,although these two methods do work for stopping the sheet protection popup, they have problems of their own which outweigh their benefits .
Recently, By mere accident, I noticed that if you reset the VB Project by going to the VBE, clicking on the debugger Stop Button then pressing the Reset Button and finally executing Application.DisplayAlert=False in your code, if you follow these steps then the Sheet Protection waring popup no longer shows up and DisplayAlerts=False seems to take effect !
I don't know if this just the case with the excel edition that I am using (Excel 2010 64bit) but you can try it with your own and see if it is the same.
I just thought I would share this excel/vba peculiarity with you here but, at the same time I am hoping this might be a clue to how this can be accomplished in case anybody can build up on this and mke it work obviously without the need to programmatically (and brutally) reset the VBE as that would cause other serious issues such as losing variables,dealing with the VBE security restraints ... etc .
Regards.
Last edited:
