VBA Code to Reference another Macro and Fill Dialog Box Based on Cell Entry

[OilEconomist]

Thanks in advance for any suggestions as I will provide feedback accordingly.

Is there a way to somehow call a macro MacroA in another macro called MacroB to where MacroA has the pop-up box filled out automatically based on a cell entry?

Synopsis:
I’m using a third party spreadsheet which you fill out and click a macro which checks the data. If everything is okay, it has a pop-up so you can direct it to where you want to save the file and give it a file name. The output file is a text file.

Without changing their macro (MacroA), I’m trying to call it into my macro (MacroB) and have it get the directory path from cell A1 and file name from cell B1.

I would like to see if I can just change MacroA, but first of all I can’t figure out what macro is assigned to that button. Second of all when I look at the code, it’s way to complicated.

The reason why I would like to call MacroA into B is so I can loop it through several sets of data (file names).

 

[Norie]

I don't see how you can do this without changing MacroA.

To find out which macro is assigned to a buttton right click the button and select either Assign Macro... or View Code, whichever appears in the menu

If it's the former then the Macro dialog box should open with the assigned macro highlighted in the list, to go to the code select Edit.

If it's the latter you should be taken directly to the code.

 

[OilEconomist]

When I try to right click on it, it won't let me do anything.

I think I was able to find the code where the following is the part I think that I would like to change. How would I modify it to where instead of a pop up box it for the path where to be saved and a file name, it would use cell M3 on sheet "Sheet1" for the file path and cell L3 on "Sheet1" for the out text file name?

' close the temporary file
    CloseTmpFile iFile


    If lErrCount > 0 Then
        Set frmMyErrorForm = New frmErrors
        frmMyErrorForm.SetNumberOfErrors lErrCount
        frmMyErrorForm.Show
        If frmMyErrorForm.bIgnore = True Then
            bSaveFile = True
        Else
            bSaveFile = False
        End If
        Set frmMyErrorForm = Nothing
    Else
        bSaveFile = True
    End If
    
    If lLastDataRow < k_FIRST_DATA_ROW Then
        MsgBox "No data entered!"
    ElseIf bSaveFile Then
        Set frmMySaveForm = New frmSaveFile
        frmMySaveForm.LoadText sTmpFileName
        frmMySaveForm.Show
        Set frmMySaveForm = Nothing
    End If
    
    ' delete the temporary file
    DeleteTmpFile sTmpFileName


    If lFirstErrorRow = 0 Then
        ' move the cursor back to the top of the page
        Cells(k_FIRST_DATA_ROW, 1).Select
    Else
        ' move the cursor to the row of the first error
        Cells(lFirstErrorRow, 1).Select
    End If


    ' undo the changes made to speed up the workbook
    WorkbookSlowDown True

 

[Norie]

Are you sure that code is displaying the pop-up?

Have you tried stepping through it with F8 to see which line of code is causing the pop-up to display?

 

[OilEconomist]

Yes I do think it's the code as the form name that I saw was in there. I will try to step through the Macro. Thanks!

Set frmMySaveForm = New frmSaveFile
        frmMySaveForm.LoadText sTmpFileName
        frmMySaveForm.Show
        Set frmMySaveForm = Nothing