Unprotecting a sheet once a text box is selected

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,714
Can you unprotect a sheet once a text box is selected and then reprotect it again when it is unselected?

I do not want to use active x text boxes, just normal text boxes.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,576
Headed to sleep for the night. Will check in the morning.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,714
I thought of a way to do it. I could lock the area from about 7 rows below the table to the top of the sheet. Then I could have a label saying to type notes below the locked area. Just not sure how to code it.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,253
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You can lock a range of cells, but it won't take effect until you protect the sheet....then you won't be able to type notes into that protected sheet !!
 
L

Legacy 456155

Guest
Can you unprotect a sheet once a text box is selected and then reprotect it again when it is unselected?

I do not want to use active x text boxes, just normal text boxes.


?

VBA Code:
Sub TextBox1_Click()
    Unprotect
    ActiveSheet.Shapes("TextBox 1").Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Protect
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,714

ADVERTISEMENT

How would you select the text box if the workbook is locked?
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,253
Office Version
  1. 2013
Platform
  1. Windows
You can't.....you must unprotect the sheet first...note the unprotect line in Dataluvers code
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,576
Here's a workaround :

VBA Code:
Option Explicit

Sub TextBox1_Click()
    Unprotect
    ActiveSheet.Shapes("TextBox 1").Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Application.ScreenUpdating = False
        If Me.Range("A1:Z20").Select Then
            Beep
            Me.Range("A21").Select
            Exit Sub
        End If
    Application.ScreenUpdating = True
  
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,942
Messages
5,525,769
Members
409,663
Latest member
littleriver

This Week's Hot Topics

Top