Shortening/combining VBA

T

TheJay

Active Member
Joined
Nov 12, 2014
Messages
364
Office Version
  1. 2019
Platform
  1. Windows
Hello there, I want to assign keyboard shortcuts to save me time showing toolbars/unlocking worksheets and hiding toolbars/locking worksheets. I've created a VBA macro module called "Tools" and within in, two subs. It's not working at the moment and I think in part it's because I need to combine the code, but not sure exactly how to do this whilst ensuring it works.

For example, are "Sheet" and "Worksheet" considered the same thing by Excel VBA?

VBA Code: 
Sub ProtectHide()
    Dim Sht As Sheet
        For Each Sht In ActiveWorkbook.Sheets
'        ActiveSheet.Protect "YourPassword"
        ActiveSheet.Protect
        Next Sht
    Dim wsSheet As Worksheet
    Application.ScreenUpdating = False
    For Each wsSheet In ThisWorkbook.Worksheets
        If Not wsSheet.Name = "Blank" Then
            wsSheet.Activate
            With ActiveWindow
                .DisplayFormulas = True
                .DisplayGridlines = False
                .DisplayHeadings = False
                .DisplayStatusBar = False
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = False
                .CommandBars("Standard").Visible = False
                .CommandBars("Formatting").Visible = False
            End With
        End If
    Next wsSheet
    Application.ScreenUpdating = True
End Sub

Sub UnprotectShow()
    Dim Sht As Sheet
        For Each Sht In ActiveWorkbook.Sheets
'        ActiveSheet.Protect "YourPassword"
        ActiveSheet.Unprotect
        Next Sht
    Dim wsSheet As Worksheet
    Application.ScreenUpdating = False
    For Each wsSheet In ThisWorkbook.Worksheets
        If Not wsSheet.Name = "Blank" Then
            wsSheet.Activate
            With ActiveWindow
                .DisplayFormulas = True
                .DisplayGridlines = False
                .DisplayHeadings = True
                .DisplayStatusBar = True
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = True
                .CommandBars("Standard").Visible = True
                .CommandBars("Formatting").Visible = True
            End With
        End If
    Next wsSheet
    Application.ScreenUpdating = True
End Sub

Thanks for your help.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Failed attempt

Compile error:
"Next without For"

VBA Code: 
Sub ProtectHide()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = "Blank" Then
            ws.Activate
            ActiveSheet.Protect
            With ActiveWindow
                .DisplayFormulas = False
                .DisplayGridlines = False
                .DisplayHeadings = False
'            End With
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = False
                .DisplayStatusBar = False
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = False
                .CommandBars("Standard").Visible = False
                .CommandBars("Formatting").Visible = False
            End With
'        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Sub UnprotectShow()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = "Blank" Then
            ws.Activate
            ActiveSheet.Unprotect
            With ActiveWindow
                .DisplayFormulas = True
                .DisplayGridlines = False
                .DisplayHeadings = True
'            End With
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = True
                .DisplayStatusBar = True
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = True
                .CommandBars("Standard").Visible = True
                .CommandBars("Formatting").Visible = True
            End With
'        End If
[B]    Next ws[/B]
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
TheJay said:
Failed attempt

Compile error:
"Next without For"
Click to expand...
That is because you commented out mandatory "End With" and "End If" lines of code.
Every "If" needs a matching "End If" (unless doing the entire IF ... THEN on a single line) and every "With" needs a matching "End With".
 
Upvote 0
Solution
Thanks.

VBA Code: 
Sub ProtectHide()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = "Blank" Then
            ws.Activate
            ActiveSheet.Protect
            With ActiveWindow
                .DisplayFormulas = False
                .DisplayGridlines = False
                .DisplayHeadings = False
            End With
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = False
                .DisplayStatusBar = False
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = False
                .CommandBars("Standard").Visible = False
                .CommandBars("Formatting").Visible = False
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Sub UnprotectShow()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = "Blank" Then
            ws.Activate
            ActiveSheet.Unprotect
            With ActiveWindow
                .DisplayFormulas = True
                .DisplayGridlines = False
                .DisplayHeadings = True
            End With
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = True
                .DisplayStatusBar = True
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = True
                .CommandBars("Standard").Visible = True
                .CommandBars("Formatting").Visible = True
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
If I want to edit this code so that it is activated only on the sheet being displayed, how would I modify this?
 
Upvote 0
TheJay said:
If I want to edit this code so that it is activated only on the sheet being displayed, how would I modify this?
Click to expand...
If you leave off all sheet/worksheet references, it will default to the active sheet.
And if you only want it to be effective on the active sheet, there is no need to loop through sheets.
 
Upvote 0
Thanks again. Like this?

VBA Code: 
Sub ProtectHide()
    Application.ScreenUpdating = False
            ActiveSheet.Protect
            With ActiveWindow
                .DisplayFormulas = False
                .DisplayGridlines = False
                .DisplayHeadings = False
            End With
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = False
                .DisplayStatusBar = False
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = False
                .CommandBars("Standard").Visible = False
                .CommandBars("Formatting").Visible = False
            End With
    Application.ScreenUpdating = True
End Sub

Sub UnprotectShow()
    Application.ScreenUpdating = False
            ActiveSheet.Unprotect
            With ActiveWindow
                .DisplayFormulas = True
                .DisplayGridlines = False
                .DisplayHeadings = True
            End With
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = True
                .DisplayStatusBar = True
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = True
                .CommandBars("Standard").Visible = True
                .CommandBars("Formatting").Visible = True
            End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Now it works perfectly.

VBA Code: 
Sub ProtectHide()
    Application.ScreenUpdating = False
            ActiveSheet.Protect
            With ActiveWindow
                .DisplayGridlines = False
                .DisplayHeadings = False
            End With
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = False
                .DisplayStatusBar = False
                .CommandBars("Full Screen").Visible = True 'Ribbon Menu
                .CommandBars("Worksheet Menu Bar").Enabled = False
                .CommandBars("Standard").Visible = False
                .CommandBars("Formatting").Visible = False
            End With
    Application.ScreenUpdating = True
End Sub

Sub UnprotectShow()
    Application.ScreenUpdating = False
            ActiveSheet.Unprotect
            With ActiveWindow
                .DisplayGridlines = False
                .DisplayHeadings = True
            End With
            With Application
                .DisplayFullScreen = False 'Ribbon Menu
                .DisplayFormulaBar = True
                .DisplayStatusBar = True
                .CommandBars("Full Screen").Visible = False
                .CommandBars("Worksheet Menu Bar").Enabled = True
                .CommandBars("Standard").Visible = True
                .CommandBars("Formatting").Visible = True
            End With
    Application.ScreenUpdating = True
End Sub

This is going to save so much time when wanting to edit a sheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

gheyman
  • Solved
Copy from one workbook to another (just format and values)
Replies
2
Views
421
gheyman
gheyman
rjplante
  • Solved
Change from Set as Default to Set as Active printer
Replies
1
Views
219
NdNoviceHlp
NdNoviceHlp
R
  • Solved
VBA Help to Automate Year
Replies
2
Views
130
RattlingCarp3048
R
T
  • Question
VBA code not working, have a sheet that copies contents of any unlocked cell to clipboard and want to change tab order
Replies
5
Views
609
TheJay
T
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
283
offthelip
O

Forum statistics

Threads
1,214,979
Messages
6,122,559
Members
449,089
Latest member
Motoracer88

Share this page

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