Some rudimentary VBA Visual Basic commands assistance needed concerning text formatting

I know very little about vba. But am trying to tweak a macro for visio.

I want to tweak text but can't find anything on this:
 
            ' set font size and color, text alignment, border, fill and shadow
            .Cells("Char.Size").Formula = "10 pt"
            .Cells("Char.Color").Formula = 0     ' black
            .Cells("Para.HorzAlign") = 0         ' left justify
            .Cells("LinePattern").Formula = 0    ' none
            .Cells("FillPattern").Formula = 0    ' none
            .Cells("ShdwPattern").Formula = 0    ' none

1) Is there a table  that lists what numbers to use to change HorizAlign to get centered?

2) What if I wanted to bold the text?

3) there's a line after these for text (it's a table of contents):   .Text = arrPages(i, 1) & vbTab & CInt(arrPages(i, 2))

I'd love for the space between the page name and page number to be dotted  like:   Licenses.. . . . . . . .3

Do you know how I could get that?

Any idea why I can't find much about this on the web? I was googling cells syntax vba and pasting these lines into google. Didn't come up with anything.

Thanks!
BeGentleWithMe-INeedHelpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Not quite answering your question in detail, but just some pointers here and there.

I know that this isn't quite intuitive, but I typically, save the file as an XML and then you can sift through all the code it uses inside. This was way too easy with Office XML format. Ever since it has gone in .???x format, this has become a bit too complicated to handle.

Given below is a link to the Visio VBA reference, could potentially be helpful.

https://msdn.microsoft.com/en-us/vba/vba-visio
0
KoenChange and Transition ManagerCommented:
going on Nitin's comment...just do it in Excel, whilst recording it in a macro...then go check the code...

just an example of some text tweaking:
Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.FormulaR1C1 = "some tekst"
    Range("B2").Select
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("B2").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("B2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .Color = -4165632
        .TintAndShade = 0
    End With
End Sub

Open in new window

0
BeGentleWithMe-INeedHelpAuthor Commented:
thanks. using Excel to write the code - I forgot about that. Cute.

But while the script I have now talks of

  .Cells("Char.Size").Formula = "10 pt"
            .Cells("Char.Color").Formula = 0     ' black
            .Cells("Para.HorzAlign") = 0         ' left justify

and the code from Excel is a different syntax

        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom

I tried putting these inside the cells parenthesis / changing things, but get errors whenever it runs. Or even, no change in the text.
1
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Unfortunately, there is very little crossover between Excel and Visio syntax.

However, you can use the same approach you tried in Excel but do it in Visio, i.e., turn on Visio's macro recorder, make a series of changes, and then look at the recorded macro. You should be able to discover how to make text bold, center it, etc.

One warning: the macro recorder uses a more verbose VBA style so the syntax it generates may not look exactly like your sample code above. If you run into this difficulty, just reply here and we'll get it sorted out.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
BTW, there are also hundreds of predefined enumerations in VBA that can be helpful. For example, type. Visio.vis in the VBA editor and then scroll down. Unfortunately, there are so many enumerations that it's not always easy to find what you want. But some are discoverable, e.g., try Visio.visbold or Visio.visHor to see a list of settings related to horizontal alignment.

Of course, discovering a couple of useful enumerations only solves part of the problem, but between these and the macro recorder, you'll be further along the path.
0
BeGentleWithMe-INeedHelpAuthor Commented:
You've done so much already with this for me, I hate bothering you more.

a) didn't know about the visio macro recorder. Thanks. Funny, googled that and had to first turn on the developer tab.  Just trying to tweak things and winding up with a course in VBA, which sorry, I am trying to avoid.  I won't use VBA till I wind up forgetting all this anyway.

2).  Ran the macro to bold and center text (from the macro you posted).  Came up with this scrpt. Put that in your script as a subroutine to bold the first heading.

