Unprotecting a sheet once a text box is selected

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,484
Disregard the FORM stuff.

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
Easiest way to get to the Sheet Code Module is to right click on the sheet tab and select VIEW CODE.
The code window is the large white pane on the right.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,581
But I want to lock the spreadsheet. Can you lock the spreadsheet and still be able to press enter for additional text in the text box?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,581
I added this code to my sheet.

VBA Code:
Private Sub TextBox4_GotFocus()
    ActiveSheet.Unprotect
End Sub

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

Private Sub TextBox4_LostFocus()
    ActiveSheet.Protect
End Sub
Whenever the worksheet is unprotected, I am able to go to new lines fine but with the above code and the workbook protected, it seems to jump to a random unlocked cell whenever I press enter.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,484
VBA Code:
ActiveSheet.Range("A1").Select
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,581
If I don't have anything selected, cell A11911 is selected. If I have a cell within the unlocked cells selected and then type in the box and press enter, the cell below the cell I had selected before I hit enter gets selected
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,581
I want to be able to go to a new line within the text box. Depending on how much information is needed above it, the box could be anywhere on the sheet as it moves with the data. I enter a table row and that pushes it down.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,484
My apologies ... someone else will need to step in and assist.

Anyone ??
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,865
Messages
5,507,801
Members
408,647
Latest member
gazmoz17

This Week's Hot Topics

Top