Running a macro from within another macro

[ShogunPatch]

Hi
I have setup an excel worksheet as a task list with outline numbered (1; 1.1; 1.1.1; etc) tasks and subtasks laid out as follows:

• In column A I manually set the outline numbering level (e.g: L3).
• In column B I have the outline number (e.g: 1.2.1)
• In column C I have the task name, which is indented according to the outline level (e.g: indented out two levels in the example above)

I have a nice, simple (what I think is an) event macro called "Private Sub Worksheet_Change(ByVal Target As Range)" which automatically indents the task name in Column C appropriately depending on the corresponding number in Column A.

I also have another, rather more complex macro called Sub WBSNumbering() which automatically enters the correct outline number into column B, based on the level of indentation in Column C.

I can take no credit for either macro as I have got both of them working using online help, but they both work a treat.

At the moment, after manually entering the outline level into column A, I have to manually run the second macro to generate the outline number, but I would like to set it up so that when I enter or change the outline level in column A it indents Column C appropriately and then automatically runs the macro to generate the outline number in Column B straight away as I press enter.

I have tried simply typing the name of the second macro just before the End Sub statement of the event macro but I get the following error message:

Run-time Error '28':
Out of stack space

Any help or advice on how to achieve this would be much appreciated. I hope I have clearly explained my query and included all required info, but I am not a VBA expert (at best I cobble together other people's bits of advice/code) so if you need any further info please let me know.

Patch

 

[JamesW]

There could be a few reasons for the Out of Stack Space error:

http://support.microsoft.com/kb/142138

Can you paste both of your codes using [ CODE ] [ / CODE ] tags?

 

[ShogunPatch]

James, thanks for the quick response.

Macro to indent task headings in Column C based on a value in Column A:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("A:A"), Target) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value >= 0 Then
Target.Offset(0, 2).IndentLevel = (Target.Value - 1)
End If
End If
End If
End Sub

Macro to create outline numbers in Column B based on the indent level in Column C:

Sub WBSNumbering()

'From [URL]http://j.modjeska.us/?p=31[/URL]
'Renumber tasks on a project plan
'Associate this code with a button or other control on your spreadsheet

'Layout Assumptions:
'Row 1 contains column headings
'Column B contains WBS numbers
'Column C contains Task description, with appropriate indentation
'Some text (here we assume "END OF PROJECT") delimits the end of the task list

    On Error Resume Next

    'Hide page breaks and disable screen updating (speeds up processing)
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    'Format WBS column as text (so zeros are not truncated)
    ActiveSheet.Range("B:B").NumberFormat = "@"
    Dim r As Long                   'Row counter
    Dim depth As Long               'How many "decimal" places for each task
    Dim wbsarray() As Long          'Master array holds counters for each WBS level
    Dim basenum As Long             'Whole number sequencing variable
    Dim wbs As String               'The WBS string for each task
    Dim aloop As Long               'General purpose For/Next loop counter

    r = 3                           'Starting row
    basenum = 0                     'Initialize whole numbers
    ReDim wbsarray(0 To 0) As Long  'Initialize WBS ennumeration array

    'Loop through cells with project tasks and generate WBS
    Do While Cells(r, 3) <> "END OF PROJECT"

        'Ignore empty tasks in column C
        If Cells(r, 3) <> "" Then

           'Skip hidden rows
            If Rows(r).EntireRow.Hidden = False Then

                'Get indentation level of task in col C
                depth = Cells(r, 3).IndentLevel

                'Case if no depth (whole number master task)
                If depth = 0 Then

                    'increment WBS base number
                    basenum = basenum + 1
                    wbs = CStr(basenum)
                    ReDim wbsarray(0 To 0)

                'Case if task has WBS depth (is a subtask, sub-subtask, etc.)
                Else

                    'Resize the WBS array according to current depth
                    ReDim Preserve wbsarray(0 To depth) As Long

                    'Repurpose depth to refer to array size; arrays start at 0
                    depth = depth - 1

                    'Case if this is the first subtask
                    If wbsarray(depth) <> 0 Then

                        wbsarray(depth) = wbsarray(depth) + 1

                    'Case if we are incrementing a subtask
                    Else

                        wbsarray(depth) = 1

                    End If

                    'Only ennumerate WBS as deep as the indentation calls for;
                    'so we clear previous stored values for deeper levels
                    If wbsarray(depth + 1) <> 0 Then
                        For aloop = depth + 1 To UBound(wbsarray)
                            wbsarray(aloop) = 0
                        Next aloop
                    End If

                    'Assign contents of array to WBS string
                    wbs = CStr(basenum)

                    For aloop = 0 To depth
                        wbs = wbs & "." & CStr(wbsarray(aloop))
                    Next aloop

                End If

                'Populate target cell with WBS number
                Cells(r, 2).Value = wbs

                'Get rid of annoying "number stored as text" error
                Cells(r, 2).Errors(xlNumberAsText).Ignore = True

                'Apply text format: next row is deeper than current
                If Cells(r + 1, 3).IndentLevel > Cells(r, 3).IndentLevel Then

                    Cells(r, 2).Font.Bold = True
                    Cells(r, 3).Font.Bold = True
                'Else (next row is same/shallower than current) no format
                Else
                    Cells(r, 2).Font.Bold = False
                    Cells(r, 3).Font.Bold = False
                End If
                'Special formatting for master (whole number) tasks)
                If Cells(r, 3).IndentLevel = 0 Then
                    Cells(r, 2).Font.Bold = True
                    Cells(r, 3).Font.Bold = True
                    'Add whatever other formatting you want here

                End If

            End If

        End If

    'Go to the next row
    r = r + 1

    Loop

End Sub

I added both of these into my worksheet by right-clicking on the sheet name, selecting 'View Code' and pasting.

Many thanks.