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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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

P
  • Solved
Disable 'X' on Worksheet
Replies
4
Views
117
Pacman52
P
P
  • Question Question
Selected Cell Shown in Different Cell
Replies
2
Views
262
Pacman52
P
C
  • Solved
Need Help VBA
2
Replies
10
Views
427
Trebor76
T
L
  • Question Question
VBA - Run time error '9': subscript out of range
Replies
3
Views
182
RoryA
RoryA
V
  • Question Question
Copy Range from Multiple Sheets and Paste Link into a Single Sheet
Replies
2
Views
182
vbascientist
V

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

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