combine 2 macro code in to one so that i can use it in single sheet....its urgentttt

[girishhb]

Please combine the below given both the codes so that i can use it in one module.
1. to insert current date and time in A column when data entered in B column.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("B:B")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value = "" Then Target.Offset(0, -1).Value = ""
If Target.Value <> "" Then Target.Offset(0, -1).Value = Now
End Sub


2 . To lock the range of cells when data is entered.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:F1048576")) Is Nothing Then 'set your range here
ActiveSheet.Unprotect Password:="mypassword"
Target.Locked = True
ActiveSheet.Protect Password:="mypassword"
End If
End Sub

Thanks in advance for your help

[westconn1]

try like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:F1048576")) Is Nothing Then 'set your range here
ActiveSheet.Unprotect Password:="mypassword"
Target.Locked = True
if target.column = 2
if not isempty(target) then
target.offset(, -1) = now
else
target.offset(, -1) = ""
end if
ActiveSheet.Protect Password:="mypassword"
End If
End Sub
not tested at all, you will need to see if it does as you require

[lecxe]

Hi girishhb

The second code assumes that the worksheet is protected, and the first doesn't. What should the merged code consider?

[david000]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
With Me
If Not Intersect(Target, .Range("A:F")) Is Nothing Then

.Unprotect Password:="xyz"
.Cells.Locked = False

Set rng = .Range("B:B")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value = "" Then Target.Offset(0, -1).Value = ""
If Target.Value <> "" Then Target.Offset(0, -1).Value = Now

.UsedRange.SpecialCells(xlCellTypeConstants).Locked = True
.Protect Password:="xyz"
End If
End With
End Sub