Compile error : Named argument not found, when using Formula1:

[Richhodg]

I have created some vba code which i thought would work, but each time I run it I get a Compile error : Named argument not found, when using Formula1:

here is a copy of my code:

Sub Insert_Profile()

    ' Declare variables
    Dim MacroNames As Variant
    Dim ChosenMacro As Variant

    ' Define the list of macro names
    MacroNames = Array("X-Small", "Small", "Medium", "Large", "X-Large")

    ' Display the pop-up box with the macro names
    ChosenMacro = Application.InputBox(Prompt:="Please select Profile Size", Title:="Profile Size List", Type:=8, Formula1:=Join(MacroNames, ","))

   
    ' Run the selected macro
    Select Case ChosenMacro
        Case "X-Small"
            Workings_Insert_XS_Profile
        Case "Small"
            Workings_Insert_S_Profile
        Case "Medium"
            Workings_Insert_M_Profile
        Case "Large"
            Workings_Insert_L_Profile
        Case "X-Large"
            Workings_Insert_XL_Profile
        Case Else
            ' Do nothing if no macro is selected or the selected macro does not exist
    End Select

End Sub

If I don't add the argument in, it brings up the blank popup box, so any help would be appreciated.

 

[Joe4]

Welcome to the Board!

Where did you get "Formula1" from?
I do not see it as being one of the arguments for Application.InputBox: Application.InputBox method (Excel)

 

[Richhodg]

Welcome to the Board!

Where did you get "Formula1" from?
I do not see it as being one of the arguments for Application.InputBox: Application.InputBox method (Excel)

Thanks for the welcome, I picked it up off a openai program, it may be wrong, the whole point what I’m trying to do is to have a single macro that I run which brings up a selection box/list, depending on what I select it will run another macro.

 

[Joe4]

Thanks for the welcome, I picked it up off a openai program, it may be wrong

Yes, those are HIGHLY unreliable. So much so, that we have a rule about people posting solutions generated by ChatGPT without first testing (rule 17 here: Message Board Rules). That rule is directed more to responders, not questioners, so people don't post bad untested code that they got from one of these tools.

One way to do what you want would be to create a cell with Data Validation, that has a drop-down of your choices.
Then, you could have a "Worksheet_Change" event procedure automatically fire when the value in that cell is changed.
Does that sound like it might work for you?

 

[DanteAmor]

Or simply:

Sub Insert_Profile()
  ' Declare variables
  Dim ChosenMacro As Variant
  
  ' Display the pop-up box with the macro names
  ChosenMacro = Application.InputBox("Please select Profile Size:" & vbCr & _
    "X-Small, Small, Medium, Large, X-Large", "Profile Size List")
  
  
  ' Run the selected macro
  Select Case ChosenMacro
  Case "X-Small"
    Workings_Insert_XS_Profile
  Case "Small"
    Workings_Insert_S_Profile
  Case "Medium"
    Workings_Insert_M_Profile
  Case "Large"
    Workings_Insert_L_Profile
  Case "X-Large"
    Workings_Insert_XL_Profile
  Case Else
  ' Do nothing if no macro is selected or the selected macro does not exist
  End Select
End Sub

 

[Richhodg]

Thanks for the advise, I will give these a try tomorrow

 

[DanteAmor]

Hi and welcome to MrExcel

Or instead of capturing the text, you can capture a number from 1 to 5, for example:

Sub Insert_Profile()
  ' Declare variables
  Dim ChosenMacro As Variant
  
  ChosenMacro = Application.InputBox("Please enter number of Profile Size:" & vbCr & _
    "1) X-Small, 2) Small, 3) Medium, 4) Large, 5) X-Large", "Profile Size List", Type:=1)
  
  ' Run the selected macro
  Select Case ChosenMacro
    Case 1
      Workings_Insert_XS_Profile
    Case 2
      Workings_Insert_S_Profile
    Case 3
      Workings_Insert_M_Profile
    Case 4
      Workings_Insert_L_Profile
    Case 5
      Workings_Insert_XL_Profile
    Case Else
    ' Do nothing if no macro is selected or the selected macro does not exist
  End Select
End Sub

 

[Richhodg]

Hi and welcome to MrExcel

Or instead of capturing the text, you can capture a number from 1 to 5, for example:

Sub Insert_Profile()
  ' Declare variables
  Dim ChosenMacro As Variant
 
  ChosenMacro = Application.InputBox("Please enter number of Profile Size:" & vbCr & _
    "1) X-Small, 2) Small, 3) Medium, 4) Large, 5) X-Large", "Profile Size List", Type:=1)
 
  ' Run the selected macro
  Select Case ChosenMacro
    Case 1
      Workings_Insert_XS_Profile
    Case 2
      Workings_Insert_S_Profile
    Case 3
      Workings_Insert_M_Profile
    Case 4
      Workings_Insert_L_Profile
    Case 5
      Workings_Insert_XL_Profile
    Case Else
    ' Do nothing if no macro is selected or the selected macro does not exist
  End Select
End Sub

this works perfectly, thanks so much for your help.