I am confused about a macro I have built.

[Marq]

using 365

I have a work book that I use to track progress at work. I have a macro built in it where I populate about 10 cells on one sheet, then click a macro button and the data I populated in the 10 cells automatically populates my headers (left, center and right).

So I will open up the workbook which is titled "Blank Progress"...I will load the data in the cells, click the button anticipating the headers populate and then id save it as the new jobs name. (I automated my headers because I have to put the same headers on multiple sheets and got tired of manually doing it)

Last week I edited the macro in VB to include some new header info I need to start seeing and ever since then when I click the button, another workbook that's already populated for a job opens up and only then will the automated header button work in the Blank Progress.

ALSO....when I open the blank progress work book and go to view the macro, it isn't showing up..i got to View/Macros/ and its chosen on "This Work Book" and its blank...there are no macros to view.

The only time I can see the macro in the View/Macros pop up window is if that other work book that somehow opens up whenever I click the button on the Blank progress work book.

I hope I did not confuse anyone with my description but I could use some help figuring out how to fix this.

 

[MARK858]

I think it would help if you showed us the code

 

[Marq]

I think it would help if you showed us the code

oops..haha..my bad.

This is what I have in the module. I have since edited some things in it hoping to fix it.

Sub WrkshtHeader()
Dim Headers As Worksheet
Dim Master As Worksheet
Dim CoatingsProgress As Worksheet
Dim InsulationProgress As Worksheet

Dim JobDesc As Range
Dim WO As Range
Dim PO As Range
Dim EstAsLead As Range
Dim LeadResults As Range
Dim Asbestos As Range
Dim SurfacePrepOPS As Range
Dim SurfacePrepPO As Range
Dim PaintTSAOPS As Range
Dim PaintTSAPO As Range
Dim EnclosePCOPS As Range
Dim EnclosePCPO As Range
Dim RemoveInsINSOPS As Range
Dim RemoveInsINSPO As Range
Dim InstallInsINSPO As Range
Dim InstallInsINSOPS As Range
Dim RemoveEnclosurePCOPS As Range
Dim RemoveEnclosurePCPO As Range
Dim CleanUPOPS As Range
Dim CleanUPPO As Range
Dim FCOOPS As Range
Dim FCOPO As Range


Set Headers = Worksheets("Headers")
Set Ins = Worksheets("Insulation Progress")
Set Coat = Worksheets("Coatings Progress")

Set JobDesc = Headers.Range("B2")
Set WO = Headers.Range("B3")
Set PO = Headers.Range("B4")
Set EstAsLead = Headers.Range("B5")
Set LeadResults = Headers.Range("B6")
Set Asbestos = Headers.Range("B7")
Set InstallEnclosePCOPS = Headers.Range("B8")
Set RemoveInsulationOPS = Headers.Range("B9")
Set SurfacePrepOPS = Headers.Range("B10")
Set PaintTSAOPS = Headers.Range("B11")
Set InstallInsulationOPS = Headers.Range("B12")
Set RemoveEnclosePCOPS = Headers.Range("B13")
Set CleanUPOPS = Headers.Range("B14")
Set FCOOPS = Headers.Range("B15")



On Error GoTo errExit
Application.ScreenUpdating = False
Application.EnableEvents = False

Ins.Activate
With Ins.PageSetup
.LeftHeader = "&""Arial""&16OPS-PO" & Chr(10) & "&14Enclose/PC:" & InstallEnclosePCOPS & Chr(10) & "&14RemoveINS: " & RemoveInsulationOPS & Chr(10) & "&14InstallINS: " & InstallInsulationOPS & Chr(10) & "&14RemoveEnclose/PC: " & RemoveEnclosePCOPS & Chr(10) & "&14FCO: " & FCOOPS & Chr(10) & "&14ASBESTOS? " & Asbestos & Chr(10)
.CenterHeader = "&""Arial,Bold""&24" & JobDesc & Chr(10) & "&16Insulation Progress" & Chr(10) & "&14Work Order # " & WO & Chr(10) & "&14PO# " & PO
.RightHeader = "&""Arial,Bold""&21&D" & Chr(10) & Format(Weekday(Date), "dddd")
End With

Coat.Activate
With Coat.PageSetup
.LeftHeader = "&""Arial""&16OPS-PO" & Chr(10) & "&14SurfacePrep: " & SurfacePrepOPS & Chr(10) & "&14Paint/TSA: " & PaintTSAOPS & Chr(10) & "&14CleanUp: " & CleanUPOPS & Chr(10) & "&14FCO: " & FCOOPS & Chr(10) & "&14Estimated as LEAD? " & EstAsLead & Chr(10) & "&14LEAD Results: " & LeadResults & Chr(10)
.CenterHeader = "&""Arial,Bold""&24" & JobDesc & Chr(10) & "&16Coatings Progress" & Chr(10) & "&14WO# " & WO & Chr(10) & "&14PO# " & PO
.RightHeader = "&""Arial,Bold""&21&D" & Chr(10) & Format(Weekday(Date), "dddd")
End With

Master.Activate
With Master.PageSetup
.CenterHeader = "&""Arial,Bold""&24" & JobDesc & Chr(10)
    End With

  

  
errExit:

Headers.Activate

MsgBox "Headers Update Completed!"

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[MARK858]

