+ Reply to Thread
Results 1 to 4 of 4

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

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    640

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

    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

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

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

    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
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    640

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

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

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Best way to combine to worksheet change events
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2014, 11:35 AM
  2. [SOLVED] Code working as change event, but not as a macro.
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 20
    Last Post: 12-21-2013, 06:25 PM
  3. Combine 2 worksheet change events
    By akderitend in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-07-2012, 06:57 PM
  4. Request macro code - when cell change event
    By Rhey1971 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-01-2006, 06:55 AM
  5. [SOLVED] combobox change event is running when enable events is false
    By tysop in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2006, 10:20 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