Help with locking an entire column

[sangeeta25]

HI EVERYONE

I have the following code:
Sub TextBox3_Click()

Range("E3").EntireRow.Insert Shift:=xlDown
Range("E3") = Range("E4") + 1

End Sub

which is a macro so when a user clicks on this text box the macro is made to, insert an entire new row also whilst this occurs, as you can see in the code above, column E will automatically be updated with a new number which will be the next number above the number in the cell below.

So for instance I have row E5 which reads 543 then when I click my textbox to which the macro is assigned, a new row will be inserted and in the new E5 cell will read 544 and the old E5 cell will become E4.

That's just some background which I'm not sure is relevant to my query but I want to know how I can protect or lock or make the entire column E read only because I want my macro to still work but I just don't want anyone to be able to click into the cells and manually change the numbers in column E.

Is there a code I can add to my current macro above or do I need another code and what is this code?

Regards
S

 

[Fishboy]

HI EVERYONE

I have the following code:
Sub TextBox3_Click()

Range("E3").EntireRow.Insert Shift:=xlDown
Range("E3") = Range("E4") + 1

End Sub

which is a macro so when a user clicks on this text box the macro is made to, insert an entire new row also whilst this occurs, as you can see in the code above, column E will automatically be updated with a new number which will be the next number above the number in the cell below.

So for instance I have row E5 which reads 543 then when I click my textbox to which the macro is assigned, a new row will be inserted and in the new E5 cell will read 544 and the old E5 cell will become E4.

That's just some background which I'm not sure is relevant to my query but I want to know how I can protect or lock or make the entire column E read only because I want my macro to still work but I just don't want anyone to be able to click into the cells and manually change the numbers in column E.

Is there a code I can add to my current macro above or do I need another code and what is this code?

Regards
S

Hi Sangeeta25,

Is column E the only part of the sheet you want to lock / protect?

 

[sangeeta25]

Hello!!

Yes column E is the only part of the sheet I want to lock/protect but I still want my macro to work.

S

 

[Fishboy]

Hello!!

Yes column E is the only part of the sheet I want to lock/protect but I still want my macro to work.

S

Right then, to start with you will want to select ALL cells in the sheet then right-click and select Format Cells. Next, go to the protection tab and ensure that there is NOT a tick in the Locked box. Now select ALL of column E, right-click and select Format Cells again. Go to the protection tab and put a tick back in the Locked box.

Once this has been done it will mean that protecting the sheet will only lock column E.

You can now wrap your existing code in an unprotect / reprotect command as follows:

Sub TextBox3_Click()
ActiveSheet.Unprotect Password:="YourPassword"
Range("E3").EntireRow.Insert Shift:=xlDown
Range("E3") = Range("E4") + 1
ActiveSheet.Protect Password:="YourPassword"
End Sub

 

[sangeeta25]

Hello!!!

Thank you very much, it worked perfectly!


S

 

[Fishboy]

Hello!!!

Thank you very much, it worked perfectly!


S

Happy to help.