combine two VBA codes

mkh61083 · Jun 26th 2016

I need a help to combine these two VBA codes:

Code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)Dim cell   As Range
  On Error GoTo enditall
 Application.EnableEvents = False
For Each cell In Target
 cell = UCase(cell)
 If Target.Cells.Column > 0 Then
 n = Target.Row
 If Excel.Range("C" & n).Value <> "" Then
 Excel.Range("A" & n).Value = Now
 End If
 End If
 Next
enditall:
 Application.EnableEvents = True
 End Sub

Display More

and

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Log" Then Exit Sub
Application.EnableEvents = False
UserName = Environ("USERNAME")
NewVal = Target.Value
Application.Undo
oldVal = Target.Value
lr = Sheets("Log").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Log").Range("A" & lr) = Now
Sheets("Log").Range("B" & lr) = ActiveSheet.Name
Sheets("Log").Range("C" & lr) = Target.Address
Sheets("Log").Range("D" & lr) = oldVal
Sheets("Log").Range("E" & lr) = NewVal
Sheets("Log").Range("F" & lr) = UserName
Target = NewVal
Application.EnableEvents = True
End Sub

Display More

Thanks in Advance

**cytop** · Jun 26th 2016

Re: How to combine these two VBA codes please?

Seems straightforward enough. What problems/errors do you get if you just combine them?

mkh61083 · Jun 26th 2016

Re: How to combine these two VBA codes please?

Quote from cytop;773070

Seems straightforward enough. What problems/errors do you get if you just combine them?

In the beginning I'd like to thank you for your Advice in this thread http://www.ozgrid.com/forum/sh…00145&p=772518#post772518
my file show a Run-time error 1004, I think that error refer to the conflict of "Worksheet_Change", so I tried to combine them but my tries were useless.
this is my last step in the file, and I gave up.

**cytop** · Jun 26th 2016

Re: How to combine these two VBA codes please?

Post the code from when you tried to combine them.

mkh61083 · Jun 26th 2016

Re: How to combine these two VBA codes please?

Code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)Dim cell   As Range
    On Error GoTo enditall
    Application.EnableEvents = False
    For Each cell In Target
        cell = UCase(cell)
        If Target.Cells.Column > 0 Then
            n = Target.Row
            If Excel.Range("C" & n).Value <> "" Then
                Excel.Range("A" & n).Value = Now
                 If ActiveSheet.Name = "Log" Then Exit Sub
                    Application.EnableEvents = False
                    UserName = Environ("USERNAME")
                    NewVal = Target.Value
                    Application.Undo
                    oldVal = Target.Value
                    lr = Sheets("Log").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("Log").Range("A" & lr) = Now
                    Sheets("Log").Range("B" & lr) = ActiveSheet.Name
                    Sheets("Log").Range("C" & lr) = Target.Address
                    Sheets("Log").Range("D" & lr) = oldVal
                    Sheets("Log").Range("E" & lr) = NewVal
                    Sheets("Log").Range("F" & lr) = UserName
                    Target = NewVal
                    Application.EnableEvents = True
                 End If
              End If
         End If
    Next
enditall:
    Application.EnableEvents = True
End Sub

Display More

**cytop** · Jun 26th 2016

Re: How to combine these two VBA codes please?

General answer only as you never mentioned where the error occurs, but perhaps the Excel object should be the Application object. I've never seen 'Excel.' used like that in VBA, only in Visual Studio code referring to
Microsoft.Office.Interop.Excel

There are other issues like exiting with EnableEvents still set False and one or 2 other things.

mkh61083 · Jun 26th 2016

Re: How to combine these two VBA codes please?

This is the file
Try to change any cell in sheet (1)
any Password (mkh)

**cytop** · Jun 26th 2016

Re: How to combine these two VBA codes please?

I won't download so I'll leave that for someone else.

The issue is simple and probably could have been fixed if you had mentioned where it occurs, as I mentioned.

combine two VBA codes

Participate now!

Share