I would like to combine the two following codes into one. The first code as you can, is to add items to a list if it doesn't exist with a message. The second code as you can see, will enter current dates and change a positive value to a negative value.
Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim str As String
Dim i As Integer
Dim rngDV As Range
Dim rng As Range
Dim lCol As Long
Dim myRsp As Long
Dim strList As String
If Target.Count > 1 Or Target.Value = "" Then Exit Sub
If Target.Row > 1 Then
If Target.Validation.Type <> 3 Then Exit Sub
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
Set rng = ThisWorkbook.Names(str).RefersToRange
If rng Is Nothing Then Exit Sub
Set ws = rng.Parent
If Application.WorksheetFunction _
.CountIf(rng, Target.Value) Then
Exit Sub
Else
myRsp = MsgBox("Add this item to the list?", _
vbQuestion + vbYesNo + vbDefaultButton1, _
"New Item -- not in drop down")
If myRsp = vbYes Then
lCol = rng.Column
i = ws.Cells(Rows.Count, lCol).End(xlUp).Row + 1
ws.Cells(i, lCol).Value = Target.Value
strList = ws.Cells(1, lCol).ListObject.Name
With ws.ListObjects(strList).Sort
.SortFields.Clear
.SortFields.Add _
Key:=Cells(2, lCol), _
SortOn:=xlSortOnValues, _
Order:=xlAscending
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With ws.ListObjects(strList)
.Resize .DataBodyRange.CurrentRegion
End With
End If
End If
End If
End Sub
'The following code will automatically enter the current date & change a positive value to a negative value.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A5:A25")) Is Nothing Then
With Target(1, 2)
.Value = Date
End With
End If
If Not Intersect(Target, Range("H5:H25")) Is Nothing Then
With Target(1, 2)
.Value = Date
End With
End If
If Intersect(Target, Range("E5:E25")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value > 0 Then Target = Target.Value * -1
End Sub
Bookmarks