Sub Macro3()

    'Enable diagram services
    Dim DiagramServices As Integer
    DiagramServices = ActiveDocument.DiagramServicesEnabled
    ActiveDocument.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150

    Dim UndoScopeID2 As Long
    UndoScopeID2 = Application.BeginUndoScope("Align Center")
    Dim vsoCharacters1 As Visio.Characters
    Set vsoCharacters1 = Application.ActiveWindow.Page.Shapes.ItemFromID(11).Characters
    vsoCharacters1.Begin = 0
    vsoCharacters1.End = 22
    vsoCharacters1.ParaProps(visHorzAlign) = 1#
    Application.EndUndoScope UndoScopeID2, True

    Dim UndoScopeID4 As Long
    UndoScopeID4 = Application.BeginUndoScope("Bold")
    Dim vsoCharacters3 As Visio.Characters
    Set vsoCharacters3 = Application.ActiveWindow.Page.Shapes.ItemFromID(11).Characters
    vsoCharacters3.Begin = 0
    vsoCharacters3.End = 22
    vsoCharacters3.CharProps(visCharacterStyle) = 17#
    Application.EndUndoScope UndoScopeID4, True

    'Restore diagram services
    ActiveDocument.DiagramServicesEnabled = DiagramServices

End Sub

Open in new window


It ran good!!  BUT this was a successfully tweak of your script to make 2 ToCs - sorted by name and sorted by page number.

I put the call in the same spot for the 2nd sort.  Didn't work.

After some experimenting, I realize - the visio created subroutine works on a specific window ID.  That's fine for ID(1) - the headng of first sort.  But heading of the 2nd sort will have different ID depending on how many pages in the package.

Here's my tweaked version of your macro.  I'm thinking the answer I am looking for is a couple lines to insert right around font size, etc to bold and center that text?

Care to share those?


AND..... 2 more things I'd love to add in:

dotted lines between the page name and page number... word does that. Just to class it up. But I don't think visio offers dotted tabs?  So it'd have to be a dotted line (object) not part of the text.  That seems way more work than it's worth.

When there's lots of pages (very rare situation at least for me) that the app knows to change the font size / make it smaller.  Again, at least for me, way more work than it's worth. For the few times it comes up, I can do that manually.

Those guard lines you have I think do stuff like that for other situations. As you said when you posted the file, there's not much error testing in it.

Regardless, it works 95% for me.  THANKS!

Option Explicit

Enum iSortTypeList
    ieZero
    ieName
    ieNumber
End Enum

Sub TOC()
    Call GenerateTOC(ieName)
    Call GenerateTOC1(ieNumber)
End Sub

    Sub GenerateTOC(iSortType)
    'Comments with @@@@ are to allow adjustment of text
    ' generates an alphabetical or numerical list of page names and adds hyperlink to each page

'@@@@@@how far to start from left
    Const dLeftEdge     As Double = 0.5
    
'@@@@space between text and number
    Const dWidth        As Double = 3.5
    
'@@@@space between lines
    Const dDeltaY       As Double = 0.2
    Const sTOCpagename  As String = "ToC"

    Dim arrPages()      As String
    ''''    Dim arrNameNumber() As String
    Dim pg              As Visio.Page
    Dim shp             As Visio.Shape
    Dim iPageCount      As Integer
    Dim dX              As Double
    Dim dY              As Double
    Dim i               As Integer
    Dim HL              As Visio.Hyperlink
    
    ' create array of page names and numbers and sort them
    iPageCount = 0
    ReDim arrPages(ActiveDocument.Pages.Count, 2)
    For Each pg In ActiveDocument.Pages
        ' exclude background pages and existing TOC, if any
        If (Not pg.Background) And (pg.Name <> sTOCpagename) Then
            iPageCount = iPageCount + 1
    ''''            ' store page name and number separated by a pipe character (they will be split later)
            arrPages(iPageCount, 1) = pg.Name
            arrPages(iPageCount, 2) = CStr(Format(pg.Index, "000"))     ' make equal length strings
        End If
    Next
    
    Call SortAscend_x2(arrPages, iSortType, 1, iPageCount)

    On Error Resume Next
    Set pg = ActiveDocument.Pages("ToC")
    If pg Is Nothing Then                                       ' no existing page so create one
        ' create new page for TOC and make it the first page
        Set pg = ActiveDocument.Pages.Add
        pg.Name = "ToC"
        'make this the first page
        pg.Index = 1
        ActiveWindow.Page = pg.Name
    Else                                                        ' page exists so delete everything on it
        With ActiveWindow
            .Page = pg.Name
            .SelectAll
            .Selection.Delete
        End With
    End If
    
    dX = dLeftEdge
