Link to home
Start Free TrialLog in
Avatar of aaron_karp
aaron_karp

asked on

Excel / VBA Macro to Insert Value from Pop-up

Hi experts,

Is there any way I could create a VBA macro that when run would display a pop-up list of values of which one could be selected and inserted into the current cell?

For exmaple

Cell 1: Empty

Run Macro - brings up pop-up list:
Option1
Option2
Option3
Option4
Option5

Select Option3:

Cell 1: (Option3)

Is this at all possible? Can you give me a solution?

Thanks.
-- Matt
Avatar of R_Rajesh
R_Rajesh

str1 = Array("option1", "option2", "option3", "option4", "option5")
strP = "Please enter your option [1-5]"
For i = LBound(str1) To UBound(str1)
strP = strP & vbLf & i + 1 & ". " & str1(i)
Next i
Range("A1") = str1(InputBox(strP) - 1)
If having the user type the options in an input box is acceptable to you, there is this possibility:

Sub selectoption()
 Dim myoption As String
 myoption = InputBox("Select from the following Options: Option1, Option2, Option3, Option4, Option5")
 ActiveCell.Value = myoption
End Sub

If you wanted the cell where the options would appear to be a particular cell, you could replace:

Activecell.value

with the rangename of the cell e.g.

Range("A1").Value = myoption
Avatar of aaron_karp

ASKER

Hi,

That seems to work, but I need it to be relative to the selected cell. So, if I had B12 selected it would insert the selected option into B12 and not A1.

-- Matt

Sub testt()
str1 = Array("option1", "option2", "option3", "option4", "option5", "option6")
strP = "Please enter your option [1-" & UBound(str1) + 1 & "]"
For i = LBound(str1) To UBound(str1)
strP = strP & vbLf & i + 1 & ". " & str1(i)
Next i
ActiveCell = str1(InputBox(strP, , 2) - 1)
End Sub
Is there any way to make the pop-up display a list of values that the user could just click rather than using an input box?

-- Matt
ASKER CERTIFIED SOLUTION
Avatar of R_Rajesh
R_Rajesh

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can call the userform from you macro like this
UserForm1.Show
Avatar of byundt
You may find it easier to avoid VBA altogether and use a Data Validation dropdown.

To install a Data Validation dropdown, select the cell to receive the dropdown, then open the Data...Validation menu item.

On the Settings tab, choose Allow...List. On the Source field, enter your values separated by commas--dog, cat, mouse, aardvark. Or you could enter a cell range reference instead, e.g.
=A1:A6          This cell reference must be on the same worksheet
Alternatively, you may enter a named range reference for the Source:
=myRange    This reference may be on a different worksheet

If you want to put these dropdowns in a range of cells, then select that range before starting the above process. Each cell in the selected range will then be set up with a dropdown. You can also copy an existing data validation dropdown, then select a new range of cells and use the Edit...Paste Special...Validation menu item to copy the data validation into the other cells.

The entire process is nicely illustrated at http://www.theofficeexperts.com/excel.htm#DropdownLists
This works great! Thanks!