Applying formatting on a cell range of a VBA table

[askou]

Greetings to the community,

I am trying to write a VBA macro that applies a formatting change on a cell range of a Powerpoint table. I have written the following code with the purpose to change vertical alignment of the selected range:


Sub ChangeRange()
Dim Shp As Shape
Set Shp = ActiveWindow.Selection.ShapeRange(1)
Shp.TextFrame2.VerticalAnchor = msoAnchorMiddle
End Sub

Can somebody explain what I am doing wrong?

Best,
Andreas

[John Wilson]

Depends what you need to achieve. Shp is the table and there's no way to directly set the verticxal anchor for a table. You probably need to loop through the cells. If you need to only apply to selected cells try this otherwise set all cells.


Sub ChangeRange()
Dim iCol As Integer
Dim iRow As Integer
Dim otbl As Shape
Set otbl = ActiveWindow.Selection.ShapeRange(1)
If otbl.HasTable Then
For iRow = 1 To otbl.Table.Rows.Count
For iCol = 1 To otbl.Table.Columns.Count
If otbl.Table.Cell(iRow, iCol).Selected Then
otbl.Table.Cell(iRow, iCol).Shape.TextFrame2.VerticalAnchor = msoAnchorMiddle
End If
Next iCol
Next iRow
End If
End Sub

[askou]

Thanks John,

Really appreciate the response.

Best,
Andreas

[demirufuk]

Hi John,

Similar to above quote, I am trying to write a VBA code that allows format change for some certain range of cells from `USD` currency to a custom format `;;;` (to make it invisible) after data entry and file is saved.
Any suggestion or help would be appriciate.

Thanks a lot in advance
Ufuk

[John Wilson]

Is this in PowerPoint or Excel?

[demirufuk]

Excel John.. I am using 2010 version.

Thanks
Ufuk

[John Wilson]

I guessed that but you are posting in the PowerPoint section.

[demirufuk]

Sorry about that..
Thanks John..

[magnel]

Hi John, the alignment is working excellent. I was trying to add the following piece of code in the above code
.Font.Glow.Radius = 10

but was finding it difficult.
It would result in giving a text glow effect to the selected range in the above code. Please can you help me solve the puzzle.

[John Wilson]

Not sure what the problem is but this should work


Sub ChangeRangeGlow()
Dim iCol As Integer
Dim iRow As Integer
Dim otbl As Shape
Set otbl = ActiveWindow.Selection.ShapeRange(1)
If otbl.HasTable Then
For iRow = 1 To otbl.Table.Rows.Count
For iCol = 1 To otbl.Table.Columns.Count
If otbl.Table.Cell(iRow, iCol).Selected Then
With otbl.Table.Cell(iRow, iCol).Shape.TextFrame2
.VerticalAnchor = msoAnchorMiddle
.TextRange.Font.Glow.Radius = 10
.TextRange.Font.Glow.Color.RGB = RGB(255, 0, 0)
End With
End If
Next iCol
Next iRow
End If
End Sub

[magnel]

Thank you so much John, it works.