Change Event Code too long - Need to combine 2 events into 1 macro

**Sc0tt1e** · 10-15-2014, 01:34 AM

Private Sub Worksheet_Change(ByVal target As Range)
Dim rng1 As Range
Set rng1 = Intersect(Range("BR:BR, BV:BV, BZ:BZ, CD:CD, CH:CH, CL:CL, CP:CP, CT:CT, CX:CX, DA:DA, DF:DF, DJ:DJ, DN:DN, DR:DR, DV:DV, DZ:DZ, ED:ED, EH:EH, EL:EL, EP:EP, ET:ET, EX:EX, FB:FB, FF:FF, FJ:FJ, FN:FN, FR:FR, FV:FV, FZ:FZ, GD:GD, GH:GH, GL:GL, GP:GP, GT:GT, GX:GX, HB:HB, HF:HF"), target)
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
rng1.Offset(0, 2).Value = Environ("username")
rng1.Offset(0, 3).Value = Now()
Application.EnableEvents = True
End Sub

The above values in rng1 is the max amount of cell values I can have in the macro before I get a run time 1004 error. I still have the below values to add, is there anyway of creating a rng2 with these values to run within the change sub as I am unable to combine these values within a working macro?

HF:HF, HJ:HJ, HN:HN, HR:HR, HV:HV, HZ:HZ, ID:ID

**karedog** · 10-15-2014, 03:22 AM

Hi,

You can use matrix and loop like this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng1 As Range, mtx(1 To 2) As String
  mtx(1) = "BR:BR, BV:BV, BZ:BZ, CD:CD, CH:CH, CL:CL, CP:CP, CT:CT, CX:CX, DA:DA, DF:DF, DJ:DJ, DN:DN, DR:DR, DV:DV, DZ:DZ,ED:ED, EH:EH, EL:EL, EP:EP, ET:ET, EX:EX"
  mtx(2) = "FB:FB, FF:FF, FJ:FJ, FN:FN, FR:FR, FV:FV, FZ:FZ, GD:GD, GH:GH, GL:GL, GP:GP, GT:GT, GX:GX, HB:HB, HF:HF"

  Application.EnableEvents = False
  For i = LBound(mtx) To UBound(mtx)
      Set rng1 = Intersect(Range(mtx(i)), Target)
      If Not rng1 Is Nothing Then
         rng1.Offset(0, 2).Value = Environ("username")
         rng1.Offset(0, 3).Value = Now()
      End If
  Next i
  Application.EnableEvents = True
End Sub

**Sc0tt1e** · 10-15-2014, 03:36 AM

Matrix is new to me but it does the job, thank you karedog

**karedog** · 10-15-2014, 03:57 AM

You're welcome Sc0tt1e, thanks for the reputation point.

Change Event Code too long - Need to combine 2 events into 1 macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Change Event Code too long - Need to combine 2 events into 1 macro

Re: Change Event Code too long - Need to combine 2 events into 1 macro

Re: Change Event Code too long - Need to combine 2 events into 1 macro

Re: Change Event Code too long - Need to combine 2 events into 1 macro

Thread Information

Users Browsing this Thread

Similar Threads

Best way to combine to worksheet change events

[SOLVED] Code working as change event, but not as a macro.

Combine 2 worksheet change events

Request macro code - when cell change event

[SOLVED] combobox change event is running when enable events is false

Bookmarks

Bookmarks

Posting Permissions