[SOLVED] Create button to save cell data to workbook header

[tailz]

Hi,

I am trying to create a button that when clicked will save the data from a cell into the center section of the header for all sheets in a workbook. The cell will contain a customer name and that is what I need in the header to be printed out with every sheet within a workbook.

Any help would be appreciated.

Thanks

Tony

[JOHN H. DAVIS]

Where will the cell be located. This assumes the cell is in Range("A1") Sheets("Sheet1")

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveSheet.PageSetup.CenterHeader = Sheets("Sheet1").Range("A1").Value
End Sub

Or do you want something like this?

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ws.PageSetup.CenterHeader = Selection.Value
Next ws
End Sub

[tailz]

Hi John,

I don't seem to be able to use either code within the

Sub Button1_Click()
End Sub

Coding, any suggestions?

[JOHN H. DAVIS]

The second suggestion worked for me?

Private Sub CommandButton1_Click()
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ws.PageSetup.CenterHeader = Selection.Value
Next ws
End Sub

You have to make a selection before clicking.

[tailz]

Hi John,

Still no joy, I created a new button and input the following,

Private Sub CommandButton3_Click()
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ws.PageSetup.CenterHeader = Selection.Value
Next ws
End Sub

I made a cell selection and clicked on the Button3 but got the following error message,

"Cannot run the macro "Machine Reportstest.xlsm'!Customer_Info_Button3_Click'. The macro may not be available in this workbook or all macros may be disabled."

Macros are definitely enabled as my other buttons work ok.

Any ideas?

Cheers

Tony

The second suggestion worked for me?

Private Sub CommandButton1_Click()
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ws.PageSetup.CenterHeader = Selection.Value
Next ws
End Sub

You have to make a selection before clicking.

[JOHN H. DAVIS]

Can you attach a sample workbook? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[tailz]

Can you attach a sample workbook? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Hi John,

Workbook attached, I have removed the button, which I had placed alongside the entry cell for the customer name and also the vba module.

Cheers

Tony

[tailz]

Hi John,

I have no idea what is going on, still the same message as before, all other buttons work fine. Very odd.

[JOHN H. DAVIS]

I tested it on the file you attached? It worked fine. Maybe someone else may have some ideas?

[tailz]

Hi John,

I have attached the workbook again, this time with button and code, can you see if it works on your system please?

Regards

Tony

[tailz]

I sorted out the error as being previousaly experienced however I am now getting a mismatch error on

x = Selection.Value

I am using a text string in the cell,

dim x As String

should be correct, so any ideas why the "Runtime error 13, type mismatch"?

[tailz]

Hi,

Is anyone able to offer any help with this issue? I am struggling to get a button to do this task. I am open to alternate methods of adding this cell data into the workbook.

Many thanks

Tony

[JOHN H. DAVIS]

Machine Reportstest.xlsm

Tony:

I added a command button to Column Q. If you select a cell and click it you'll see that it addeds a Center Header based on the selection to all worksheets. Isn't that what you're trying to achieve?

[tailz]

Hi John,

Yes I eventually figured out where I was going wrong, so it is all sorted now. Many thanks

[JOHN H. DAVIS]

Took us awhile, but we got there. Glad to hear you've got a working solution.