Calling and Excel Constant by A Variable

[diakonos1984]

Very simple question:
Given a userform with ComboBoxHorizontalAlign populated with the values "Left", "Right", and "Center", and a current Value of "Left"

With wsTarget.UsedRange
    'Set horizontal alignment
    .HorizontalAlignment = xlLeft 'This works
    .HorizontalAlignment = "xl" & ComboBoxHorizontalAlign.Value 'This doesn't work.  I want it to.
End With

What am I missing?

[shg]

    Select Case ComboBoxHorizontalAlign.Value
      Case "Left"
        .HorizontalAlignment = xlLeft
      Case "Right"
        .HorizontalAlignment = xlRight
      Case ...
        .HorizontalAlignment = ...
    End Select

[diakonos1984]

It gets worse in another place that I hadn't mentioned before: Paper Sizes.
There are Excel constants for approximately 40 different paper sizes. I have a function that queries my printer and checks to see what page sizes are available. It would sure be nice to dump an item selected from that list straight to the .PaperSize item, instead of having to write a Select with 40 Cases.

I know... I'm whining... and hoping someone has a better way.

[Richard Buttrey]

....There are Excel constants......

I think with that statement you've probably answered your own question. A constant is not a string of text that can be built up by combining other strings, it's an integral and indivisible reference to an object's property.

[diakonos1984]

Sigh..... I know I can do that..... is that really the only way? I have to trade out one clean line of code for a lengthy Case statement?

I appreciate the help, shg.

[shg]

In VBA, name binding of constants and simple variables to the identifiers that reference them occurs at compile time; i.e., they are statically bound.

You could put them in a collection, using left/right/center/whatever as the key and the enumeration value as the item:

.HorizontalAlignment = myCollection(textbox.value)

[Leith Ross]

Hello diakonos1984,

Maybe this option would be more appealing...

    Dim cbAlign As String
    
        With wsTarget.UsedRange
            'Set horizontal alignment
            .HorizontalAlignment = xlLeft 'This works
            cbAlign = "xl" & ComboBoxHorizontalAlign.Value
            .HorizontalAlignment = Switch(cbAlign = "xlLeft", xlLeft, cbAlign = "xlRight", xlRight, cbAlign = "xlCenter", xlCenter)
        End With

[diakonos1984]

Thank you for those helpful suggestions.
I may use Leith's concise Switch for the alignments and shg's Collection item for the paper sizes.

[romperstomper]

The paper size constants range from 1 to 41 (excluding xlPaperUser which is 256) so if you load the combobox in the correct order, you could simply add 1 to the ListIndex to get the correct xlPaperSize value.