Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Running A Macro From Within Another Macro

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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


Similar Excel Video Tutorials

Helpful Excel Macros

Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Filter Data in Excel Without the Filter "Arrow" Appearing in the Filtered Column - AutoFilter
- This Excel macro filters data in Excel without the filter "arrow" appearing in the column that is being filtered. This

Similar Topics







I'm looking for help in developing a formula in column 'A' that will number the task automatically according to the outline below. The diferent levels of tasks are in diferent columns, so I was hoping for a formula solution rather than having to maintain this manually. Thank you for any help you can offer on this.

A B C D E 1 5.1 Level 1 lask 2 5.1.1 Level 2 task 3 5.1.1.1 Level 3 task 4 5.1.1.2 Level 3 task 5 5.1.1.2.1 Level 4 task 6 5.1.1.2.2 Level 4 task 7 5.1.1.2.3 Level 4 task 8 5.1.2 Level 2 task 9 5.1.2.1 Level 3 task 10 5.1.2.2 Level 3 task 11 5.1.3 Level 2 task 12 5.1.3.1 Level 3 task 13 5.1.3.2 Level 3 task 14 5.1.4 Level 2 task 15 5.1.4.1 Level 3 task 16 5.1.4.2 Level 3 task 17 5.1.4.3 Level 3 task 18 5.1.4.4 Level 3 task 19 5.1.4.5 Level 3 task 20 5.1.4.6 Level 3 task 21 5.1.4.7 Level 3 task 22 5.2 Level 1 lask 23 5.2.1 Level 2 task 24 5.2.1.1 Level 3 task 25 5.2.1.1.1 Level 4 task 26 5.2.1.1.2 Level 4 task 27 5.2.1.1.3 Level 4 task 28 5.2.1.1.4 Level 4 task 29 5.2.1.2 Level 3 task


I have a large spreadsheet that is organized as an outline with the outline level listed in the first column. I would like to implement the Excel outline feature using the outline levels listed in the first column.

To clarify, the first column looks like this:

Level
1
2
2
3
3
3
2
3
4
4
1
...

Thanks in advance!

Mike


I have save an MS Project plan as an Excel workbook. The problem is that the outlining of the project plan didn't come across - well, the task names aren't indented, but the outline level did come across. So, I see 1, 2, 3, 4 in outline level, so I assume I can do some kind of formula in Excel that says if outline level=2 then indent once, if outline level=3 then indent twice, etc. Any ideas?


I need help outlining my Excel spreadsheet using the auto-outline function. The first column is the level of indentation the row should have and the second column is the text associated with that row. For example:

Level Text 1 The 2 quick 2 brown 3 fox 2 jumps 1 over 1 the 2 lazy 2 dog
Should look like this:

The
_quick
_brown
__fox
_jumps
over
the
_lazy
_dog

with _ being a single indentation

How do I get this to work with automatic outline?

Thanks


OK, so I have a worksheet with a header row and several hundred rows of data below. The data has been grouped using Excel's Outline & Grouping feature.

I have also turned on the AutoFilter. One of my columns contains either a Y or N for each row and is usually filtered to N using the AutoFilter.

Here's my problem. If I apply the AutoFilter and then click an Outline level, I see all rows for that level, not just the N rows. Similarly, if I click an Outline level and then apply the AutoFilter, I see all the N rows and not just the ones for the level.

What I want to do is apply both the AutoFilter and Outline level (in either order) and then see only the filtered rows for the chosen level. So how can I get the AutoFilter and Outline levels to play nicely together?

I can't easily use code to sort this out as it has to work for people who have macros switched off.

Currently the workbook is in Excel 2003, but if there's a solution in 2007 I can upgrade the workbook. Currently I experience the same problem in both versions.


Is there a way to import the outline levels into Excel?

My challenge is that I am attempting to import MS Project information into Excel. I export the Outline Level (1, 2, 3, etc) with my other fields from Project. I know how to use the Outline Level to add spaces and create the right visual tiering effect in Excel (=REPT(" ",(H2-1))&B2) , but want to use this information to create an Outline List (Level 1, Level 2, Level 3, etc) in Excel.

Thanks.


