|
Running A Macro From Within Another Macro
|
|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Running A Macro From Within Another Macro - Excel
|
View Answers
|
|
|
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
Edit Recorded Macro
- See how to record a MACRO to copy data to a new location, and then edit the code. See the VBA functions RANGE and OFFSET. Edit Recorded Mac ...
Recorded Macro Basics
- Learn about: 1.How to Record a Macro 2.Macro = VBA code 3.Macros are great for repetitive tasks 4.What file extension to use for E ...
Macro & Form Button
- See how to create a basic Macro and then assign the Macro to a Form button. See how to fix a formula with an error with the IFERROR, IF, an ...
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
|
|
