Long iteration with private sub sheet change, Shorten code help (Itteration), call macros, 3D ranges

excelover

New Member
Joined
Jun 27, 2012
Messages
3
I have a Private Sub Worksheet_Change(ByVal Target As Range) macro that calls 4 other macros.
It is long and takes too much time to run through 18 sheets.
1st Question. Can it be shortened?
2nd Question. I need to lock and unlock cells but need user to tab only unlocked cells in the 18 sheets.
3rd Question. How can I use a 3D named range instead of activating each sheet. (Shorten run time.)
The private sheet macro follows and the 4 macros follow that.
Thanks in advance,
Molly


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myCell2 As Range
Set myCell = Me.Range("A20")
Set myCell2 = Me.Range("A21")
ActiveSheet.Unprotect Password:=""
    If myCell.Value = 0 Then
         Me.PickTeams.Visible = False 'hide it if the value changes???
         Me.CheckBox1.Visible = False
         Call LockP3                'Call Sub LockP3
     Else
        Me.PickTeams.Visible = True 'unhide it if the value changes???
        Me.CheckBox1.Visible = True
        Call UnLockP3               'Call Sub UnlockP3
     End If
        ActiveSheet.Protect Password:=""
    
        If myCell2.Value = 0 Then
         Me.PickTeams.Visible = False 'hide it if the value changes???
         Me.CheckBox1.Visible = False
         Call LockP4                'Call Sub LockP4
    Else
        Me.PickTeams.Visible = True 'unhide it if the value changes???
        Me.CheckBox1.Visible = True
        Call UnLockP4               'Call Sub UnlockP4
    End If
        ActiveSheet.Protect Password:=""
End Sub
Code/

Code/

Sub LockP4()
'
' Lock range of cells in multiple worksheets then protect each worksheet.
On Error Resume Next
Dim wsSheet As Worksheet
Dim I As Long
    For I = 1 To 18
    Set ws = Worksheets("Hole " & I)
    ws.Unprotect Password:=""
        ws.Range("G8,I8,O7,P6,Q5").Locked = True
    
    With ActiveSheet
        .EnableSelection = xlUnlockedCells
    End With
        
        ws.Protect Password:=""
    Next I
    
End Sub
Sub UnLockP4()
'
' UNLock range of cells in multiple worksheets then protect each worksheet.
On Error Resume Next
Dim wsSheet As Worksheet
Dim I As Long
    For I = 1 To 18
    Set ws = Worksheets("Hole " & I)
    ws.Unprotect Password:=""
        ws.Range("G8,I8,O7,P6,Q5").Locked = False
    
    With ActiveSheet
        .EnableSelection = xlUnlockedCells
    End With
        
        ws.Protect Password:=""
    Next I
    
End Sub
Sub LockP3()
'
' Lock range of cells in multiple worksheets then protect each worksheet.
On Error Resume Next
Dim wsSheet As Worksheet
Dim I As Long
    For I = 1 To 18
    Set ws = Worksheets("Hole " & I)
    ws.Unprotect Password:=""
        ws.Range("G7,I7,O6,P5").Locked = True
    
    With ActiveSheet
        .EnableSelection = xlUnlockedCells
    End With
        
        ws.Protect Password:=""
    Next I
    
End Sub
Sub UnLockP3()
'
' UNLock range of cells in multiple worksheets then protect each worksheet.
On Error Resume Next
Dim wsSheet As Worksheet
Dim I As Long
    For I = 1 To 18
    Set ws = Worksheets("Hole " & I)
    ws.Unprotect Password:=""
        ws.Range("G7,I7,O6,P5").Locked = False
    
    With ActiveSheet
        .EnableSelection = xlUnlockedCells
    End With
        
        ws.Protect Password:=""
    Next I
    
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,283
Members
414,051
Latest member
tabecker

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
Top