I can't see anything in the code that opens another workbook but you have Application.EnableEvents = False in the code. Do you have any worksheet or workbook event code that you haven't posted? also do you have any links in the workbooks?

As for the code not showing in the dialog box can you open a blank workbook and with all the workbooks open run the code below (apologies I can't remember who wrote the original code) and post the result using XL2BB. Make sure that you follow the instruction in green.

Sub List_OpenWb_Macros()
    Dim VBComp As VBComponent, cMod As CodeModule, LSht As Worksheet
    Dim sLine As Long, pName As String, iCnt As Long, wb As Workbook

' Make sure that you set a reference to Microsoft Visual Basic for Applications Extensibility
' Tools menu - References - Microsoft Visual Basic for Applications Extensibility 5 _ Tick Checkbox

    Application.ScreenUpdating = False
    On Error Resume Next
    
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MacroSheet"
    Set LSht = Worksheets("MacroSheet")
    iCnt = 1
    LSht.[a1] = "Macro List"
    
    For Each wb In Application.Workbooks
        For Each VBComp In wb.VBProject.VBComponents
            Set cMod = wb.VBProject.VBComponents(VBComp.Name).CodeModule
            With cMod
                sLine = .CountOfDeclarationLines + 1
                Do Until sLine >= .CountOfLines
                    LSht.[a1].Offset(iCnt, 0).Value = _
                                                            wb.Name & "_" & cMod.Name & "_" & .ProcOfLine(sLine, vbext_pk_Proc)
                    iCnt = iCnt + 1

                    sLine = sLine + _
                                .ProcCountLines(.ProcOfLine(sLine, _
                                                            vbext_pk_Proc), vbext_pk_Proc)
                Loop
            End With
            Set cMod = Nothing
        Next VBComp
    Next wb
    
    Worksheets("MacroSheet").Columns(1).AutoFit
    Application.ScreenUpdating = True
End Sub

 

[Marq]

I can't see anything in the code that opens another workbook but you have Application.EnableEvents = False in the code. Do you have any worksheet or workbook event code that you haven't posted? also do you have any links in the workbooks?

As for the code not showing in the dialog box can you open a blank workbook and with all the workbooks open run the code below (apologies I can't remember who wrote the original code) and post the result using XL2BB. Make sure that you follow the instruction in green.

Sub List_OpenWb_Macros()
    Dim VBComp As VBComponent, cMod As CodeModule, LSht As Worksheet
    Dim sLine As Long, pName As String, iCnt As Long, wb As Workbook

' Make sure that you set a reference to Microsoft Visual Basic for Applications Extensibility
' Tools menu - References - Microsoft Visual Basic for Applications Extensibility 5 _ Tick Checkbox

    Application.ScreenUpdating = False
    On Error Resume Next
  
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MacroSheet"
    Set LSht = Worksheets("MacroSheet")
    iCnt = 1
    LSht.[a1] = "Macro List"
  
    For Each wb In Application.Workbooks
        For Each VBComp In wb.VBProject.VBComponents
            Set cMod = wb.VBProject.VBComponents(VBComp.Name).CodeModule
            With cMod
                sLine = .CountOfDeclarationLines + 1
                Do Until sLine >= .CountOfLines
                    LSht.[a1].Offset(iCnt, 0).Value = _
                                                            wb.Name & "_" & cMod.Name & "_" & .ProcOfLine(sLine, vbext_pk_Proc)
                    iCnt = iCnt + 1

                    sLine = sLine + _
                                .ProcCountLines(.ProcOfLine(sLine, _
                                                            vbext_pk_Proc), vbext_pk_Proc)
                Loop
            End With
            Set cMod = Nothing
        Next VBComp
    Next wb
  
    Worksheets("MacroSheet").Columns(1).AutoFit
    Application.ScreenUpdating = True
End Sub

to answer your first question: "Do you have any worksheet or workbook event code that you haven't posted? also do you have any links in the workbooks?"

No I do not. The only code is what you see. The code was not as long as you see now. About a week ago I did not need that many line items in the left header section and what I had previously worked like a charm but my job changed the game on me and I had to add some additional lines of code to accommodate the new request from the field guys. I just did some copy and pasting within the code and changed the words in the newly copied code lines....I figured I couldn't do any wrong and it would work like a charm.

And what does Application.EnableEvents = False in the code represent..meaning what is it doing? Should it even be there? Should it be True?

 

[Marq]

I followed the steps u listed and got this.

I opened all the workbooks that currently have the macro....then opened a new blank workbook..right click on tab to view code....copied and pasted the code you directed me to. Closed visual basic.....then viewed macros....chose the macro and clicked run and what u see in the pic is what showed up.

 

[MARK858]

Make sure that you follow the instruction in green.

 

[Marq]

I am leaving for work now..I will try this first thing tomorrow morning and report back (im central time USA)

 

[Marq]

Its the next day

I have followed the steps..I chose Microsoft Visual Basic for Applications Extensibility 5.3...that's the only option I had.

I also choose "all open workbooks"

My excel has been locked up ("excel not responding") from the second I clicked to run the macro....going on 10 minutes now.

Should I have choosen "this work book" instead of all open work books? I will try again choosing this work book.

 

[Marq]

I've tried it with this work book option and again, excel locked up.