Compile Error: Procedure too Large Question

  • Thread starter Thread starter Guest
  • Start date Start date
I cannot make it smaller. Here is the code:
If Target.Address = "$G$9748" Then
If Me.Range("G9748").Value = "Yes" Then
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = Range("F9745:I9753").Height
Selection.Width = Range("F9745:I9753").Width
Selection.Top = Range("F9745:I9753").Top
Selection.Left = Range("F9745:I9753").Left
Selection.Placement = xlMoveAndSize
End If
End If
This is repeated 176 times with different values. Is there a way to make it
smaller?
 
Hi,
Consider holding the target address and corresponding ranges in a
table and using VLOOKUP or INDEX/MATCH. The code would only be a minor
modification to your code below.

VLOOKUP table would be of format

Target Address Data Range
$G$9748 F9745:I9753


HTH
 
Looks like you could only have such code appear one time and program a loop
that goes through the various cells and performs the relative action.

So you should be able to make it smaller. If not, then break it into two
macros. Half the code in one module (88 cells worth) and half the code in
another module (88 cells worth). then you can use a tiny macro to call both

Sub Master()
Macro1
Macro2
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.count > 1 then exit sub
If Target.Column = 7 then
if Target.Value = "Yes" then
set rng = Target.Offset(-3,-1).Resize(8,4)
Application.Dialogs(xlDialogInsertPicture).Show
Selection.Height = rng.Height
Selection.Width = rng.Width
Selection.Top = rng.Top
Selection.Left = rng.Left
Selection.Placement = xlMoveAndSize
End if
End if
End Sub
 
Back
Top