' @@@@@set vertical location for first TOC entry
    dY = pg.PageSheet.Cells("PageHeight").Result(visInches) - 1.3

    ''''''''''''''''''''''''
    
'Make header for Table of Contents
        
     dY = dY - dDeltaY
        Set shp = pg.DrawRectangle(dX, dY, dX + dWidth, dY + dDeltaY * 2)
        With shp
            ' set right-aligned tab stop at right side of rectangle
            .RowType(visSectionTab, visRowTab) = Visio.VisRowTags.visTagTab2
            .CellsSRC(visSectionTab, 0, visTabStopCount).FormulaU = "1"
            .CellsSRC(visSectionTab, 0, visTabPos).FormulaU = "GUARD(Width-LeftMargin-RightMargin)"
            .CellsSRC(visSectionTab, 0, visTabAlign).FormulaU = Visio.visTabStopRight
            .CellsSRC(visSectionTab, 0, 3).FormulaU = "0"
            
' @@@@@@@@@set font size and color, text alignment, border, fill and shadow
            .Cells("Char.Size").Formula = "16 pt"
            .Cells("Char.Color").Formula = 0     ' black
            .Cells("Para.HorzAlign") = 0         ' left justify
            .Cells("LinePattern").Formula = 0    ' none
            .Cells("FillPattern").Formula = 0    ' none
            .Cells("ShdwPattern").Formula = 0    ' none
        
            
            ''''' extract page name and number that are separated by a pipe character
            ''''            arrNameNumber = Split(arrPages(i), "|")
            .Text = "Sorted by Page Name"
            'added 4/25/18
            Call BoldCenterHeadings
            End With
    ''''''''''''''''''''''''
    For i = 1 To iPageCount
        ' draw rectangle
        dY = dY - dDeltaY
        Set shp = pg.DrawRectangle(dX, dY, dX + dWidth, dY + dDeltaY * 2)
        With shp
            ' set right-aligned tab stop at right side of rectangle
            .RowType(visSectionTab, visRowTab) = Visio.VisRowTags.visTagTab2
            .CellsSRC(visSectionTab, 0, visTabStopCount).FormulaU = "1"
            .CellsSRC(visSectionTab, 0, visTabPos).FormulaU = "GUARD(Width-LeftMargin-RightMargin)"
            .CellsSRC(visSectionTab, 0, visTabAlign).FormulaU = Visio.visTabStopRight
            .CellsSRC(visSectionTab, 0, 3).FormulaU = "0"
            
' @@@@@@@@@set font size and color, text alignment, border, fill and shadow
            .Cells("Char.Size").Formula = "12 pt"
            .Cells("Char.Color").Formula = 0     ' black
            .Cells("Para.HorzAlign") = 0         ' left justify
            .Cells("LinePattern").Formula = 0    ' none
            .Cells("FillPattern").Formula = 0    ' none
            .Cells("ShdwPattern").Formula = 0    ' none
            
            ''''' extract page name and number that are separated by a pipe character
            ''''            arrNameNumber = Split(arrPages(i), "|")
            .Text = arrPages(i, 1) & vbTab & CInt(arrPages(i, 2))
          
            
            Set HL = .Hyperlinks.Add                ' create hyperlink
            HL.SubAddress = arrPages(i, 1) ' set hyperlink to page name
        End With
    Next i

    ActiveWindow.DeselectAll

End Sub
Private Sub SortAscend_x2(ByRef arr, SortKey, SortStart, SortEnd)
    ' SortKey identifies column in array by which to sort
    ' SortStart and SortEnd allow flexibility to sort only selected rows within the array
    ' UCASE() does case-insensitive sort

    Dim i As Integer, j As Integer
    Dim Temp1 As String, Temp2 As String

    If SortEnd - SortStart <= 0 Then Exit Sub

    ' bubble sort
    For i = SortEnd - 1 To SortStart Step -1
        For j = SortStart To i
            If UCase(arr(j, SortKey)) > UCase(arr(j + 1, SortKey)) Then ' Compare neighboring elements
               Temp1 = arr(j, 1)
               Temp2 = arr(j, 2)
               arr(j, 1) = arr(j + 1, 1)
               arr(j, 2) = arr(j + 1, 2)
               arr(j + 1, 1) = Temp1
               arr(j + 1, 2) = Temp2
            End If
        Next j
    Next i

    'For i = SortStart To SortEnd
    '    Debug.Print arr(i, 1) & " " & arr(i, 2)
    'Next i

