first VBA question

[novski]

Hi
Im starting with VBA and wold like to add a Dropdown to my macro.

I found this useful code on John Goldsmiths blog.

but i wold like to choose the "PaperKind" from a drop down. how can i do that?

Sub ChangeAllPageSizes()
Dim refPag As Page
Dim pag As Page
Dim SkipBackgrounds As Boolean

    'Set your reference page
    Set refPag = ActivePage
   
    'Choose to apply changes to background pages
    If MsgBox("Apply changes to " & _
                        "background pages?", _
                        vbYesNo) = vbNo Then
        SkipBackgrounds = True
    End If
    MsgBox
   
    'Run through each page in document
    For Each pag In ThisDocument.Pages
        If pag.ID <> refPag.ID Then
            If Not (pag.Background And SkipBackgrounds) Then
                pag.PageSheet.CellsU("PageWidth").FormulaU = _
                    refPag.PageSheet.CellsU("PageWidth").FormulaU
                pag.PageSheet.CellsU("PageHeight").FormulaU = _
                    refPag.PageSheet.CellsU("PageHeight").FormulaU
                pag.PageSheet.CellsU("PaperKind").FormulaU = _
                    refPag.PageSheet.CellsU("PaperKind").FormulaU
            End If
        End If
    Next pag
End Sub

Thanks for help.
Novksi

[Surrogate]

This macro just set Paperkind like as at selected page to all pages of document.
For create dropdown list you must add user-form and some controls (comboboxes and button)

[Surrogate]

i quickly create this file with same macro

[novski]

Hi Surrogate, Thank you so mutch for that help. I now understand a bit more how the UserForm works.
I rewrote some parts so it wold match my prefered usecase a little more.

Private Sub cmdOK_Click()
For Each pag In ThisDocument.Pages
'        If pag.ID <> refPag.ID Then
            If Not (pag.Background And SkipBackgrounds) Then
                pag.PageSheet.CellsU("PaperKind").FormulaU = cmbPaperKind.Value
            End If
'        End If
    Next pag
UserForm1.Hide
End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Initialize()
With cmbPaperKind
.AddItem "A4 Vertiakal"
.AddItem "A4 Horizontal"
.AddItem "A3 Horizontal"
.AddItem "A2 Horizontal"
.AddItem "A1 Horizontal"
End With
End Sub

As you can see i don't whant to transmit the "A4 Vertikal" as a String. The Value for A4 PaperKind is "9" and vor the others:
A3 = "8"
A2 = "66"
A1 = "2058"

How can i make the form to transmitt those Values?

Best regards
novski

[Surrogate]

i think following code not better choice, because i don't know another ways. But it works

Code Select Expand

Private Sub cmdOK_Click()
Dim pk As Integer
Select Case cmbPaperKind.Value
Case "A4"
pk = 9
Case "A3"
pk = 8
Case "A2"
pk = 66
Case "A1"
pk = 123
End Select
For Each pag In ThisDocument.Pages
            If Not (pag.Background And SkipBackgrounds) Then
                pag.PageSheet.CellsU("PaperKind").FormulaU = pk
            End If
    Next pag
UserForm1.Hide
End Sub
Private Sub UserForm_Initialize()
With cmbPaperKind
.AddItem "A4"
.AddItem "A3"
.AddItem "A2"
.AddItem "A1"
End With
End Sub

[novski]

Wow. Yes that works fine!

if i whant to use that in diferent documents, is it possible to make it available addon-like or how can i make it accessible?

[Surrogate]

yes, you can make add-on but i don't know how
you can open file contained this code and another file.
Activate another file, press Alt+F8 and select your file with macros in field Macros in

select macro and press Run

[novski]

Thank you, Surrogate. I may have to work it in to my Templates.
I will come back if it doesn't work.

best regards
novski