Use List in a macro

[bullcreel]

ActiveCell.Value = ActiveSheet.Range("A1").Value

I have the above macro, and a dozen variations of it on my worksheet. They all work great. My next step is that I want the value in "A1" to be a list, of say at least 7 items. So when i hit the macro, I want it to display the 7 items and I pick one that will display. I have tried different thing except for a User Form. Do I need to do a User Form for this or is there something I can add to my simple macro above?

 

[steve the fish]

For me the userform is what you want. A few steps you need to do. Create a named range holding your values to add to the combobox for your users to select. Create the userform itself with a combobox and a commandbutton. Then you want to add the code to the userform module similar to this:

Private Sub CommandButton1_Click()

ActiveCell.Value = ComboBox1.Value
Unload Me

End Sub

Private Sub UserForm_Initialize()

arr = Range("MyComboboxList")
ComboBox1.List = arr
    
End Sub

then finally in a standard module you need to create a macro to show the userform as the user demands eg

Sub ShowForm()

UserForm1.Show

End Sub

 

[bullcreel]

OK, I have those developed. Whats next?

 

[steve the fish]

Thats all. You just run the macro when you want to put a value in the activecell. The userform pops up. Select the value. Press the command button.

 

[bullcreel]

I apologize. I have never used a UserForm before. My range is called "Flyouts". I put that inplace of ("MyComboboxList"). There is nothing in my form.

 

[steve the fish]

You need to create the userform now and add the combobox and commandbutton to it then add the code to the modules as above.

 

[bullcreel]

OK. Thought I did all that. Will try again

 

[steve the fish]

Of vourse if you know the cells you want the list to appear in, rather than use Activecell, you could use a Data Validation dropdown list.

 

[Rick Rothstein]

Private Sub UserForm_Initialize()
  arr = Range("MyComboboxList")
  ComboBox1.List = arr
End Sub

Just pointing out that you do not have to assign a vertical range to an Variant variable first before assigning it to the List property of the ComboBox (or ListBox if you should be using that ever)... just assign the TRANSPOSE of the range directly.

Private Sub UserForm_Initialize()
  ComboBox1.List = Application.Transpose(Range("MyComboboxList"))
End Sub

or, since you are using a named range, this shortcut method...

Private Sub UserForm_Click()
  ComboBox1.List = [TRANSPOSE(MyComboboxList)]
End Sub

 

[steve the fish]

Thanks Rick. That was me doing first thing that came into head when it failed.