listbox list

[Guest]

Hello,

I would like to create a list in a listbox on an Excel Form, based values in
column A that start with the word "Total." Is it possible to reference such a
list. Please let me know if I need to give more detail.

Thank You

 

[Frank Kabel]

Hi
see:
http://www.contextures.com/xlDataVal01.html

if you mean the spreadsheet with 'Form'

 

[Guest]

Sorry, I meant a VBA Form/Listbox. I think that your weblink almost covers
it, but how would I Add the list to the listbox on the form. I understand the
Data Validation Method on a spreadsheet, but how do I combine this function
with a VBA form listbox tool.

 

[Dave Peterson]

I created a listbox on a userform and used this code:

Option Explicit
Private Sub UserForm_Initialize()

Dim myCell As Range
Dim myRng As Range
Dim myWord As String

myWord = "total"

With Worksheets("Sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
If LCase(Left(myCell.Value, Len(myWord))) = LCase(myWord) Then
Me.ListBox1.AddItem myCell.Value
End If
Next myCell

End Sub


You may want to look at some code that John Walkenbach has shared:
http://j-walk.com/ss/excel/tips/tip47.htm

You may want to incorporate some of the stuff he did (unique entries and
sorting).