Solved How can i lock the cell

January 7, 2018 at 22:49:35
Specs: Windows 7
Hi,
I need your assistance for a VBA Formula to make the cells locked in excel.

For Example
I have 4 selection on cell G4 which includes Working, Left, Vacation, & Final Settelment. I need to lock cell ranging from H4 TO AM4 if i select Left or Vacation. Can you help me on this.


Thanks & Regards
Diwas R. Bhattarai


See More: How can i lock the cell

Reply ↓  Report •

✔ Best Answer
January 10, 2018 at 09:41:32
In the future, please do not chose a Best Answer until you are sure that your issue has been resolved. I have reset the Best Answer for the time being.

Try the following code. Based on a change in Column 7 (G) it will Lock the cells in the Row that was changed for Left and Vacation and Unlock them for anything else. If you need to specifically check for Working or Final Settlement, just add the If instruction.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 7 Then
    If Target = "Vacation" Or Target = "Left" Then
      With Target
       Me.Unprotect 'Password:="myPassword"
        Me.Range(Cells(.Row, "H"), Cells(.Row, "AM")).Locked = True
       Me.Protect 'Password:="myPassword"
      End With
    Else
      With Target
       Me.Unprotect 'Password:="myPassword"
        Me.Range(Cells(.Row, "H"), Cells(.Row, "AM")).Locked = False
       Me.Protect 'Password:="myPassword"
      End With
    End If
 End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
January 8, 2018 at 12:01:05
In order for cells to be Locked on a worksheet, the sheet itself must be Protected. Even though all cells are actually Locked by default, the Lock doesn't take effect until the worksheet is Protected.

The standard process is to Unlock any cells that you want to allow users to access and then Protect the worksheet. (You can even Unlock every cell in the entire sheet and then Protect it. Depending on what Protection options you choose, the sheet may not even appear to the users as being Protected.)

Once that is accomplished, you can then use VBA to Unprotect the sheet, lock a range of cells and then Protect the sheet again. You can even include a password within the VBA, however, unless you also password protect the VBA code, any user could access it and see the worksheet password.

In the code below I have included the syntax for using a password but commented it out. It's just there so that you can how it can be used. Again, I have to stress that it makes no sense to use the Password in VBA unless you also protect the VBA project.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$G$4" Then
    If Target = "Vacation" Or Target = "Left" Then
      Me.Unprotect 'Password:="myPassword"
       Me.Range("H4:AM4").Locked = True
      Me.Protect 'Password:="myPassword"
    End If
 End If
End Sub


One last thing: You didn't say whether or not you want H4:AM4 to be Unlocked if G4 is changed from Vacation/Left to one of the other choices. If you need help with that also, let me know.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
January 8, 2018 at 22:18:22
Dear,
First of all thanks for your help. Yes surely i need to unlock H4:AM4 if G4 is changed.

Thanks
Waiting for your reply.


Reply ↓  Report •

#3
January 8, 2018 at 22:52:22
I Had done but there i got a problem i need to lock the row of that cell but the whole sheet has been locked. I don't want to lock the sheet as on another row there i need to change but if the sheet is protected then i can't do any changes

Thanks


Reply ↓  Report •

Related Solutions

#4
January 9, 2018 at 03:56:56
Did you manually Unlock all cells before protecting the Sheets?

Did you perform the steps shown here?

https://www.technipages.com/excel-l...

message edited by DerbyDad03


Reply ↓  Report •

#5
January 9, 2018 at 06:08:33
I see that the thread has been marked as solved, but it's not clear to me if all of your issues have been resolved.

Are you all set or do you need more help?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
January 9, 2018 at 22:21:41
Hi,
Thanks for the help, but i need some thing more. Like wise i had locked the cell ranging from H4 TO AM4 if i select Left or Vacation. I need your assistance if i will select Working or Final Settlement it will automatically unlocked the cell.
And how can i do for all the rows as i had ranging cells from G4 TO G382 and the coloums H4 TO H382 & AM4 TO AM382. (Same thing lock and unlock)

Thanks
Diwas


Reply ↓  Report •

#7
January 10, 2018 at 09:41:32
✔ Best Answer
In the future, please do not chose a Best Answer until you are sure that your issue has been resolved. I have reset the Best Answer for the time being.

Try the following code. Based on a change in Column 7 (G) it will Lock the cells in the Row that was changed for Left and Vacation and Unlock them for anything else. If you need to specifically check for Working or Final Settlement, just add the If instruction.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 7 Then
    If Target = "Vacation" Or Target = "Left" Then
      With Target
       Me.Unprotect 'Password:="myPassword"
        Me.Range(Cells(.Row, "H"), Cells(.Row, "AM")).Locked = True
       Me.Protect 'Password:="myPassword"
      End With
    Else
      With Target
       Me.Unprotect 'Password:="myPassword"
        Me.Range(Cells(.Row, "H"), Cells(.Row, "AM")).Locked = False
       Me.Protect 'Password:="myPassword"
      End With
    End If
 End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
January 10, 2018 at 21:22:41
Thanks
For the solution. It really worked out.
Can you help me out how to change the colors of the cell.
Same like previous if The cell H is left or vacation the cell A & B will be green and if its final settlement then it should be orange.

Regards
Diwas


Reply ↓  Report •

Ask Question