Results 1 to 6 of 6

Combine two Private Sub Worksheet_Change(ByVal Target As Range) on same sheet

Threaded View

  1. #1
    Registered User
    Join Date
    04-15-2014
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    3

    Combine two Private Sub Worksheet_Change(ByVal Target As Range) on same sheet

    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
    Last edited by 6StringJazzer; 04-23-2016 at 12:53 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Combine Private Sub Worksheet_Change(ByVal Target As Range) VBA Code on same worksheet
    By mark_luke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2022, 04:48 PM
  2. [SOLVED] Need help to combine 2 Private Sub Worksheet_Change(ByVal Target As Range)
    By sylvainsyl20 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2016, 11:45 AM
  3. [SOLVED] Private Sub Worksheet_Change(ByVal Target As Range)
    By hmr2662 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2015, 12:35 PM
  4. [SOLVED] Private Sub Worksheet_Change(ByVal Target As Range) End Sub
    By kanonathena in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-10-2013, 12:25 AM
  5. Private Sub Worksheet_Change(ByVal Target As Range) Help
    By adamsj1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-28-2012, 09:17 AM
  6. [SOLVED] Private Sub Worksheet_Change(ByVal Target As Range)
    By Hilton1982 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-18-2012, 01:13 AM
  7. Private Sub Worksheet_Change(ByVal Target As Range)
    By Arturo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1