Prev: Array problem? List comparison?
Next: find a non-empty cell
From: Maver1ck666 on 29 Apr 2010 04:49
I've had a quick search and couldn't seem to find anything on this so
hopefully you can help :)

I have a spreadsheet with a number of subtotals with a second tier. I also
have a button to add a new sub task to a tier 1 group. For example:

Task A
Sub Task a.1 value
Sub Task a.2 value

Task B
Sub Task b.1 value
Sub Task b.2 value
Sub Task b.3 value
Sub Task b.4 value

Task C
Sub Task c.1 value

The command button provides a usercontrol which displays the tier 1 tasks
with a blank text box to add a new item. What I need is for some code to
check that the tier 1 task isn't collapsed before it inserts a line and if it
is, to expand it.

Any suggestions please?

Thanks all!

Mav
From: Jacob Skaria on 29 Apr 2010 05:46
If you mean to display all outline levels; try the below

Sub Macro4()
Dim lngRow As Long, varOLevel As Variant
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Rows(lngRow).OutlineLevel > varOLevel Then
varOLevel = Rows(lngRow).OutlineLevel
End If
Next
ActiveSheet.Outline.ShowLevels varOLevel
End Sub

--
Jacob (MVP - Excel)


"Maver1ck666" wrote:

> I've had a quick search and couldn't seem to find anything on this so
> hopefully you can help :)
>
> I have a spreadsheet with a number of subtotals with a second tier. I also
> have a button to add a new sub task to a tier 1 group. For example:
>
> Task A
> Sub Task a.1 value
> Sub Task a.2 value
>
> Task B
> Sub Task b.1 value
> Sub Task b.2 value
> Sub Task b.3 value
> Sub Task b.4 value
>
> Task C
> Sub Task c.1 value
>
> The command button provides a usercontrol which displays the tier 1 tasks
> with a blank text box to add a new item. What I need is for some code to
> check that the tier 1 task isn't collapsed before it inserts a line and if it
> is, to expand it.
>
> Any suggestions please?
>
> Thanks all!
>
> Mav
From: Maver1ck666 on 29 Apr 2010 09:08
Cheers for that Jacob. It kinda works but expands all the lists as opposed to
just the one the cursor is at. Any ideas please?

Mav

"Jacob Skaria" wrote:

> If you mean to display all outline levels; try the below
>
> Sub Macro4()
> Dim lngRow As Long, varOLevel As Variant
> For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
> If Rows(lngRow).OutlineLevel > varOLevel Then
> varOLevel = Rows(lngRow).OutlineLevel
> End If
> Next
> ActiveSheet.Outline.ShowLevels varOLevel
> End Sub
>
> --
> Jacob (MVP - Excel)
>
>
> "Maver1ck666" wrote:
>
> > I've had a quick search and couldn't seem to find anything on this so
> > hopefully you can help :)
> >
> > I have a spreadsheet with a number of subtotals with a second tier. I also
> > have a button to add a new sub task to a tier 1 group. For example:
> >
> > Task A
> > Sub Task a.1 value
> > Sub Task a.2 value
> >
> > Task B
> > Sub Task b.1 value
> > Sub Task b.2 value
> > Sub Task b.3 value
> > Sub Task b.4 value
> >
> > Task C
> > Sub Task c.1 value
> >
> > The command button provides a usercontrol which displays the tier 1 tasks
> > with a blank text box to add a new item. What I need is for some code to
> > check that the tier 1 task isn't collapsed before it inserts a line and if it
> > is, to expand it.
> >
> > Any suggestions please?
> >
> > Thanks all!
> >
> > Mav
 | 
Pages: 1
Prev: Array problem? List comparison?
Next: find a non-empty cell