Unprotecting a sheet once a text box is selected

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Presuming the textbox is located on the sheet ... only ActiveX Textboxes are allowed to be used there.
Regular Textboxes are used on UserForm.

If ActiveX on Sheet, use the Sheet Module :

VBA Code:
Option Explicit

Private Sub TextBox1_GotFocus()
MsgBox "Hi"
End Sub

'Replace MsgBox with your macro or a call to a macro

Private Sub TextBox1_LostFocus()
MsgBox "Bye"
End Sub


If Textbox on Form, in the Form Code Window:

Code:
Option Explicit

Private Sub TextBox2_Enter()
MsgBox "Hi"
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox "Bye"
End Sub
 
Upvote 0
What do you mean by "Regular text boxes are used on user form" as I have my regular text box on the sheet?
 
Upvote 0
Go to DEVELOPER tab, select INSERT. From the small window that pops up, the REGULAR TEXTBOX is greyed out.

The only thing to select is ACTIVEX TEXTBOX.

How did you manage to paste a REGULAR TEXTBOX ?
 
Upvote 0
Umm.....Insert>>Text>>TextBox ??
 
Upvote 0
Can you post a pic of what you are looking at ?
You can also place a "regular" (not Active-X) Textbox on a sheet programatically:
VBA Code:
Sub RegularTextBox()
Dim TBx As Shape
Set TBx = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 60, 40, 120, 120)
MsgBox TypeName(TBx)
End Sub
 
Upvote 0
That is FABULOUS !!

I just learned something new today. Thank you.
 
Upvote 0
I'm still interested learning how to paste a Regular Textbox on a sheet.
 
Upvote 0

Forum statistics

Threads
1,212,947
Messages
6,110,837
Members
448,302
Latest member
sniffit1st

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