Auto Outline generation based on Indent Level

D

dave981

New Member
Joined
Jun 25, 2008
Messages
12
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.

<script language="JavaScript" src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></script><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Excel Outline - Sandbox.xls</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: 12.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb755237"><input onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' value="Copy Formula" name="btCb873980" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb078704"></form><td style="width: 60px;" align="center" bgcolor="white"><select onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name="sltNb935705"><option value="" selected="selected">E1</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="Outline" name="txbFb426622"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="center" width="2%">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>E</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>F</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>G</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="center"><center>H</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>1</center></td><td style="border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); border-width: 0.5pt; font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">Outline</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">Indented Level</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>2</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">b</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>3</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 1em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">a</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>4</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 1em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>5</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 1em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">d</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>6</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 2em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">d</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>7</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.1.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 3em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">g</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>8</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 2em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">a</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="center" width="2%"><center>9</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">1.3.2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); text-indent: 2em; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: left;">a</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; width: 120pt; background-color: rgb(255, 255, 255);" align="left">Sheet1</td><td> </td></tr></tbody></table></td></tr></tbody></table>
[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.</center>

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)
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Ok, I think I have most of the code 'working' (with a few bugs). I have it working as on 'on change' - but that causes problems when I copy / paste in values into the outline column. I think by creating a function (UDF) for Excel called "NumOutline" i can get around this error - and it will provide me with more flexibility for when i need to skip a few lines in my document:

When I call the code, however, the "countsubstr()" function breaks... "Compile Error: Sub or Function not defined".


Code: 
Function NumOutline(IndText, PrevOutline2, PrevIndentLevel)

            IndentLevel = IndText.IndentLevel
            PrevOutline = PrevOutline2.value
            PrevIndentLevel = PrevIndentLevel.IndentLevel
            PeriodCount = countsubstr(PrevOutline, ".")
            
            If IndText.value = "" Then End
            
            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
                If InStr(PrevOutline, ".") > 0 Then
                    LastTail = ReturnTail(PrevOutline, ".")
                End If
                hierarchy = ReturnStart(PrevOutline, ".") & "." & LastTail + 1
            
            End If
            
            If IndentLevel = PrevIndentLevel And IndentLevel >= 1 Then
                LastTail = ReturnTail(PrevOutline, ".")
                LastIncr = LastTail + 1
                Initial = ReturnStart(PrevOutline, ".")
                hierarchy = Initial & "." & LastIncr
            End If
            
            If IndentLevel = 0 Then
                Initial = ReturnStart(PrevOutline, ".")
                hierarchy = Initial + 1
            End If
                      
            Outline = hierarchy

End Function

Function ReturnTail(haystack, needle)
    sTemp = StrReverse(haystack)
    sTemp = haystack
    nPos = InStr(sTemp, needle)
    nPos = nPos - 1
    '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
 
Upvote 0
Does it need to be such a complex code ?
How about?
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, x
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
If Not Intersect(Target, Range("e:e")) Is Nothing Then
    For Each r In Intersect(Target, Range("e:e")).Cells
        If InStr(r.Value, ".") = 0 Then
            r.Offset(,1).Value = Trim(r.Offset(,1).Value)
        Else
            x = Split(r.Value, ".")
            r.Offset(,1).Value = Space(UBound(x) + 1) & Trim(r.Offset(,1).Value)
        End If
    Next
End If
If Not Intersect(Target, Range("f:f")) Is Nothing Then
    For Each r In Intersect(Target, Range("f:f")).Cells
        If InStr(r.Offset(,-1).Value, ".") = 0 Then
            r.Value = Trim(r.Value)
        Else
            x = Split(r.Offset(,-1).Value, ".")
            r.Value = Space(UBound(x) + 1) & Trim(r.Value)
        End If
    Next       
End If 
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub
 
Upvote 0
Thanks Jindon. That worked well for when I entered, "1.1" and adjusting the outline depth of the text column. But it didn't work for updating the outline column when i adjusted the text.

E.g. If I changed the indent level of "__Text" to "_Text", i'd like the numerical counter/outline to adjust accordingly:
1.1.1 __Text

To
1.1 _Text

Thanks for the help!
Dave
 
Upvote 0
Reading through your code inspired me to create the following, simpler, code:

