I have made this code where from a modeless user form a user can control a machine using VISA, take data from it and immediately graph it on a fresh workbook. The user should also be able to create as many workbooks as they please placing different data on the workbook they have selected. My issue is performing pWb.SaveAs on a workbook in the clsWorkbook class that was created in clsWorkbookManager which passed the newly added/created workbook reference into the initializer of clsWorkbook and into the pWb variable. The SaveAs function of the workbook does not work in the button click section as expected, I want it to work there. I am running 64-bit windows 10, MSO 64-bit Excel windows 10.

The workbook which has the code will not contain any graphs, it just holds the macros.

This is the workbookManager class

Private pCurrentWb As clsWorkbook 

Public Sub Init()
    Set pCurrentWb = Nothing 
End sub 

Public Property Get CurrentWb() as clsWorkbook 
    Set CurrentWb = pCurrentWb 
End Property 

Public function AddNewWb() as clsWorkbook 

    Dim newWorkbook as Workbook 
    Set newWorkbook = Workbooks.add() 
    Dim newWb as clsWorkbook 
    Set newWb = New clsWorkbook 

    NewWb.unit newWorkbook 
    Set pCurrentWb = newWb 
End function 

ClsWorkbook class

Private WithEvents pWb as Workbook

Public sub init(ByRef tWb) 
    Set pWb = tWb 
End sub 

Public function save() as string 
    Dim filePath as Variant 
    Dim strFilePath as string 

    If pWb.Path = “” then 
        filePath = Application.GetSaveAsFilename(“xlsx filter”) 
        strFilePath = filePath 

        pWb.SaveAs strFilePath, 51, createBackup:=False
    Else 
        pWb.save 
    End if 
End function 

I’m calling these functions like this inside my userform

Private sub cmdSave_Click()
    ‘workbookManager.CurrentWb.wb.SaveAs “C:…foobar.xlsx”, 51, CreateBackup:= False
    WorkbookManager.CurrentWb.save
End sub
Private sub Userform_Initialize()
    If workbookManager.CurrentWb Is Nothing Then
        WorkbookManager.AddNewWb
        ‘WorkbookManager.CurrentWb.save <- works fine here 
    End If
End Sub

My main module where the whole code starts looks like this

Public workbookManager As clsWorkbookManager

Public sub Auto_Open()
    Set workbookManager = New clsWorkbookManager
    WorkbookManager.Initialize

    Dim temp As Userform1
    Set temp = New Userform1
    Temp.show vbmodeless ‘SaveAs works in the button when this is vbmodal. 
End sub

I’ve tried activating pWb before I save, I tried all the different enumerations of file format, I tried the variant, I tried saving without the .xlsx, I performed Dir() on the directory to ensure it’s fine, my file names I enter are usually “blah” then I get as …blah.xlsx, I tried doing SaveAs right after I do workbook.add() and it works like a charm, using debug I ensure that the name of the workbook in the watch is the same as the workbook I just created. The reason I’m not SaveAs-ing immediately is because the user will not want to save an empty workbook. I tried activating the current workbook in workbookManager and then SaveAs the ActiveWorkbook in the userform it SaveAs-ed the workbook with all the macros in it. If I run this program without any breakpoints, I get the runtime error 1004 Method save as of object workbook failed. But if I put a break point on the if statement and then step through it, the SaveAs works sometimes. I’ve also tried DoEvents and sleeping for 10 seconds before the if to no avail. I’ve also tried calling a function from my main module with just a SaveAs in it but still didn’t work. I believe the issue lies with the userform being modeless as the SaveAs does work in the initializor of the userform but doesn’t work in the button click section. However I do need the user to be able to interact with the workbook so vbmodal is out the picture. I feel like one should be able to SaveAs a reference to a workbook at anytime. Does anyone have any way to fix this or a different approach?

Thank you!