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.

 

[Logit]

Disregard the FORM stuff.

On Sheet, use the Sheet Module :

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.

 

[Michael M]

@dpaton05
If you use a regular textBox you don't need code !

 

[dpaton05]

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?

 

[Legacy 456155]

 

[dpaton05]

I added this code to my sheet.

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]

What cell do you want it to go to ?

 

[Logit]

ActiveSheet.Range("A1").Select

 

[dpaton05]

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]

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]

My apologies ... someone else will need to step in and assist.

Anyone ??