Hi,

Using code such as

ActiveSheet.Outline.ShowLevels RowLevels:=2

I can collapse or expand all the outlined data on a sheet to reveal a given level of detail.

However, this affects all data outlined to that level on that sheet.

The effect I am looking for is what is achieved by clicking on one of the + (or -) outline symbols for that level. (I do not wish to show the outline symbols on the spreadsheet due to the space this uses.)

Any ideas on how to code this?

Thanks in advance.

Pete.


I created a multilevel list in a document a long time ago in Word 2003, but at that time I didn't know about the Outlining Feature (i.e. assigning true outline "Levels" to the list so that you can see them in the Outline view as "Level 1, Level 2, Level 3, etc.). Now when I open up this document in Word 2007 the outline still looks great in normal view, however when I view the document in "Outlining" view, the levels of my list don't show up in the "Outlining" view (i.e. everything is categorized as "Text Body").

How can I quickly and easily change this 150 page very detailed mutlilevel outline so that the levels of the outline show up in the "Outline" mode without having click on each individual line and manually change it over. That would take me 2 fully days of work to convert it that way one by one. There's got to be a quicker way.


I have a spreadsheet that contains data that has been outlined using Excel's "Group and Outline" functionality. I would like to indicate on the printed document whether the information is "Detail" or "Summary."

Is there any way to determine the outline level showing?

Thanks,

Mike


I have a spreadsheet with 4 outline levels. I would like all the rows in level 3 to be formatted with a border on top. The spreadsheet has 3000 rows so selecting them individually is not feasible. If i collapse to level 3, and select all, it formats all the rows in the spreadsheet. Is there a way to get excel to format the rows in a particular outline level?
Thanks!


I need to delete a row in a outline sheet. I know I can remove the outline if I go to Data/Group and Outline/Clear Outline. What I need to do is remove #2 outline rows but not the data in each sub category. The row has only one cell that has any info in it. I can not hold the Ctrl to highlight each one and then delete all. I plan on removing the outline once the rows are deleted. Any suggestions?


After doing some searches (and coming up empty) I figured I'd check and see if anyone has suggestions on some code to dynamically create an outline in one column that reflects the indentation level of the second column.

******** language="JavaScript" ************************************************************************> Microsoft Excel - Excel Outline - Sandbox.xls ___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout E1 =
E F G H 1 Outline Indented Level 2 1 b 3 1.1 a 4 1.2 2 5 1.3 d 6 1.3.1 d 7 1.3.1.1 g 8 1.3.1 a 9 1.3.2 a Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Basically, as I enter values / change the indent of column I would like the outline to adjust dynamically.

Here's the code that I have written so far. Issues with it:
a) Moving the indent 'back'/'up' one level is not accounted for
b) If an new row is inserted in the middle of the outline the cells below are not updated.
c) It's probably better to just start over from scratch... (I don't think my 'ReturnTail' function works properly either...)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
     
If Not Intersect(Range("F:F"), Target) Is Nothing Then
              'Get Previous Value:
            IndentLevel = Target.IndentLevel
            PrevOutline = Target.Offset(-1, -1).Value
            PrevIndentLevel = Target.Offset(-1, 0).IndentLevel
            PeriodCount = countsubstr(PrevOutline, ".")
            
            If PrevOutline = "" Then
                hierarchy = "1"
                'Temporary Bug Fix:
                PrevOutline = "0"
            End If
            
            'New level indented.
            If IndentLevel - 1 = PrevIndentLevel Then
                hierarchy = PrevOutline & ".1"
            End If
            
            'Step Back Level:
            If IndentLevel < PrevIndentLevel Then
                StepBack = PrevIndentLevel - IndentLevel
                
                ct = 0
                Do While ct < StepBack
                    PrevOutline = ReturnStart(PrevOutline, ".")
                    ct = ct + 1
                Loop
                MsgBox ("PrevOutline = " & PrevOutline)
                LastTail = ReturnTail(PrevOutline, ".")
                MsgBox LastTail
                hierarchy = PrevOutline & "." & LastIncr
            
            End If
            
            
            If IndentLevel = PrevIndentLevel Then
                LastTail = ReturnTail(PrevOutline, ".")
                LastIncr = LastTail + 1
                Initial = ReturnStart(PrevOutline, ".")
                hierarchy = Initial & "." & LastIncr
            End If
            
            'If IsNumeric(PrevOutline) Then
            '    hierarchy = PrevOutline + 1
            'End If
            
            
           
            'Else: Hierachy = ReturnTail(PrevOutline, ".")
                'Target.Offset(0, -1).Value = Target.IndentLevel
            'MsgBox (ReturnTail(Target.Offset(0, -1), "."))
            'MsgBox hierarchy
            
            Target.Offset(0, -1).Value = hierarchy
    
    End If
        
   
