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

Richhodg

New Member
Joined
Feb 28, 2023
Messages
4
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
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:


VBA 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.
 
Last edited by a moderator:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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?
 
Upvote 0
Or simply:
VBA Code:
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
 
Upvote 0
Thanks for the advise, I will give these a try tomorrow
 
Upvote 0
Hi and welcome to MrExcel

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

VBA Code:
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
 
Upvote 0
Solution
Hi and welcome to MrExcel

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

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top