Push value to a cell attribute?

[G Lykos]

Greetings! Am having difficulty imagining an algorithm so am looking for
guidance.

Would like to manipulate the "indent" attribute of a column of cells that
contains text. It would seem that a VBA function is needed, and it would be
fed a target cell absolute location and an indent value. Is this then a
single-cell formula, invoking the function and passing it a target cell
location and an indent value? Is entering the formula with function and
parameters in a cell and then "enter"ing sufficient to cause it to process?
Is there a way to arrange this to make it easy to apply the function to a
column of adjacent cells using drag/copy, perhaps with corresponding columns
of target cell locations and indent values?

Thanks for your ideas!
George

 

[Norman Jones]

Hi G,

Try:

'============>>
Public Sub IndentIt(Rng As Range, indentNum As Long)
Rng.IndentLevel = indentNum
End Sub
'<<============

Use like:

'============>>
Public Sub TestIt()
IndentIt Range("A1:A10"), 3
End Sub
'<<============

The indenting can be performed from the Format | Cells menu.

 

[G Lykos]

Norman, thanks for the suggestion. How could the range parameter be
(re)defined such that the subroutine would pick up the range value contained
in (for example) an adjacent cell in lieu of a hard-coded parameter? The
interest would be to do a drag/copy after setting up the first formula and
adjacent cell so as to process a column of data.

Thanks again,
George

 

[Norman Jones]

Hi George,

A UDF returns a value to the calling cell; it cannot change the format of
another cell or alter its environment.

Consider, instead, assigning the following code to a toolbar button:

'============>>
Public Sub IndentIt()
On Error Resume Next
Selection.IndentLevel = Selection(1).IndentLevel + 1
On Error GoTo 0
End Sub
'<<============

The above will increase the indent of the selected cells each time the
button is clicked.

If this approach appeals to you, you may wish to assign the following code,
which reduces the indent, to a second button:

'============>>
Public Sub ReduceIndentIt()

Selection.IndentLevel = _
Application.Max(Selection(1).IndentLevel - 1, 0)

End Sub
'<<============

 

[G Lykos]

Norman, the suggested Sub does exactly what I was looking for, setting the
indentation of a remote cell. Now, can you please help me adapt it?

1. Can a UDF be used to call the IndentIt Sub (in lieu of TestIt Sub) below?
2. How can the UDF be arranged so as to read the target cell location and
desired indentation from two adjacent cells (e.g. 1 and 2 cells to the right
of the UDF formula cell) and pass them to the Sub?

Thanks again,
George