Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Hi I was wondering if anyone could help me create a macro that will add a selection to excels custom list. Typically to do this I select a a series of rows in one column, then I got to file, options, advanced and scroll to the very bottom and click edit custom lists. When going through this with the macro creator turned on all I get is a very simple script that is related to the specific cells I have selected. I would like to tweak the code so that it is using whatever I have currently selected to add to the custom list. That way I am not always tied down to the J4-J9 Range. Below is the code I receive.

Sub Customlistadd()
' Customlistadd Macro
' Keyboard Shortcut: Ctrl+Shift+I
    Application.AddCustomList ListArray:=Range("J4:J8")
End Sub

Is it as simple as adding current selection in place of j4:j8? If so how do I do I write that exactly? Do I first need to declare a variable type to hold the current section? Almost all examples I've looked at that seem to be attempting to do something along the lines of what I am trying to do tend to use variables to hold the current selections range. Thank you.

share|improve this question

Change your code to use the Selection object, which will always contain the currently selected cells (if more than 1 is selected). i.e.

Application.AddCustomList Selection

And make sure your custom list only contains letters, not numbers. e.g. A,B,C,D,E,F,G,... or A1,A2,A3,A4,A5,A6,... are fine, but 1,2,3,4,5 isn't.

share|improve this answer
I have tried the following combinations, none of which have run successfully. Could you provide a little more clarification Application.AddCustomList ListArray:=Range("selection") Application.AddCustomList ListArray:=Range(selection) Application.AddCustomList ListArray:=Range selection" Application.AddCustomList ListArray:= selection Application.AddCustomList selection None the combinations I have have tried run correctly. I know I must be screwing up the syntax but I just can't figure out where. – small3687 Jun 6 '13 at 23:16
Just use the command as Rhys Gibson has posted. No ListArray or Range. Just use Application.AddCustomList Selection – teylyn Jun 7 '13 at 1:09
Unfortunately that is how I have already tried to write the code but it fails to run. Sub Customlistadd() ' ' Customlistadd Macro ' ' Keyboard Shortcut: Ctrl+Shift+I ' Application.AddCustomList Selection End Sub That is the entirety of my code and I get stopped on the line with the Selection object. Any other ideas? I end up receiving a run time error 1004 – small3687 Jun 7 '13 at 14:15
Make sure your custom list only contains letters, not numbers. e.g. A,B,C,D,E,F,G,... is fine, but 1,2,3,4,5 isn't. – Rhys Gibson Jun 9 '13 at 20:45
They are 20 digit strings of numbers that are formatted as text. Do you think that is the problem? – small3687 Jun 10 '13 at 12:59

Try this

Public Sub CreateCustomList()
  Dim v As Variant
  Dim rng As Excel.Range

  Set rng = ActiveSheet.Range("A1:A3")

  'Transpose from 2 dim array to 1 dim array
  v = Application.Transpose(Selection)

  'Add the custom list
  Application.AddCustomList v

End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .