How change Pagesetup property in the For...Each...Next?

[backup69]

Hi all

I have Form with 6 ComboBox:
LeftHeader ; CenterHeader ; RightHeader ; LeftFooter .........

Now i try minimize my code and loop trough the Combobox names and values like this.

For Each ws In Worksheets
    For Each a In Controls
    If TypeName(a) = "ComboBox" Then
    Select Case a.Value
    Case "Page"

        ws.PageSetup.a.Name = "Page &P"
    
    Case "Page of Pages"

        ws.PageSetup.a.Name = "Page &P of &N"
........

I know (ws.PageSetup.a.Name) this part is wrong. But how i can use ComboBox names for the Pagesetup Header and Footer property?

 

[erik.van.geit]

Hi,

not sure if this will work
see the helpfiles for CallByName

kind regards,
Erik

 

[erik.van.geit]

after some testing

this works for me

Dim sh As Worksheet
Dim ctrl As MSForms.Control

Set sh = ActiveSheet
'testline
sh.PageSetup.CenterFooter = "anytext"

    For Each ctrl In Me.Controls
    MsgBox CallByName(sh.PageSetup, ctrl.Name, VbGet)
    Next ctrl

so no problem to "get" it
now searching to "set" it

best regards,
Erik

 

[erik.van.geit]

FOUND IT

Dim sh As Worksheet
Dim ctrl As Control
Dim txt As String

Set sh = ActiveSheet

    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "ComboBox" Then
        CallByName sh.PageSetup, ctrl.Name, VbLet, ctrl.Value
        End If
    Next ctrl

 

[backup69]

Ty

This is exactly what i need

 

[erik.van.geit]

FINE

I hope you checked the helpfiles to learn something, anyway I did as you can see in the process above