Error when assigning a macro to a button on excel with

Question

I'm trying to use a macro that is called with a button. I followed this answer to someone was looking to make the same thing : https://stackoverflow.com/a/52882368/20824840

But I have an error when running it (in french: enter image description here It would translate to: "Not able to run the macro 'compilateur.xlsm!'Browse 6, 3''. It is possible that the macro isn't available in this workbook or that all macros are deactivated/disabled"

What I did was the following : Right click on picture --> assign macro --> manualy written the macro's name and added the arguments: enter image description here

Here is the code behing :

Public Sub Browse(ByVal row As Integer, ByVal column As Integer) ' Procédure parcourir
    
        ' Décalartion des workbooks et worksheets
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(1)
        ' Déclaration de la variable contenant l'objet FileDialog (nécessaire pour ouvrir un fenêtre)
    Dim DialogBox As FileDialog
    Set DialogBox = Application.FileDialog(msoFileDialogOpen)
    Dim InputFileName As String: InputFileName = "erreur"
    
        ' Ajout des paramètres de séléction
    DialogBox.AllowMultiSelect = False
    DialogBox.Title = "Séléctionnez un fichier"
    DialogBox.InitialFileName = Application.ThisWorkbook.path
    DialogBox.Filters.Clear
        
        ' Ajout de filtres d'extension en fonction du type de fichier
    Dim vp As Boolean: vp = False
    Select Case row
        Case 6 To 8, 13 To 15, 20 To 22, 27 To 29
            DialogBox.Filters.Add "Composant", "*.xlsx; *.xlsm; *.xml", 1
        Case 9, 16, 23, 30
            DialogBox.Filters.Add "Fichier", "*.xlsx; *.xlsm", 1: vp = True
        Case Else
            MsgBox "Erreur"
            GoTo Endsub
    End Select
    
        ' Ouverture de la fenêtre et écriture du chemin
    If DialogBox.Show = -1 Then
        InputFileName = DialogBox.SelectedItems(1)
        ws.Cells(row, column).Value = InputFileName
    End If

    If vp Then
        SplittedInputFileName = Split(InputFileName, "\")
        Dim ExactNamefile As String: ExactNamefile = Split(SplittedInputFileName(UBound(SplittedInputFileName)), ".")(0)
        ws.Cells(row + 1, column).Value = ExactNamefile & ".xml"
    End If
Endsub:
End Sub

Basicaly it is a code that opens a window from which I can pick a file and it input the full path in the cell with the argument row and column who sould be inputed in the button : enter image description here

I tried running the code with another method, with this code:

Private Sub TestBrowse()
    Browse 9, 3
End Sub

And it runned perfectly fine

I also tried to manualy assign the TestBrowse method (which is private) like so :enter image description here And it worked fine as well.

One more thing is that I use two seperate modules that doesn't interact with each other.

Answer 1

Now I understand your problem: your macro name is the same as your module name. If you really wish to do this, then you have to call your macro thus:

compilateur.xlsm!'Browse.Browse 6, 3'

The alternative is to use different names.

The reason for this behaviour, is that Excel ranks module above macro. So that if there is a module called Browse, and you do nothing to qualify it, then Excel assumes you are referring to the module. Calling Browse.Browse says, call the macro called Browse in the module called Browse.

Leaving out the module name is fine as long as there can be no confusion.