Mimic PageSetup - Headers

[Jotoya]

Hi, Please

I would like for my users to be able to edit the headers in a sheet while working with a userform. So far, I've been able to setup the controls: A textbox for the left header, one for the center and another for the right header. A commandbutton will fire the code.

I have just used If...then..statements with little luck. Basically, how would I loop through each control (textbox) and, if it has a True value then update the header. If the textbox is empty then IGNORE it (here's where I failed) so as to NOT override any prior entries already in that particular header.

Thank you so much for any attempts and help.

 

[XL-Dennis]

Hi,

Below You find a sample which gives You a start. Name Your controls and add at the end of each name a running number.

Option Explicit

Private wbBook As Workbook

Private wsSheet As Worksheet

Private rnValues As Range, rnCell As Range

Private i As Long


Private Sub cmbClose_Click()

Set wbBook = ThisWorkbook

Set wsSheet = wbBook.Worksheets("Blad1")



With wsSheet

Set rnValues = .Range("B4:B7")

End With



For i = 1 To 4

With Me.Controls("CheckBox" & i)

rnValues(i, 1).Value = .Value

End With

Next i

End Sub



Private Sub UserForm_Initialize()

Set wbBook = ThisWorkbook

Set wsSheet = wbBook.Worksheets("Blad1")



With wsSheet

Set rnValues = .Range("B4:B7")

End With



For i = 1 To 4

With Me.Controls("CheckBox" & i)

.Value = rnValues(i, 1).Value

.Caption = rnValues(i, 1).Offset(0, -1).Value

End With

Next i

End Sub

 

[Tom Urtis]

Unless I'm missing the point of the question, this should do the job:

Private Sub CommandButton1_Click()
If Len(TextBox1.Text) > 0 Then ActiveSheet.PageSetup.LeftHeader = TextBox1.Text
If Len(TextBox2.Text) > 0 Then ActiveSheet.PageSetup.CenterHeader = TextBox2.Text
If Len(TextBox3.Text) > 0 Then ActiveSheet.PageSetup.RightHeader = TextBox3.Text
End Sub

 

[Jotoya]

Tom & XL-Dennis,

Gentleman, as always, a HUGE! thanks. Your talent and experience is MUCH appreciate it. Job done