Unprotecting a sheet once a text box is selected

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,699
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Logit

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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,699
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,237
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 !!
 

dataluver

Well-known Member
Joined
Jan 17, 2020
Messages
537
Office Version
  1. 365
Platform
  1. Windows
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,699

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,237
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,575
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,595
Messages
5,523,796
Members
409,534
Latest member
mhearst

This Week's Hot Topics

Top