End Sub

Function ReturnTail(haystack, needle)
    sTemp = StrReverse(haystack)
    nPos = InStr(sTemp, needle)
    nPos = Len(sTemp) - nPos
    ReturnTail = Right(haystack, nPos)

End Function
Function ReturnStart(haystack, needle)
    sTemp = StrReverse(haystack)
    nPos = InStr(sTemp, needle)
    nPos = Len(sTemp) - nPos
    ReturnStart = Left(haystack, nPos)

End Function

Function countsubstr(ByVal haystack As String, ByVal needle As String)
    Dim pos
    pos = InStr(haystack, needle)
    If pos > 0 Then
        countsubstr = countsubstr(Mid(haystack, pos + 1), needle) + 1
    Else
        countsubstr = 0
    End If

End Function


It would also probably be easier (Preferable?) to write an new Excel function called, "Outline" that would take the arguments, "Previous Outline" and "Indented Values".

E.g.
Cell E3: "=Outline(PrevOutline,IndentedCell)"
Cell E3: "=Outline(E2,F3)"


Thanks for the help!
Dave
(This falls into the "I'd-rather-utilize-VBA-code-in-Excel-than-fight-with-Project"-bucket)


I have created a quick ouline for a chart of accounts. I would like to have a formula that verifies that all the 'children' sum up to the parent at each level of the outline.

I have a column with the integer value of the outline level, 0 being the "root", and each sub-level going up. i.e. 1's would be children of 0s, 3's children of 2's, etc.

Example:

0 Entity Value
1 Child1 Value
1 Child2 Value
2 Child1a Value
2 Child2b Value
1 Child3 Value
0 Entity2 Value
1 Etc...

How would you write a formula that verifies that a row's value is the sum of the children that are associated with it?


Does anyone have an Excel VBA function to sort an Outline/WBS?

I have a column in an excel workbook with a Outline/WBS.
If a user uses the default Excel sort I need to sort the Outline/WBS back to the correct Outline structure (example below):


1
1.1
1.2
1.3
1.3.1
1.3.2
2
2.1
2.2
2.2.1
2.2.2
2.3


I have the following procedure on a worksheet. It's intention is to sort the
rows on the sheet by a value in the score column. The rows are set up as
outline rows, so you have a high level row and several rows underneath. The
outline column is just a means to keep the low level lines together with the
high level they belong to.

When I run the procedure, instead of ordering the rows by score, it orders
them by outline. I can manually sort them back to score, but when run
programatically it completely ignores sorting by score. The scores are all
different numbers and you can see the values in no particular order after the
sort. I ensures the area is correct and it seems to run the sort, but not
correctly. Any idea why this may be? Thanks!

Private Sub cmdScoreSort_Click()
'sort Sheet
Dim sAddress As String

DisableScreenUpdates

sAddress = Selection.Address

'get rid of outline
Me.Cells.ClearOutline

'Sort first by score, then by outline
Me.Range(Me.Cells(TOP_ROW - 1, 1), Me.Cells(Me.Cells(Rows.Count,
RPT_SCORE_COL).End(xlUp).Row, RPT_OUTLINE_COL)).Sort _
key1:=Me.Cells(TOP_ROW - 1, RPT_SCORE_COL), order1:=xlDescending, _
key2:=Me.Cells(TOP_ROW - 1, RPT_OUTLINE_COL), order2:=xlAscending,
header:=xlYes

