I am confused about a macro I have built.

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,007
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I think it would help if you showed us the code ;)
 

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,007
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.

VBA Code:
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

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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.
 

Attachments

  • vba pic.PNG
    vba pic.PNG
    49.8 KB · Views: 3

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
I am leaving for work now..I will try this first thing tomorrow morning and report back (im central time USA)
 

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
I've tried it with this work book option and again, excel locked up.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top