Results 1 to 2 of 2

Multiple codes in one worksheet_change

Threaded View

Arnis Multiple codes in one... 02-23-2020, 08:15 AM
mehmetcik Re: Multiple codes in one... 02-23-2020, 08:39 AM
  1. #1
    Registered User
    Join Date
    02-18-2020
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    13

    Multiple codes in one worksheet_change

    Hi,

    I am a newbie to VBA and are creating a production sheet. I have two VBA codes which needs to be applied into difference columns. The codes fullfill separate purpose and is working perfectly fine in seperate excel sheets. One code is for automatically dates and the other is for message box pop up.

    I need the codes in one sheet. How can I make these codes work in one worksheet_change. Below is my two codes.

    Here is the first code and its working perfectly fine if its added alone:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
    Application.EnableEvents = False
    Cells(Target.Row, 4).Value = Date + Time
    Application.EnableEvents = True
    Else
    If Target.Column = 12 Then
    Application.EnableEvents = False
    Cells(Target.Row, 16).Value = Date + Time
    Application.EnableEvents = True
    Else
    If Target.Column = 15 Then
    Application.EnableEvents = False
    Cells(Target.Row, 16).Value = Date + Time
    Application.EnableEvents = True
    End If
    End If
    End If
    
    End sub

    MY SECOND CODE:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Const iDefaultDays As Integer = 84
    Dim dtExpected As Date
    
    On Error GoTo Terminate
    
    If Target.Cells.Count > 6 Or Target.Column <> 6 Then GoTo Terminate
    If Target.Value = "" Then
    Target.Offset(0, 2).ClearContents
    GoTo Terminate
    End If
    
    Application.EnableEvents = False
    
    If Not IsDate(Target.Value) Then
    MsgBox "Invalid value - please enter a date", vbExclamation + vbOKOnly
    Target.ClearContents
    Target.Select
    Else
    dtExpected = Target.Value + iDefaultDays
    If MsgBox("Expected delivery date: " & dtExpected & String(2, vbCr) & "Accept this date?", vbYesNo + vbQuestion) = vbNo Then
    dtExpected = InputBox("Manually enter expected delivery date", , dtExpected)
    End If
    Target.Offset(0, 2).Value = dtExpected
    End If
    
    Terminate:
    If Err Then
    Debug.Print "Error", Err.Number, Err.Description
    Err.Clear
    End If
    Application.EnableEvents = True
    End Sub

    Thanks alot!
    Last edited by Arnis; 02-23-2020 at 08:52 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do I put 2 codes in VBA Worksheet_change
    By Arnis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-19-2020, 08:58 AM
  2. How to Merge Two Worksheet_Change Codes
    By merveileuse in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2019, 04:56 AM
  3. Merging 2 Private Sub Worksheet_Change codes
    By anilpatni1234 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-01-2019, 10:08 PM
  4. How to compile two codes in Private Sub Worksheet_Change(ByVal Target As Range)
    By rajeev.raj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-19-2019, 01:51 PM
  5. Combine Two Private Sub Worksheet_Change codes in same worksheet
    By abhinavbinkar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-01-2019, 06:58 AM
  6. [SOLVED] Combine two Private Sub Worksheet_Change VBA codes
    By sloshpuppy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-13-2017, 04:55 AM
  7. Combine two Worksheet_Change Codes
    By otherbobby in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2014, 04:10 PM

Tags for this Thread

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