Missing Macros

[gheyman]

I have a workbook that was working when I created it yesterday. Today when I try to open it, all the buttons/objects no longer have the Macros assigned to them. When I go to Assign the Macro's they are not listed. But if I go into my VBA project explorer they are all there. Can someone explain what's happening? I did save it correctly (Excel Macro-Enabled Workboob xlsm) and my Macros are NOT disabled.

Thanks for your time and help.

 

[Joe4]

Three things would make them not appear in the list of macros you can assign to buttons:
1. If they are Functions instead of Procedures
2. If they have the word "Private" in front of "Sub"
3. If they have parameters

If you cannot figure it out, please post a VBA procedure that is not showing up, but should be.

 

[gheyman]

Three things would make them not appear in the list of macros you can assign to buttons:
1. If they are Functions instead of Procedures
2. If they have the word "Private" in front of "Sub"
3. If they have parameters

If you cannot figure it out, please post a VBA procedure that is not showing up, but should be.

Thanks

Sub CopyProjectNumbers()
'*************G Heyman

    Application.ScreenUpdating = False
    
    Dim ws As Worksheet
    Dim pt As PivotTable
    
    
    ' Specify the worksheet containing the PivotTable
    Set ws = ThisWorkbook.Sheets("Proj Actuals")
    
    ' Specify the PivotTable by name
    Set pt = ws.PivotTables("PivotTableProjAct")
    
    Sheets("Data_Fields").Visible = True
    
    
        ' Copying values from "Est. Method" sheet cell F5
        Sheets("Est. Method").Range("F5").Copy

        ' Pasting special values to "Data_Fields" sheet cell B6. This is the UD_ProjectNumbers table that is used to filter data on load.
        Sheets("Data_Fields").Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        ' Clear the clipboard
        Application.CutCopyMode = False
    
    
    Sheets("Data_Fields").Visible = xlSheetVeryHidden
    
    If Sheets("Est. Method").Range("F5").Value <> "" Then
    
        Sheets("ProjectAct").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    
        ' Refresh the PivotTable
            pt.RefreshTable
    
        Sheets("ProjectAct").Range("A4").Value = "Last Refreshed on: " & Now
        Sheet15.Select
        Sheet15.Range("N4").Value = "Proj Actuals: " & Now
        
    Else
    MsgBox "You must insert Projct ID(s)"
    End If
   
   Application.ScreenUpdating = True
   Sheet15.Select
    
End Sub

 

[gheyman]

Sub CopyTargetPartNumbers()
'G Heyman

On Error Resume Next

    Application.ScreenUpdating = False
    
    Dim ws1 As Worksheet
    'Dim pt As PivotTable
    
    
    ' Specify the worksheet containing the PivotTable
    'Set ws1 = ThisWorkbook.Sheets("Target Cost")
    
    ' Specify the PivotTable by name
    'Set pt = ws.PivotTables("PivotTableProjAct")
    
    Sheets("Data_Fields").Visible = True
    
    ' Copying values from "Est. Method" sheet cell F19
    Sheets("Est. Method").Range("F19").Copy

        ' Pasting special values to "Data_Fields" sheet cell J6. This is the UD_PartsTargetCost table that is used to filter data on load.
        Sheets("Data_Fields").Range("J7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        ' Clear the clipboard
        Application.CutCopyMode = False
        
    Sheets("Data_Fields").Visible = xlSheetVeryHidden
    
    ' Refresh the PivotTable
    'pt.RefreshTable
    
    If Sheets("Est. Method").Range("F19").Value <> "" Then
    
    Sheets("Target Cost").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    
    Sheets("Target Cost").Range("A11").Value = "Last Refreshed on: " & Now
    Sheet15.Select
    Sheet15.Range("N19").Value = "Target Cost: " & Now
    
    Else
        MsgBox "You must enter Part IDs"
    End If
    
On Error GoTo 0

Application.ScreenUpdating = True

    'Sheets("Est. Method").Range("F19").Activate
    Sheet15.Select
    
End Sub

 

[Joe4]

OK, that looks like this should be visible.
Where exactly have you stored these procedures?
What is the name of the module they reside in?

Also, when looking to assign your macros, make sure you have your settings set to an option where you can see them:

 

[gheyman]

Joe, When I removed the "On Error Resume Next" and "On Error GoTo 0" from the code everything appeared back under the Macro Names and I was able to reassign them to my objects. Removing that part of the code was a complete guess to me, but for some reason it worked. Is that part of the code considered a Function? I was trying to include an error handler by adding it to the original code.

 

[Joe4]

Joe, When I removed the "On Error Resume Next" and "On Error GoTo 0" from the code everything appeared back under the Macro Names and I was able to reassign them to my objects. Removing that part of the code was a complete guess to me, but for some reason it worked. Is that part of the code considered a Function? I was trying to include an error handler by adding it to the original code.

That is bizarre. No, I have never heard that before or had that issue.
I don't know if there is any correlation between the two, or just coincidental.

 

[RoryA]

There have been lots of similar reports of this (and/or nonsensical code errors) kind of thing with 365. It usually seems that any sort of change to the code that forces a recompile will clear out the problem, at least for a while. It may also be worth making the registry change that Charles mentions here: VBA Corruption Registry Fix

 

[Joe4]

There have been lots of similar reports of this (and/or nonsensical code errors) kind of thing with 365. It usually seems that any sort of change to the code that forces a recompile will clear out the problem, at least for a while. It may also be worth making the registry change that Charles mentions here: VBA Corruption Registry Fix

That is interesting Rory, and makes sense.
I know I have lots of code that has error handling that I have never had that kind of issue with, so didn't think it was related to that.
Thanks for the info!

 

[gheyman]

Thank you both Joe and Rory! Much appreciated!