Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.