How to include a drop down input box in a macro

[MrsMac]

Hi Guys, I'm fairly new to macros so any help is appreciated!
I have written a macro to translate a column of text from any language (Auto-detected) into English - (code included below) which works fine as long as the data is in Column A, on a worksheet called 'Sheet1' which is ok.

Now I need to include a drop-down box of a list of languages for the user to choose the output language 'outputstring'. I need to be able to use this macro on hundreds of different spreadsheets across the world, and need it quickly so how on earth do I do that? Would I need to hard-code the list of languages into the macro? How do I get the associated 2-digit code from that to use in the translator?
Am completely blank on this, thanks in advance!
MrsMac


Current code:

Please Login or Register  to view this content.

Example of languages and codes: - I need the user to input eg. Czech from drop down list, and pass 'CS' to outputstring for translator.

Czech CS
Danish DA
Dutch NL
English EN
Esperanto EO
Estonian ET
Faeroese FO
Fiji FJ
Finnish FI
French FR
Frisian FY
Gaelic GD
Galician GL
Georgian KA
German DE
Greek EL
Greenlandic KL

[jewelsharma]

Hi MrsMac,
Unfortunately, the VBA InputBox does not have an option to create a dropdown.

For what you want to achieve, you should look at a "ListBox" or a "ComboBox" on a UserForm. Userforms are fairly easy to create & code, aesthetically much more elegant than an "InputBox", and can help you create a plethora of options that interact with the spreadsheet users at run-time. Search within this forum (or simply Google for it) and you'll find plenty of good examples.

HTH. Cheers!
Jewel