Header Formatting vba only works with breakpoint or Step Through

[dswan388]

All,

Apologies ahead of time for the ugly code. I am a complete novice at this and have used inputs for forums like yourself to kludge together something that appears to work, except that I can't get it to run successfully when I push play. I've seen a lot of posts about only working when steeping through (F8) but none of the answers seemed to do it for me. I'm sure there is a golden rule I'm breaking that will make my code 1000x better, so any inputs you all have would be greatly appreciated.

So this code is used to apply a header to a swath of tabs in an Excel spreadsheet. Date and file name with a particular format. It runs successfully if I put a breakpoint around the end of it ( ActiveWorkbook.Sheets("TOC").Range("A2").Select) or if I step through using F8. Without it, the date doesn't get applied to the rest of the sheets (only the "TOC" sheet that I specified). Please let me know if anything stands out and thank you in advance!

'Variables
Dim fso As New Scripting.FileSystemObject

Sub ConfigSMPV()

Sheets("Title").Visible = False
Sheets("SystemConfig").Visible = False
Sheets("Sheet ii").Visible = False

Sheets("TOC").Activate

For Each VisableSheet In Sheets
With VisableSheet
If .Visible = True Then .Select Replace:=False
End With
'Debug.Print cntr
'cntr = cntr + 1
Next VisableSheet

'Application.PrintCommunication = False

With ActiveWorkbook.Sheets("TOC").PageSetup
.RightHeader = vbCr & fso.GetBaseName(ActiveWorkbook.Name)
End With

With ActiveWorkbook.Sheets("TOC").PageSetup
.LeftHeader = vbCr & Format(ActiveWorkbook.Sheets("SystemConfig").Range("C15").Value, "mmmm d, yyyy")
End With

ActiveWorkbook.Sheets("TOC").Range("A2").Select

'Application.Wait (Now + TimeValue("0:00:10"))

Sheets("Title").Visible = True
Sheets("SystemConfig").Visible = True
Sheets("SystemConfig").Activate

End Sub

 

[dswan388]

I'd like to add that I've tried putting a Wait command in a number of places throughout the macro and it didn't seem to help. I went anywhere from 1 second to 10 seconds. Only thing that is working right now is adding the breakpoint.

 

[dswan388]

Update: I replaced ActiveWorkbook.Sheets("TOC").Range("A2").Select

with a defined variable Dim clickoff As Range

Set clickoff = ActiveWorkbook.Sheets("TOC").Range("A2")

Still no luck...

 

[dswan388]

I've tried to clean things up a bit. Been reading about defining variables as being the preferred method over activating Activesheets, Activeworkbook, etc. Still the same issues.

'Variables
Dim fso As New Scripting.FileSystemObject

Sub ConfigSMPV()

'Variables

Dim wb As Workbook
Set wb = Application.Workbooks("SMPV-Template-DanWIP-Test.xlsm")

'Code

Sheets("Title").Visible = False
Sheets("SystemConfig").Visible = False
Sheets("Sheet ii").Visible = False

For Each VisableSheet In Sheets
With VisableSheet
If .Visible = True Then .Select Replace:=False
End With
Next VisableSheet

Application.PrintCommunication = False

Sheets("TOC").Activate

With ActiveWorkbook.Worksheets("TOC").PageSetup
.RightHeader = vbCr & fso.GetBaseName(ActiveWorkbook.Name)
.LeftHeader = vbCr & Format(ActiveWorkbook.Sheets("SystemConfig").Range("C15").Value, "mmmm d, yyyy")
End With

'Application.Wait (Now + TimeValue("0:00:01"))

Set clickoff = wb.Worksheets("TOC").Range("A2")

Sheets("Title").Visible = True
Sheets("SystemConfig").Visible = True
Sheets("SystemConfig").Activate

End Sub

 

[dswan388]

Ok, so the code seems to hinge on Application.PrintCommunication = False. If I comment it out, the code doesn't work with the breakpoint at Set clickoff = wb.Worksheets("TOC").Range("A2"), but if I leave it in there it works with the breakpoint. Is this a timing issue? I forgot to mention in my original post that there's a second half to this code that runs it again for an individual sheet and that's where the Application.PrintCommunication = True is.

'Variables
Dim fso As New Scripting.FileSystemObject

Sub ConfigSMPV()

'Variables

Dim wb As Workbook
Set wb = Application.Workbooks("SMPV-Template-DanWIP-Test.xlsm")

wb.Sheets("Title").Visible = False
wb.Sheets("SystemConfig").Visible = False
wb.Sheets("Sheet ii").Visible = False

For Each VisableSheet In Sheets
With VisableSheet
If .Visible = True Then .Select Replace:=False
End With
Next VisableSheet

Application.PrintCommunication = False

wb.Sheets("TOC").Activate

With wb.Worksheets("TOC").PageSetup
.RightHeader = vbCr & fso.GetBaseName(wb.Name)
.LeftHeader = vbCr & Format(wb.Sheets("SystemConfig").Range("C15").Value, "mmmm d, yyyy")
End With

'Application.Wait (Now + TimeValue("0:00:01"))

Set clickoff = wb.Worksheets("TOC").Range("A2")

wb.Sheets("Title").Visible = True
wb.Sheets("SystemConfig").Visible = True
wb.Sheets("SystemConfig").Activate

End Sub

 

[dswan388]

Ok, I think I might have figured it out, though I can't really explain why yet. Any help with an explanation would be much welcome.

I was able to simplify a lot of things by removing some of the Activates and Selects. Seems like just by having the tabs I'm interested in selected, when it applies the header it did to all the selected tabs without the need to select a cell in the active worksheet (like I would if I was doing it manually). The kicker seemed to be adding an Application.PrintCommunication = True. I put it toward the end of the code and all of if sudden things started working. I'm able to run my code off the button I created in my spreadsheet! I'll have to do some Googling about PrintCommunication to see exactly what it's doing. Feel like I learned a lot from going through this processes! Hope this helps someone down the line going through a similar step-through issue.

'Variables
Dim fso As New Scripting.FileSystemObject

Sub ConfigSMPV()

'Variables

Dim wb As Workbook
Set wb = Application.Workbooks("SMPV-Template-DanWIP-Test.xlsm")

wb.Sheets("Title").Visible = False
wb.Sheets("SystemConfig").Visible = False
wb.Sheets("Sheet ii").Visible = False

'Select Visable Sheets

For Each VisableSheet In Sheets
With VisableSheet
If .Visible = True Then .Select Replace:=False
End With
Next VisableSheet

'Application.Wait (Now + TimeValue("0:00:01"))
Application.PrintCommunication = False
'Application.Wait (Now + TimeValue("0:00:01"))

'wb.Sheets("TOC").Activate

'Apply header

With wb.Worksheets("TOC").PageSetup
.RightHeader = vbCr & fso.GetBaseName(wb.Name)
.LeftHeader = vbCr & Format(wb.Sheets("SystemConfig").Range("C15").Value, "mmmm d, yyyy")
End With

'Application.Wait (Now + TimeValue("0:00:01"))
'wb.Sheets("TOC").Activate
'Set clickoff = wb.Worksheets("TOC").Range("A2")

Application.PrintCommunication = True

wb.Sheets("Title").Visible = True
wb.Sheets("SystemConfig").Visible = True
wb.Sheets("SystemConfig").Activate

End Sub