how do i create worksheet change event only for a specific column i.e. q:q

123amitjain

New Member
Joined
Sep 10, 2013
Messages
5
the below codes are for entire sheet , but i just want to run the just for the entire column q
i just want to lock the column q for the entry just for one time.
how do i ?
i tried many times ....can anybody help me?






Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range


ActiveSheet.Unprotect

For Each cl In Target
If cl.Value <> "" Then
check = MsgBox("is this entry correct? this cell cant be edited after once entered", vbYesNo, "cell Lock Notification")
If check = vbYes Then
cl.Locked = True
Else
End If
End If
Next cl
ActiveSheet.Protect
End Sub
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,084
Office Version
  1. 365
Platform
  1. Windows
Will you be changing one cell at a time, or multiple cells?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,084
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Or Target.Value = "" Then Exit Sub
   If Not Intersect(Target, Range("Q:Q")) Is Nothing Then
      Me.Unprotect
      If MsgBox("is this entry correct? this cell cant be edited after once entered", vbYesNo, "cell Lock Notification") = vbYes Then
         Target.Locked = True
      End If
      Me.Protect
   End If
End Sub
 

123amitjain

New Member
Joined
Sep 10, 2013
Messages
5
Ok, how about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Or Target.Value = "" Then Exit Sub
   If Not Intersect(Target, Range("Q:Q")) Is Nothing Then
      Me.Unprotect
      If MsgBox("is this entry correct? this cell cant be edited after once entered", vbYesNo, "cell Lock Notification") = vbYes Then
         Target.Locked = True
      End If
      Me.Protect
   End If
End Sub

wow
it works
i have been trying solving for this code from a long time .
but i couldn't do that perfectly
...
u r genius
hats off to you
thank you so much
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,084
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,175
Messages
5,576,544
Members
412,730
Latest member
Thundereagle
Top