End Sub

''''''
    Sub GenerateTOC1(iSortType)
    ' generates an alphabetical or numerical list of page names and adds hyperlink to each page

'@@@@@@how far to start from left
    Const dLeftEdge     As Double = 6.5
    
'@@@@space between text and number
    Const dWidth        As Double = 3.5
    
'@@@@space between lines
    Const dDeltaY       As Double = 0.2
    Const sTOCpagename  As String = "ToC"

    Dim arrPages()      As String
    ''''    Dim arrNameNumber() As String
    Dim pg              As Visio.Page
    Dim shp             As Visio.Shape
    Dim iPageCount      As Integer
    Dim dX              As Double
    Dim dY              As Double
    Dim i               As Integer
    Dim HL              As Visio.Hyperlink
    
    ' create array of page names and numbers and sort them
    iPageCount = 0
    ReDim arrPages(ActiveDocument.Pages.Count, 2)
    For Each pg In ActiveDocument.Pages
        ' exclude background pages and existing TOC, if any
        If (Not pg.Background) And (pg.Name <> sTOCpagename) Then
            iPageCount = iPageCount + 1
    ''''            ' store page name and number separated by a pipe character (they will be split later)
            arrPages(iPageCount, 1) = pg.Name
            arrPages(iPageCount, 2) = CStr(Format(pg.Index, "000"))     ' make equal length strings
        End If
    Next
    
    Call SortAscend_x21(arrPages, iSortType, 1, iPageCount)

    On Error Resume Next
    Set pg = ActiveDocument.Pages("ToC")
    If pg Is Nothing Then                                       ' no existing page so create one
        ' create new page for TOC and make it the first page
        Set pg = ActiveDocument.Pages.Add
        pg.Name = "ToC"
        'make this the first page
        pg.Index = 1
        ActiveWindow.Page = pg.Name
    Else                                                        ' page exists so delete everything on it
        '#With ActiveWindow
          '#  .Page = pg.Name
           '# .SelectAll
            '# .Selection.Delete
       '# End With
    End If
    
    dX = dLeftEdge
' @@@@@set vertical location for first TOC entry
    dY = pg.PageSheet.Cells("PageHeight").Result(visInches) - 1.3
    
    ''''''''''''''''''''''''
    
'Make header for Table of Contents
        
     dY = dY - dDeltaY
        Set shp = pg.DrawRectangle(dX, dY, dX + dWidth, dY + dDeltaY * 2)
        With shp
            ' set right-aligned tab stop at right side of rectangle
            .RowType(visSectionTab, visRowTab) = Visio.VisRowTags.visTagTab2
            .CellsSRC(visSectionTab, 0, visTabStopCount).FormulaU = "1"
            .CellsSRC(visSectionTab, 0, visTabPos).FormulaU = "GUARD(Width-LeftMargin-RightMargin)"
            .CellsSRC(visSectionTab, 0, visTabAlign).FormulaU = Visio.visTabStopRight
            .CellsSRC(visSectionTab, 0, 3).FormulaU = "0"
            
