inserting sub rutines
 

I have 6 sub rutines in a worksheet.
The first 10 lines are the same in all the subs, where after I continue
with a uniq rutine for each of the subs.
Is there a way for me to avoid writing the first 10 lines in each sub,
and just call it from somewhere else and then returning to finish the
sub I started in the first place?

Also..
I have a situation where I have 6 shapes that I need to color. The
colour varies after a number of variables. All six shapes have the same
variables, but for different areas. So far I set up the folowing.
(Shortend text)

Private Sub Worksheet_Activate()
Dim vValStorkunder As Double
Dim vValSmb As Double

vValStorkunder =
Evaluate("Max(if(Medarbejderdata!C1:C2000=""Storku nder"",Medarbejderdata!G1:G2000,0))")
vValSmb =
Evaluate("Max(if(Medarbejderdata!C1:C2000=""SMB"", Medarbejderdata!G1:G2000,0))")

If vValStorkunder = 0 Then
ActiveSheet.Shapes("pzlStorkunder").Fill.ForeColor .SchemeColor = 9
' White
End If
If vValStorkunder = 1 Then
ActiveSheet.Shapes("pzlStorkunder").Fill.ForeColor .SchemeColor = 22
' Grey
End If

If vValSmb = 0 Then
ActiveSheet.Shapes("pzlSmb").Fill.ForeColor.Scheme Color = 9 '
White
End If
If vValSmb = 1 Then
ActiveSheet.Shapes("pzlSmb").Fill.ForeColor.Scheme Color = 22 '
Grey
End If
End Sub

Just imagine the above text with 6 different vVals and 5 different
result for colours.

Can I do this an a loop somehow, so that I dont have to define the same
5 Fill.Foreclor.SchemeColor for each vVal I make?

I thought of a
For Each Shape in ActiveSheets.Shapes
or something. I just dont know how to set it up...

thx
Rune Daub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

inserting sub rutines
 

The first 10 lines are the same in all the subs

Put the common code in its own sub and make a call to it in each sub that
needs it:

Sub NeedsToCallCommonCode()
CommonCode
''unique code starts here
End Sub

Sub CommonCode()
''Common code goes here
End Sub

--
Jim Rech
Excel MVP