Code: 
Function CustomOutline(PrevOutline, IndentLev)
    'Turn the previous Outline into an Array
    Dim xArr() As String
    xArr = Split(PrevOutline, ".")
    'Determine Outline Text Indent Level:
    yIndent = IndentLev.IndentLevel
    
    'Decrease Indent: 1.x.y to 1.x
    If UBound(xArr) >= yIndent Then
    ReDim Preserve xArr(0 To yIndent) As String
    xArr(yIndent) = xArr(yIndent) + 1
    End If
    
    'Increase Indent: 1.x to 1.x.y
    If UBound(xArr) < yIndent Then
    ReDim Preserve xArr(0 To yIndent) As String
    xArr(yIndent) = 1
    End If
    
    
    'segment = xArr(yIndent)
    'segment = segment + 1
    'xArr(yIndent) = segment
    CustomOutline = Join(xArr, ".")
End Function
Use: Added code above to the "Module" section. So now I can create a cell function: =customOutline('PreviousOutlineCell','CurrentRowsIndentedText')

Issues:
a) If a text cell is indented >1 level in beyond the previous row it will break the function when you pull back in.
<!-- Please do not remove this header --><!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com) --><table bordercolordark="#FFFFFF" border="1" bordercolor="#c0c0c0" cellpadding="2" cellspacing="0"><tbody><tr><td align="left" valign="bottom"><nobr>15</nobr></td><td align="left" valign="bottom"><nobr>High level</nobr></td></tr><tr><td align="left" valign="bottom"><nobr>15.1</nobr></td><td align="left" valign="bottom"><nobr>_another level</nobr></td></tr><tr><td align="left" valign="bottom"><nobr>15.1.1</nobr></td><td align="left" valign="bottom"><nobr>_ _more text</nobr></td></tr><tr><td align="left" valign="bottom"><nobr>16</nobr></td><td align="left" valign="bottom"><nobr>Back to the beginning</nobr></td></tr><tr><td align="left" valign="bottom"><nobr>16..1</nobr></td><td align="left" valign="bottom"><nobr>_ _ _Indented too far</nobr></td></tr><tr><td align="left" valign="bottom"><nobr>16..1.1</nobr></td><td align="left" valign="bottom"><nobr>_ _ _ _Indented again, missing a cell</nobr></td></tr><tr><td align="left" valign="bottom"><nobr>#VALUE!</nobr></td><td align="left" valign="bottom"><nobr>_ Oops, an error encountered</nobr></td></tr></tbody></table>


b) When I update "Indented Text", either by decreasing or increasing the indent, i have to activate the formula cell to get it to update.
 
Upvote 0
dave981 said:
Thanks Jindon. That worked well for when I entered, "1.1" and adjusting the outline depth of the text column. But it didn't work for updating the outline column when i adjusted the text.

E.g. If I changed the indent level of "__Text" to "_Text", i'd like the numerical counter/outline to adjust accordingly:
1.1.1 __Text

To
1.1 _Text

Thanks for the help!
Dave
Click to expand...
What do you want to do when Col.E Is Error ?
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, x, y As Long
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
If Not Intersect(Target, Range("e:e")) Is Nothing Then
    For Each r In Intersect(Target, Range("e:e")).Cells
        If (InStr(r.Value, ".") = 0) + (IsError(r.Value)) Then
            r.Offset(,1).Value = LTrim(r.Offset(,1).Value)
        Else
            x = Split(r.Value, ".")
            r.Offset(,1).Value = Space(UBound(x) + 1) & LTrim(r.Offset(,1).Value)
        End If
    Next
End If
If Not Intersect(Target, Range("f:f")) Is Nothing Then
    For Each r In Intersect(Target, Range("f:f")).Cells
        If (InStr(r.Offset(,-1).Value ".") = 0) + _
            (IsError(r.Offset(,-1).Value)) Then
            r.Value = LTrim(r.Text)
        Else
            x = Split(r.Offset(,-1).Text, ".")
            y = Len(r.Text) - Len(LTrim(r.Text))
            z = y - UBound(x)
            If z > 0 Then
                r.Offset(,-1).Value = r.Offset(,-1).Value & _
                    Application.Rept(".1", z)
            Else
                ReDim Preserve x(UBound(x) + z)
                r.Value = Join(x,".")
            End If
        End If
    Next
End If 
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
864
gvsumich
G
E
  • Question
Adding a custom message.
Replies
1
Views
458
Micron
M
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
340
VBAEXCELNew
V
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,215,949
Messages
6,127,888
Members
449,411
Latest member
AppellatePerson

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top