what is wrong with this line of vba code for Excel Print

Question

I have a combobox with list of page size in excel. i want to change page sizes with combobox selection change.

Following is not working

Public Sub UpdateSize()
Dim Papersizetext As String 

Papersizetext = "xlPaper" & Worksheets("Static").Range("B7").value 'A4 is the value in cell B7

shgenerate.PageSetup.PaperSize = Papersizetext 'not working 
shgenerate.PageSetup.PaperSize = "xlPaper" & Combobox1.value 'this also not working

shgenerate.PageSetup.PaperSize = xlPaperA4 'is working - i want above to work. 

'shgenerate is sheet name

End sub

`https://learn.microsoft.com/en-us/office/vba/api/excel.xlpapersize` - `xlPaperA4` is equivalent to 9. It's an enum member, not a string. You could write yourself a helper function to actually return `xlPaperA4` given the concatenated `xlPaper` and input `A4` - `Select Case` would be useful. — BigBen, Apr 13 '20 at 21:37
But how can i make it work. as i have list with page size name. — HeavyLoad Collection, Apr 13 '20 at 21:39
I got list of available sizes in combobox with help of code here https://stackoverflow.com/questions/61188638/how-get-list-of-available-page-size-in-combobox-and-change-size helped by @HTH — HeavyLoad Collection, Apr 13 '20 at 21:47
You could also use a multi-column combo and have those enum values in a hidden column. Then use those values in that hidden column as the input for your paper size setting. — SmileyFtW, Apr 13 '20 at 21:53

BigBen · Answer 1 · 2020-04-14T01:33:08.687

1

You could whip up your own function to parse the input and return the correct enum member:

Private Function PaperSize(ByVal rawSize As String) As Long
    Select Case rawSize
        Case "A4"
            PaperSize = xlPaperA4
        Case "A3"
            PaperSize = xlPaperA3
        Case "A5"
            PaperSize = xlPaperA5
        Case Else
            PaperSize = xlPaperUser ' or some other default 
    End Select
End Function

For more complex paper sizes, such as "Letter 8.5"x11" 22x28cm" or "Legal 8.5"x14" 22x36cm", you could possibly use InStr to test of the existence of "Letter" and "Legal" to return xlPaperLetter and xlPaperLegal.

Regex might be another approach to parse out the dimensions.

Without more detail though, it's hard to give a satisfactory answer, as this question is actually quite a broad topic.

edited Apr 14 '20 at 01:33

answered Apr 13 '20 at 21:46

BigBen

46,229
7
24
40

Thanks for your code But page sizes are dynamic. so can't be predefined.as above https://stackoverflow.com/questions/61188638/how-get-list-of-available-page-size-in-combobox-and-change-size that is why i want to change it with combox selection change. as combobox is updated with new sizes once printer adds new size. – HeavyLoad Collection Apr 13 '20 at 21:50
@HeavyLoadCollection - that's why you need a function to *parse* the combobox input. – BigBen Apr 13 '20 at 21:52
you might be correct. but what if after i install printer drivers and a new size with name "ABC" with width 8 inch and height 15 inch is created. how would i define the same in advance. – HeavyLoad Collection Apr 13 '20 at 21:57
`xlPaperUser` maybe? – BigBen Apr 13 '20 at 21:58
i ll take care of it. – HeavyLoad Collection Apr 13 '20 at 22:10
Can you please provide code if its possible to change size with combobox selection change. – HeavyLoad Collection Apr 13 '20 at 22:11
i am not getting details about xlPaperUser here https://learn.microsoft.com/en-us/office/vba/api/excel.xlpapersize – HeavyLoad Collection Apr 13 '20 at 22:17
Scroll all the way to the end. – BigBen Apr 13 '20 at 22:19
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/211585/discussion-between-heavyload-collection-and-bigben). – HeavyLoad Collection Apr 13 '20 at 22:23

what is wrong with this line of vba code for Excel Print

1 Answers1

Linked