Center Cycle and Underline Cycle

[bkanne]

Can someone please help me write the proper code for cycling between different Center and Underline Formats?

For the Center Cycle, the code would go from General Alignment, to Center, to Center Across Selection, and then back to General.

For the Underline Cycle, the code would go from General, to Normal Underline, to Single Accounting Underline.

I have some similar code from a Number Format Cycle that switches between several colors, shown below:

Sub CycleFormat()
   Dim Format1 AsString, Format2 AsString
   Dim Format3 AsString, Format4 AsString

    Format1 = "#,##0.0_);(#,##0.0);-_)"
    Format2 = "$#,##0.0_);($#,##0.0);-_)"
    Format3 = "#,##0.0%_);(#,##0.0%)"
    Format4 = "#,##0.0x_);(#,##0.0x)"

   With Selection
         If .NumberFormat = Format1 Then
            .NumberFormat = Format2
         ElseIf .NumberFormat = Format2 Then
            .NumberFormat = Format3
         ElseIf .NumberFormat = Format3 Then
            .NumberFormat = Format4
         ElseIf .NumberFormat = Format4 Then
            .NumberFormat = Format1
         Else
            .NumberFormat = Format1
         EndIf
   EndWith
EndSub

I'm hoping to do something similar to this, but for the formatting issues I described above.

Thanks so much in advance!

 

[Belleke]

Hello,
Can you find some inspiration in this one?
See attached.
Hope it helps.

 

[bkanne]

Thanks Belleke, this is great!

Is there anyway to do this without a dialog box? It would be two separate pieces of code - one would cycle between the different underlining formats, and the other would cycle between the different center alignment formats.

I would like to be able to assign a keyboard shortcut to each.

For example, for the underline cycle, I will assign something like "ctrl alt shift u"
as the shortcut, and then each sequential time I press it, the underline formatting will change.

Same goes for the center cycle.

Thanks so much!

 

[Belleke]

Thank you for the feedback,
Because I am (Belgium) dutch can you post an example with the result you like,it is not so easy to for me to answer.

 

[bkanne]

I would like this:

Sub CycleFormat()
   Dim Format1 AsString, Format2 AsString
   Dim Format3 AsString, Format4 AsString

    Format1 = "#,##0.0_);(#,##0.0);-_)"
    Format2 = "$#,##0.0_);($#,##0.0);-_)"
    Format3 = "#,##0.0%_);(#,##0.0%)"
    Format4 = "#,##0.0x_);(#,##0.0x)"

   With Selection
         If .NumberFormat = Format1 Then
            .NumberFormat = Format2
         ElseIf .NumberFormat = Format2 Then
            .NumberFormat = Format3
         ElseIf .NumberFormat = Format3 Then
            .NumberFormat = Format4
         ElseIf .NumberFormat = Format4 Then
            .NumberFormat = Format1
         Else
            .NumberFormat = Format1
         EndIf
   EndWith
EndSub

But, for:

xlUnderlineStyleSingle
xlUnderlineStyleDouble
xlUnderlineStyleSingleAccounting
xlUnderlineStyleNone

 

[bkanne]

Was able to come up with what I needed. Please see below:

Sub CenterCycle()

        With Selection
       
        If .HorizontalAlignment = xlLeft Then
        .HorizontalAlignment = xlCenter
       
        ElseIf .HorizontalAlignment = xlCenter Then
        .HorizontalAlignment = xlCenterAcrossSelection
       
        ElseIf .HorizontalAlignment = xlCenterAcrossSelection Then
        .HorizontalAlignment = xlLeft
               
        Else
        .HorizontalAlignment = xlLeft
       
        End If
       
        End With
       
End Sub

and:

Sub UnderlineCycle()

        With Selection.Font
       
        If .Underline = xlUnderlineStyleNone Then
        .Underline = xlUnderlineStyleSingle
       
        ElseIf .Underline = xlUnderlineStyleSingle Then
        .Underline = xlUnderlineStyleDouble
       
        ElseIf .Underline = xlUnderlineStyleDouble Then
        .Underline = xlUnderlineStyleSingleAccounting
       
        ElseIf .Underline = xlUnderlineStyleSingleAccounting Then
        .Underline = xlUnderlineStyleNone
       
        Else
        .Underline = xlUnderlineStyleNone
       
        End If
       
        End With
       
End Sub

Hopefully those are helpful to someone!