How get list of available page size in combobox and change size

Question

i am trying to get list of all available page sizes in combobox as dropdown with vba. when user select the size, i need to change the worksheet size accordingly. also in another combobox i need to populate width and height of that paper size when page size in combobox is selected.

I tried something like.

Dim i As integer
for i = 1 to 30
activesheet.combobox1.value = Activesheet.PageSetup.Papersize(i)
next i

Thanks

You might find this question helpful for correct syntax along with some methods to populate combobox values: [How to add items to a combobox in a form in excel VBA?](https://stackoverflow.com/questions/17946317/how-to-add-items-to-a-combobox-in-a-form-in-excel-vba) — Samuel Everson, Apr 13 '20 at 13:49

HTH · Accepted Answer · 2020-04-13T14:50:34.097

1

here's a solution based on This link

ActiveSheet.ComboBox1.List = GetPaperSizes

where GetPaperSizes is the following function you must place in a standard module:

Option Explicit

'Written: June 14, 2010
'Author:  Leith Ross
'Summary: Lists the supported paper sizes for the default printer in a message box.

Private Const DC_PAPERNAMES = &H10

Private Declare Function DeviceCapabilities _
  Lib "winspool.drv" _
    Alias "DeviceCapabilitiesA" _
      (ByVal lpDeviceName As String, _
       ByVal lpPort As String, _
       ByVal iIndex As Long, _
       ByRef lpOutput As Any, _
       ByRef lpDevMode As Any) _
    As Long

Private Declare Function StrLen _
  Lib "kernel32.dll" _
    Alias "lstrlenA" _
      (ByVal lpString As String) _
    As Long

Function GetPaperSizes() As Variant

    Dim AllNames As String
    Dim I As Long
    Dim Msg As String
    Dim PD As Variant
    Dim Ret As Long
    Dim papersizes() As Byte
    Dim PaperSize As String

    'Retrieve the number of available paper names
    PD = Split(Application.ActivePrinter, " on ")'<<<== change "on" with its local Language translation from english
    Ret = DeviceCapabilities(PD(0), PD(1), DC_PAPERNAMES, ByVal 0&, ByVal 0&)

    'resize the array
    ReDim papersizes(0 To Ret * 64) As Byte

    'retrieve all the available paper names
    Call DeviceCapabilities(PD(0), PD(1), DC_PAPERNAMES, papersizes(0), ByVal 0&)

    'convert the retrieved byte array to an ANSI string
    AllNames = StrConv(papersizes, vbUnicode)

     'loop through the string and search for the names of the papers
    For I = 1 To Len(AllNames) Step 64
        PaperSize = Mid(AllNames, I, 64)
        PaperSize = Left(PaperSize, StrLen(PaperSize))
        If PaperSize <> vbNullString Then Msg = Msg & PaperSize & vbCrLf
    Next I

    GetPaperSizes = Split(Left(Msg, Len(Msg) - 2), vbCrLf)

End Function

edited Apr 13 '20 at 14:50

answered Apr 13 '20 at 14:29

HTH

2,031
1
4
10

Thanks for valuable answer. it working great and sizes are loaded in combobox and with this my first task is accomplished.. I also want to load height and width of selected size in another comboxes i.e combobox2 and combobox 3 respectively... Also will it load custom sizes if its created by printer drivers ?. if not can you please help me that. thanks. – HeavyLoad Collection Apr 13 '20 at 17:44
https://learn.microsoft.com/en-us/office/vba/access/concepts/printing/programmatically-retrieve-printer-capabilities – HeavyLoad Collection Apr 13 '20 at 17:56
You are welcome. If my answer solved your question, please mark it as accepted so as to direct future readers, too… As for a new issue, you should post a new question. Thanks – HTH Apr 13 '20 at 19:46
Surely i ll mark it solved as it solved my issue. but in my question it solved part of problem. can you please help me with rest. – HeavyLoad Collection Apr 13 '20 at 21:09
i am changing page size as follows but not working Dim Papersizetext As String Papersizetext = "xlPaper" & Worksheets("Static").Range("B7").value 'A4 shgenerate.PageSetup.PaperSize = Papersizetext 'is not working but shgenerate.PageSetup.PaperSize = xlPaperA4 'is working - whats wrong – HeavyLoad Collection Apr 13 '20 at 21:12
The thing is that [`XlPaperSize`](https://learn.microsoft.com/en-us/office/vba/api/excel.xlpapersize) enumeration values are integers (e.g.: you "see" `xlPaperA4` but you get `9`) , while `"xlPaper" & Worksheets("Static").Range("B7").value` would return a `String` value. – HTH Apr 14 '20 at 14:16
You are right @HTH please help me with thread here https://stackoverflow.com/questions/61197210/what-is-wrong-with-this-line-of-vba-code-for-excel-print. to change size with combobox selection change – HeavyLoad Collection Apr 14 '20 at 14:49

How get list of available page size in combobox and change size

1 Answers1

Linked