• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compile Error...Procedure too large

M

Mario Navarrete

New Member
Hello everyone,

I'm an absolute noob to programming and I'm having trouble with a large bit of code. I receive the compile error saying "procedure too large" and I don't know how to break my code down. I have 300 circles to run this code through and I can barely get a dozen or so done before hitting the 64kb limit. I have placed the code for the first 15 here in hopes that someone can help show me how to either shorten it dramatically or break it down into parts. Please be kind, i'm an absolute beginner at this so if this is an easy thing to do, I apologize in advance.

Thanks!

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTriggerCell As Range

 Set myTriggerCell = Range("A1:A300")

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A1").Value

  Case "Available": ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A2").Value

  Case "Available": ActiveSheet.Shapes("Oval 2").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 2").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 2").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 2").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A3").Value

  Case "Available": ActiveSheet.Shapes("Oval 3").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 3").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 3").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 3").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A4").Value

  Case "Available": ActiveSheet.Shapes("Oval 4").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 4").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 4").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 4").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A5").Value

  Case "Available": ActiveSheet.Shapes("Oval 5").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 5").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 5").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 5").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A6").Value

  Case "Available": ActiveSheet.Shapes("Oval 6").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 6").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 6").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 6").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A7").Value

  Case "Available": ActiveSheet.Shapes("Oval 7").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 7").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 7").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 7").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A8").Value

  Case "Available": ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A9").Value

  Case "Available": ActiveSheet.Shapes("Oval 9").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 9").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 9").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 9").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A10").Value

  Case "Available": ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A11").Value

  Case "Available": ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A12").Value

  Case "Available": ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A13").Value

  Case "Available": ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A14").Value

  Case "Available": ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then

  Select Case Range("A15").Value

  Case "Available": ActiveSheet.Shapes("Oval 15").Fill.ForeColor.RGB = RGB(102, 204, 0)

  Case "Reserved": ActiveSheet.Shapes("Oval 15").Fill.ForeColor.RGB = RGB(255, 255, 51)

  Case "Future Lot": ActiveSheet.Shapes("Oval 15").Fill.ForeColor.RGB = RGB(160, 160, 160)

  Case "Sold": ActiveSheet.Shapes("Oval 15").Fill.ForeColor.RGB = RGB(204, 0, 0)

  End Select

End If

End Sub
 
Hi Mario ,

Can you clarify the following ?

You are using the Worksheet_Change event procedure , to run through all 300 cells ; the Worksheet_Change event procedure is triggered by a change even in a single cell. In your case , will a change happen in only one cell at a time , or can all 300 cells be changed in one go ?

If the change will take place in only one cell , can we not process only that cell , instead of running through all 300 cells ?

Narayan
 
Mario

Firstly, Welcome to the Chandoo.org Forums

The code works fine for me So i suspect something else is wrong
The code needs to be in a Worksheet Module in VBA not in a Code Module

Can you post the whole file?
 
Hi All,

Hui: Attached is the whole file as you requested.

Narayan: Yes, only one cell will change at a time. For example, if circles K8 and K9 in my attached file need to be updated, then I would singularly go into cell A8 to modify the K8 circle and then go into A9 to modify the K9 circle. I just don't know how to write the code so that it only processes that one particular cell.
 

Attachments

Replace all your code with the following
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTriggerCell As Range
Dim MyShape As String
Set myTriggerCell = Range("A1:A300")

If Not Application.Intersect(myTriggerCell, Target) Is Nothing Then
  MyShape = "Oval " + Right(Target.Address, Len(Target.Address) - InStr(InStr(1, Target.Address, "$") + 1, Target.Address, "$"))
 
  Select Case Target.Value
  Case "Available": ActiveSheet.Shapes(MyShape).Fill.ForeColor.RGB = RGB(102, 204, 0)
  Case "Reserved": ActiveSheet.Shapes(MyShape).Fill.ForeColor.RGB = RGB(255, 255, 51)
  Case "Future Lot": ActiveSheet.Shapes(MyShape).Fill.ForeColor.RGB = RGB(160, 160, 160)
  Case "Sold": ActiveSheet.Shapes(MyShape).Fill.ForeColor.RGB = RGB(204, 0, 0)
  End Select
End If

End Sub

See attached file:
 

Attachments

Last edited:
Wow!! This is great! So far so good. I'm going to be working on getting all 300 circles up and I will let you know if I have any issues. Thank you very much Hui!
 
Having 300 circles on a worksheet is going to add to the size of the file
Why not just use Conditional Formatting on a cell ?
 
You must log in or register to reply here.
Back
Top