Excel Custom Lists?

[gmooney]

I just created a custom list in an Excel file that I am using for a Slicer and pivot table filter.

I emailed the file to another person and the custom list did not transfer with the file?

How can I make sure that the custom list stays in the file?

 

[Paul Ked]

Don't think you can, directly, because they are stored in the Registry.

You could put it (them) on a separate sheet with instructions to the recipient describing how to add it (them).

 

[lrobbo314]

You can add the custom list with VBA on the Workbook_Open event.

Private Sub Workbook_Open()
On Error Resume Next


For i = 1 To Application.CustomListCount 'Get rid of other custom lists, catch errors for system lists
   Application.DeleteCustomList (i)
Next i


Application.AddCustomList ListArray:=Array("Item 1", "Item 2", "Item 3", "Item 4", "Item 5")
End Sub

 

[Paul Ked]

Nice one!

 

[gmooney]

You can add the custom list with VBA on the Workbook_Open event.

Private Sub Workbook_Open()
On Error Resume Next


For i = 1 To Application.CustomListCount 'Get rid of other custom lists, catch errors for system lists
   Application.DeleteCustomList (i)
Next i


Application.AddCustomList ListArray:=Array("Item 1", "Item 2", "Item 3", "Item 4", "Item 5")
End Sub

You can add the custom list with VBA on the Workbook_Open event.

Private Sub Workbook_Open()
On Error Resume Next


For i = 1 To Application.CustomListCount 'Get rid of other custom lists, catch errors for system lists
   Application.DeleteCustomList (i)
Next i


Application.AddCustomList ListArray:=Array("Item 1", "Item 2", "Item 3", "Item 4", "Item 5")
End Sub

lrobbo314
Thank you! Can I replace the array of Item 1, Item 2, with looking up the values from a range in a sheet of the workbook? If so what would I change that line of code to?

 

[lrobbo314]

It would be

Application.AddCustomList ListArray:=Range("A1:A5")

 

[gmooney]

It would be

Application.AddCustomList ListArray:=Range("A1:A5")

Irobbo314 does that replace all of the code or just a certain section of it?

 

[lrobbo314]

It's just replacing the line below.

Application.AddCustomList ListArray:=Array("Item 1", "Item 2", "Item 3", "Item 4", "Item 5")

 

[gmooney]

It's just replacing the line below.

Application.AddCustomList ListArray:=Array("Item 1", "Item 2", "Item 3", "Item 4", "Item 5")

Thank you so much!

 

[gmooney]

irobbo314. Can the sheet be hidden that I want to look the values up?