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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
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
50,653
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
50,653
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,279
Messages
5,571,285
Members
412,375
Latest member
BRJoeyMelo
Top