Trying to use Selection Box when Input Box is prompted using a Command Button

[AMoreno]

Hello,

I have been trying to add a selection box to my vba code to use as an option once the user names their category, so the 2nd and 3rd question would prompt up a short list to choose from. (I could just use the input box and have their options listed out, but imagine errors may come from that)

After taking a stab at it, I have been unsuccessful. Online one states its not possible and then I see other code out their using it. So maybe I am just using it incorrectly and need to call it out another way. Which is where I need help.

While clicking on the button that would start the vba code, before it starts it highlights the sub name and where it says SelectionBoxSingle and states that the nor the "Sub or Function not defined". Could anyone possibly know the solution?

I tried creating a new module to call but that didnt work, and I tried using an user form as someone else mentioned but unfamiliar with user forms, so I think I am just using it all wrong and that is why it is not working...

Please Login or Register  to view this content.

Thanks,
Ant

[protonLeah]

SelectionBoxSingle/Multi seems to be from an add-in from the https://www.codeproject.com/tips/872...eraction-with-selectionbox.

It is not a part of VBA. You should complete your user form (empty now) with an inputbox and at least one combobox with a dynamic list fill range base on the inputbox contents.

[AMoreno]

@protonLeah or if anyone else has any ideas

Okay yeah that is what I saw, was not for sure about it.

But for your comment could you please confirm/clarify: With my current user form, add whatever buttons or detail that is required. And have the user form bring over the data to whatever line? Basically put my input box questions within the user form and use VBA with a command button to open the user form and then have a button inside the form to be the final piece before the line is actually added?

Also confused by the dynamic list range... assuming something along the lines of the offset function?

Thanks,
Ant

[mikerickson]

You could use this userform as a selection input box. The userform has one label, one listbox, and two command buttons

Please Login or Register  to view this content.

and it is invoked like an InputBox

Please Login or Register  to view this content.

Note that if the MultiSelect is set to true, it returns a delimited string of the selected values.

[AMoreno]

@mikerickson thanks so much for leaving this here. Okay so I am setting this up this morning.

So once I create my user form with the toolbox adding each of the tools you mention. Then move in the code into the user form.

But kind of confused by the second piece of code with the list? Does the second piece of code go with the list also go with the other user form code?

I right clicked on the listbox and view code and added the second piece of code to there, but the list seems not to show up on the user form? However, the first piece of code works with the (what would be the) Ok and Close button on the userform. The userform is shown with a command button on the Balances sheet.

Attached is an updated file with my explanation.

Thanks,
Ant

[mikerickson]

The userform that I made was a stand-alone entity.
It can be used either directly in a sub or invoked from a userform, just like an InputBox

In the attached, I renamed it ListChoose and added some features. Pressing Return = clicking OK. Double Click the list = OK. Right click a single select ListChoose and any selection will be cleared.

The attached file shows two demos of its use. One is through a userform (press the userform button on the worksheet, and then click on any of the label on Userform2). The other button on the sheet runs a test macro that uses the ListChoose directly.

[AMoreno]

@mikerickson thanks for explaining that, I will take a stab at in the morning to put it all together! Super busy today, but thanks again! Much appreciated.

Thanks,
Ant