'Re-add the outline to each line
For inx = TOP_ROW To Me.Cells(Rows.Count, RPT_SCORE_COL).End(xlUp).Row
If Me.Cells(inx, RPT_PC_COL) = "" Then
Me.Rows(inx).OutlineLevel = 2
End If

Next

Me.Outline.ShowLevels 1

Me.Range(sAddress).Select

EnableScreenUpdates

End Sub
--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003




I've searched around and played with this some. What I'd like is to create a Worksheet macro for when the user picks a Outline Row level (on my subtotaled report), unused columns would be hidden.

Row level 5 = no hidden columns
Row level 4 = hide column D
Row level 3 = hide columns C & D
Row level 2 = hide columns B:D
Row level 1 = hide columns B:D

I can't figure out, first, how to use the current row level as a variable and secondly, what event I would use to launch the macro.


I have worksheet with 3 levels of subtotals (5 outline levels). Is there a way to edit the criteria contained in a particular level of the existing subtotal/outline without having to "Remove All" subtotals and starting over?


I have what should be a simple problem, but recording a macro doesn't seem to show anything. How do I expand or contract an outline level that is already created?

IE - what is the equivalent of expanding the + or - button on row 9?

I've made outlines dynamically, but can't seem to find the code for just expanding the view on one particular row, as opposed to all at one level.

Someone wants me to hide certain groups based on some criteria, but I still have to find the hide unhide code.


Guys,

I have an excel sheet that is pulling data from other sheet based on some cretaria...i want a macro that when ran on this excel fixes column width , makes cell border as "dotted" and outline the area with Thick Outline.

I am attaching a sample sheet with raw worskeet (unformatted) and one formatted which i did manually.

Columns in worksheet will remain same but rows can change....can any1 provide me with a macro for this plz...

this is what i need ,

Column A width to be 31.00
Column B,C,D to be 11.29
Column E to be 7.00

Cell Border to be dotted,and thick box border around rows in Column B,C,D,E where columns rows has data.


Hi all,

I'm using Excel 2003 to manage a list of requirements that started out life in MS Word. The requirements are numbered hierarchically by section using the 'outline numbered' feature in Word.

1
1.1
1.2
1.2.1
1.2.2
etc.

I have the requirements added to excel, 1 requirement per row and would like to have excel autonumber a 'requirement number' column.

So far, I have used 'group & outline' in excel to mimic the word outlining hierarchy (e.g. 1 is not grouped, 1.1 is grouped once, 1.1.1 is grouped twice) and wondered if it is possible to put a number in a cell based on this? Failing that, is there any other way that I can implement 'outline numbered' functionality in Excel?

Cheers,

Steve.


(How) Can you auto outline pivot tables? I'm using Excel 2003. I want to send the information to non-excel experts and need them to be able easily to view it at different levels. I know that to auto outline you really need a formula at the bottom of some rows of data. Pivot tables don't have formulas, but excel must know where the totals rows are as it creates them. Using the standard Data/Group and outline/Auto outline with either the whole table or selected rows gives me a "Cannot create an outline" message. Is there an easy way to do it?

Thanks


Hi everyone,

I have tried to record and save macros in relation to this, and it appears that I terminally suck at macros

I am attempting to create a macro that will print only the visible cells when the outline is either expanded or contracted. There are 6 groups on the left and one across the top.

The ranges somehow always change when the outline is opened or closed. Any help would be GREATLY appreciated...3 days on this and nothing.


Hi,

I am new to outlining data. I created an outline and I want to hide the
formulas through worksheet protection. However, I get an error message when
I try to use the outline. Is there something that I am not doing right?

Thanks



Hi. I am learning to group and outline data and wonder if there is a
tutorial or anything out there? Right now I use the auto outline but I don't
understand how it organizes things. I seem to get the same outline no matter
if the data headers are in the same column or seperated.


Thanks,


Todd



I have a document with group and outline applied in Excel 2007. The plus symbols show along the lefthand side. However, the 1-2-3 do not show on the left so you can see all Level 2s, for instance. I checked the Advanced Option and verified that the check box is checked to Show outline symbols if an outline is applied.

Any other ideas as to how I can get that 1-2-3 to show?

Thanks for your help!

Marie