' @@@@@@@@@set font size and color, text alignment, border, fill and shadow
            .Cells("Char.Size").Formula = "16 pt"
            .Cells("Char.Color").Formula = 0     ' black
            .Cells("Para.HorzAlign") = 0         ' left justify
            .Cells("LinePattern").Formula = 0    ' none
            .Cells("FillPattern").Formula = 0    ' none
            .Cells("ShdwPattern").Formula = 0    ' none
            
            ''''' extract page name and number that are separated by a pipe character
            ''''            arrNameNumber = Split(arrPages(i), "|")
            .Text = "Sorted by Page Number"
            
        'added 4/25/18
            Call BoldCenterHeadings
            
            End With
    ''''''''''''''''''''''''
    
    For i = 1 To iPageCount
        ' draw rectangle
        dY = dY - dDeltaY
        Set shp = pg.DrawRectangle(dX, dY, dX + dWidth, dY + dDeltaY * 2)
        
        With shp
            ' set right-aligned tab stop at right side of rectangle
            .RowType(visSectionTab, visRowTab) = Visio.VisRowTags.visTagTab2
            .CellsSRC(visSectionTab, 0, visTabStopCount).FormulaU = "1"
            .CellsSRC(visSectionTab, 0, visTabPos).FormulaU = "GUARD(Width-LeftMargin-RightMargin)"
            .CellsSRC(visSectionTab, 0, visTabAlign).FormulaU = Visio.visTabStopRight
            .CellsSRC(visSectionTab, 0, 3).FormulaU = "0"
            
' @@@@@@@@@set font size and color, text alignment, border, fill and shadow
            .Cells("Char.Size").Formula = "12 pt"
            .Cells("Char.Color").Formula = 0     ' black
            .Cells("Para.HorzAlign") = 0         ' left justify
            .Cells("LinePattern").Formula = 0    ' none
            .Cells("FillPattern").Formula = 0    ' none
            .Cells("ShdwPattern").Formula = 0    ' none
            
            ''''' extract page name and number that are separated by a pipe character
            ''''            arrNameNumber = Split(arrPages(i), "|")
            .Text = CInt(arrPages(i, 2)) & vbTab & arrPages(i, 1)
            
            Set HL = .Hyperlinks.Add                ' create hyperlink
            HL.SubAddress = arrPages(i, 1) ' set hyperlink to page name
        End With
    Next i

    ActiveWindow.DeselectAll

End Sub
Private Sub SortAscend_x21(ByRef arr, SortKey, SortStart, SortEnd)
    ' SortKey identifies column in array by which to sort
    ' SortStart and SortEnd allow flexibility to sort only selected rows within the array
    ' UCASE() does case-insensitive sort

    Dim i As Integer, j As Integer
    Dim Temp1 As String, Temp2 As String

    If SortEnd - SortStart <= 0 Then Exit Sub

    ' bubble sort
    For i = SortEnd - 1 To SortStart Step -1
        For j = SortStart To i
            If UCase(arr(j, SortKey)) > UCase(arr(j + 1, SortKey)) Then ' Compare neighboring elements
               Temp1 = arr(j, 1)
               Temp2 = arr(j, 2)
               arr(j, 1) = arr(j + 1, 1)
               arr(j, 2) = arr(j + 1, 2)
               arr(j + 1, 1) = Temp1
               arr(j + 1, 2) = Temp2
            End If
        Next j
    Next i

    'For i = SortStart To SortEnd
    '    Debug.Print arr(i, 1) & " " & arr(i, 2)
    'Next i

End Sub


Private Sub BoldCenterHeadings()

    'Enable diagram services
    Dim DiagramServices As Integer
    DiagramServices = ActiveDocument.DiagramServicesEnabled
    ActiveDocument.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150

    Dim UndoScopeID2 As Long
    UndoScopeID2 = Application.BeginUndoScope("Bold")
    Dim vsoCharacters1 As Visio.Characters
    Set vsoCharacters1 = Application.ActiveWindow.Page.Shapes.ItemFromID(1).Characters
    vsoCharacters1.Begin = 0
    vsoCharacters1.End = 20
    vsoCharacters1.CharProps(visCharacterStyle) = 17#
    Application.EndUndoScope UndoScopeID2, True

    Dim UndoScopeID4 As Long
    UndoScopeID4 = Application.BeginUndoScope("Align Center")
    Dim vsoCharacters3 As Visio.Characters
    Set vsoCharacters3 = Application.ActiveWindow.Page.Shapes.ItemFromID(1).Characters
    vsoCharacters3.Begin = 0
    vsoCharacters3.End = 20
    vsoCharacters3.ParaProps(visHorzAlign) = 1#
    Application.EndUndoScope UndoScopeID4, True

    'Restore diagram services
    ActiveDocument.DiagramServicesEnabled = DiagramServices

End Sub

Open in new window

1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Visio

From novice to tech pro — start learning today.