EXCEL VBA: COMPILE ERROR: Procedure too large - repetitive Selection change

StingerJ · May 5, 2018

I'm using VBA for EXCEL.
I'm also new to using it and this board.

All I'm trying to accomplish is that when a user inputs his/her initials in cell D5 the current time plus 1 hour will be inputted in C5.

I do this repetitively until cells o62 and P62.

I have repeated my code below to do this but I get the compile error.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sundays Time
If Target.Address = Me.Range("d5").Address Then
Me.Range("c5") = Format(Now + 1 / 24)
End If

If Target.Address = Me.Range("d6").Address Then
Me.Range("c6") = Format(Now + 1 / 24)
End If

If Target.Address = Me.Range("d7").Address Then
Me.Range("c7") = Format(Now + 1 / 24)
End If
If Target.Address = Me.Range("d8").Address Then
Me.Range("c8") = Format(Now + 1 / 24)
End If

etc.. etc.. SO how can I combat this.

Thank you all in advance
StingerJ

JonXL · May 5, 2018

Instead of all the Ifs, use the Target to reference the row:

Code:

Cells(Target.Row, "C").Value =
 Format(Now + 1 / 24)

StingerJ · May 5, 2018

Not really understanding, Sorry again new to this. is this what you mean?

If Target.Address = Me.Range("d5").Address Then
Cells(Target.Row, "C").Value = Format(Now + 1 / 24)
End If

Peter_SSs · May 6, 2018

Welcome to the MrExcel board!

You haven't made it clear what happens between columns C:D and O:P that you mentioned.
I have assumed that if an entry is made in in rows 2 to 62 in columns D, F, H, J, L, N or P then the timestamp should be applied in the cell immediately to the left.
If that is what you want, try replacing your whole code with the code below in a copy of your workbook & test.
If I have guessed incorrectly, then please describe more fully what you have and what you want.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Rows("2:62"), Range("D:D,F:F,H:H,J:J,L:L,N:N,P:P"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      c.Offset(, -1).Value = Format(Now + 1 / 24)
    Next c
    Application.EnableEvents = True
  End If
End Sub

StingerJ · May 6, 2018

yes each ROW consists of the following:
a1 - Task name b1- task normal execution time c1- actual execution time (generated when somebody inputs their initials in cell d1.)
Cells c1/d1 for Sunday, cells e1/f1 for Monday, G1/h1 for Tuesday etc...
each line goes Sunday thru Saturday.
and there are 61 rows (1 for each task).

I have been trying this out for over a month and somebody mentioned this forum to me. so I thought what the heck My brain is fried so here I am. I want to thank each and every one of you all for your time, Patience and knowledge.

thanks
StingerJ

StingerJ · May 6, 2018

Not working.... nothing happens
copied exactly like you wrote

TotallyConfused · May 6, 2018

StingerJ said:
Not working.... nothing happens
copied exactly like you wrote

Hello StingerJ
I too want to WELCOME you to this wonderful site.

I tried using Peter’s code that he gave you and found that it works perfectly. I can see two possible causes why you were unable to get his code to work for you.
1. In your opening question, you mention that you want the time stamp to begin in ‘C5’ (row 5). When you answered his question, you mentioned ‘C1’ (row 1). Peter’s code will start in ‘C2’ (refer to his code of rows(“2:62”) You need to change the ‘2’ in his code of rows(“2:62”) to whatever row your data begins on. For example rows(“5:62”)
2. I suspect this may be the real cause of you not getting any result from his code. For Excel’s EVENTS feature to work, you have to place the VBA code in a special place and not simply within that sheet’s module where you probably have other code. To get to this ‘special module’, click on the DEVELOPER tab at the top of your screen. Then in the CONTROL section, click on ‘VIEW CODE’. Now paste his code into the window that pops up. Now go back to the spreadsheet, and run your test.

If I am right in my two assumptions, I think you will be amazed at what happens now.

Good luck.
TotallyConfused

StingerJ · May 6, 2018

You were correct!!! Thank you all sooooo Much. I need to refer my friends to this site if they already don't know about it.

Thanks Again
StingerJ

Peter_SSs · May 6, 2018

StingerJ said:
Thank you all sooooo Much. I need to refer my friends to this site if they already don't know about it.

Thanks Again
StingerJ

You are very welcome. Glad you got it working. Yes, happy for you to refer your friends to this forum - we are always looking for more people to help.

EXCEL VBA: COMPILE ERROR: Procedure too large - repetitive Selection change

StingerJ

New Member

Excel Facts

JonXL

Well-known Member

StingerJ

New Member

Peter_SSs

MrExcel MVP, Moderator

StingerJ

New Member

StingerJ

New Member

TotallyConfused

Board Regular

StingerJ

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

EXCEL VBA: COMPILE ERROR: Procedure too large - repetitive Selection change

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP, Moderator

New Member

New Member

Board Regular

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock