vba code to update vba code in Thisworkbook

[Doug Mutzig]

Good afternoon all!

I am working on a workbook where I have been asked to lock down or turn off a lot of items (formula bar, ribbon, headings, tabs, copy/paste, dragging, etc.). I have this working but I have had to go back and do edits and other updates as changes are requested which causes a bit of a headache to manually go into vba > thisworkbook, and comment out everything, and then repeat the process to uncomment it all again. I was wondering if there is a way I could have a button on a hidden page that would run a macro to add/remove the comment " ' " from specific lines?

An example is:

Option Explicit
 
Private Sub Workbook_Open()
 
'Protect workbook structure, tabs, and worksheets
    'ThisWorkbook.Protect Password:="test", Structure:=True, Windows:=True
 
    Application.ScreenUpdating = False
 
'Turn off Formulabar, Tabs, Column & row headings, Ribbon, Drag&Drop, copy&paste, Ribbon, right-click menu
    'Application.DisplayFormulaBar = False
    'ActiveWindow.DisplayWorkbookTabs = False
    'ActiveWindow.DisplayHeadings = False
    'Application.CellDragAndDrop = False
    'Application.CutCopyMode = False
    'Application.OnKey "^c", ""
    'Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
       
'Turn off some error checking
    'Application.ErrorCheckingOptions.BackgroundChecking = False
   
   
'Go to the Index tab when opening
    Sheets("INDEX").Select
    Range("S6").Select
   
    MsgBox ("Workbook ready for editing" & vbNewLine & "Click OK to continue")
   
    Application.ScreenUpdating = True
End Sub
Private Sub Workbook_Activate()
 
    Application.ScreenUpdating = False
   
'Turns off items when clicking back on the workbook from a different application/workbook
    'Application.DisplayFormulaBar = False
    'ActiveWindow.DisplayWorkbookTabs = False
    'ActiveWindow.DisplayHeadings = False
    'Application.CellDragAndDrop = False
    'Application.CutCopyMode = False
    'Application.OnKey "^c", ""
    'Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
   
'Turns off some error checking
    'Application.ErrorCheckingOptions.BackgroundChecking = False
   
    Application.ScreenUpdating = True
       
End Sub

You can see where the "False" lines are currently commented out so that I can work without everything turned off. Is there a way to create a macro to remove the comments from the lines and then another button to add them? I tried using the macro recorder but it doesn't seem to record vba steps.

 

[Logit]

.
Good resource : https://stackoverflow.com/questions...ge-the-source-code-of-module-in-another-modul

Google "VBA dynamically edit macro code"

 

[Doug Mutzig]

Thank you, I will look and see what I can figure out.