Unprotecting a sheet once a text box is selected

[dpaton05]

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.

 

[dpaton05]

Are you still there?

 

[Logit]

Yup

 

[Logit]

Headed to sleep for the night. Will check in the morning.

 

[dpaton05]

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]

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 !!

 

[Legacy 456155]

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.

?

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

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

 

[dpaton05]

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

 

[Michael M]

You can't.....you must unprotect the sheet first...note the unprotect line in Dataluvers code

 

[Legacy 456155]

See file "Textbox Select Unprotect.xlsm" in this folder.

 

[Logit]

Here's a workaround :

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