showing a listbox with last entry as the default

[Katrina]

So far a single selection listbox is the only userform I haven't been able to
figure this out on. I have 4 listboxes with the same problem...the simplest
is filled with months during the initialize event. I would like it to
display with the previous selection highlighted as default. For example, if
the previous entry was November, I would like November to be highlighted, but
it always goes back to January. The previous entry is always stored in
sheet1:AA19 as a string. One other question I have regards populating a list
or combobox from a variable sized range. For example, I use xlDown to
determine where the end of the list is and name it FarmTable. I then specify
Listbox1.ControlSource=Farmtable.address. The problem is if FarmTable is on
Sheet1 and Sheet2 is activated, the program populates the box from the
specified cells on the active sheet. I can work around this issue but would
like to avoid having the application user seeing all the tables and "guts"
throughout the workbook so am always trying accomplish things without
actually selecting ranges while having an empty range on the screen. Any
help would be appreciated. Thank you. Katrina

 

[Jay]

Hi Katrina -

Question 1. Set two properties for the listbox using the Properties Window.
Set the RowSource property to a worksheet range that holds the 12 months of
the year (Jan, Feb, Mar, etc.). Then set the ControlSource property to your
target cell Sheet1:AA19 and that month should be highlighted whenever the
list opens.

Question 2. Comboboxes can be populated with "dynamic ranges". Dynamic
range names refer to ranges that change in size at will. They're dynamite
for a variety of applications, so I think its worth the time investment to
learn about how to tame them. There are a number of sources, including this
discussion group, but check out the following for starters:
http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm.

Good luck.

 

[Bob Phillips]

If the source of the data for the listbox is a table on a worksheet you can
do a match against that table to get the index, and use that. For example

iRow = Application.Match(Worksheets("Sheet1").Range("AA19").Value,
Worksheets("Sheet1").Range("M1:M25"),0))
ListBox1.ListIndex = iRow -1

If not, you will need to loop through the lits and get the index

Private Sub UserForm_Initialize()

Dim i As Long

For i = 0 To ListBox1.ListCount - 1
If ListBox1.List(i) = Worksheets("Sheet1").Range("A19").Value Then
ListBox1.ListIndex = i
Exit For
End If
Next i
End Sub

Second part

Listbox1.ControlSource=Farmtable.address(,,,True)

uses the external address of the range

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Katrina]

I tried both Jay's (by the way, Jay, thank you for the link...that looks like
a good website and in going through the hundreds of sites Google brought up I
hadn't found that one yet.) and Bob's suggestions (I think I've already tried
both of those methods), and I keep having the same problem. I've made a
simple stand alone user form to test. I'm probably just overlooking
something very simple. Here is the code I've entered in the User_Form
Initialize event.

Private Sub UserForm_Initialize()
Dim MonthTable As Range
(When I tried using iRow, I declared it as an integer here)
ListBox1.RowSource = MonthTable.Address
MonthTable = Sheets("sheet1").Range("A1:A12")
(I tried using defining ListBox1.ListIndex and ListBox1.ControlSource
here (one
at a time))
End Sub

No matter what method I try I almost always get this error:

Run-time error '91' Object variable or With Block variable not set

All of this code should go in the Initialize event, right? Thank you.
Katrina