VBA listbox - need to highlight selection when user form initializes

F

Frederick Kawa

Does anyone know how I can highlight the last selection of a list box when
the form initializes?

I made a 4 item listbox with a For Next (i) loop that reads the items from a
range in Sheet2.

When an item is selected I put it in another cell on Sheet2 that will be
read into a text file for later use.

I want that last selection to be highlighted the next time I run the form so
the user knows which item was selected the last time.

Any ideas on how to do it?

Thanks.

Fred.
 
P

papou

Hello Frederick
Depending on whether you wish to keep this value even after closing your
workbook, here is some way to achieve what you want:
Case #1 (workbook not closed)
Insert a standard module in your VBA project.
1 - Create a new public variable in this new module eg:
Public PreviouslySelected
2 - Give the listbox value to this variable and use it next time you show
the form using the Userform_Initialize event eg:
'**** Example #1 ***************
Private Sub CommandButton1_Click()
If ListBox1.Value = "" Then Exit Sub
PreviouslySelected = ListBox1.Value
Worksheets("Sheet2").Range("A1").Value = ListBox1.Value
End Sub.
Private Sub UserForm_Initialize()
For i = 1 To Worksheets("Sheet1").Range("D65536").End(xlUp).Row
ListBox1.AddItem Cells(i, 4)
Next i
If PreviouslySelected <> "" Then
ListBox1.Value = PreviouslySelected
End If
End Sub
'*****************************
Case #2 (workbook reopened)
Use the value you have put in your sheet Sheet2 in the Userform_Initialize
event eg:
'***** Example #2 **************
Private Sub UserForm_Initialize()
For i = 1 To Worksheets("Sheet1").Range("D65536").End(xlUp).Row
ListBox1.AddItem Cells(i, 4)
Next i
If Worksheets("Sheet2").Value <> "" Then
ListBox1.Value = Worksheets("Sheet2").Value
End If
End Sub
'******************************

HTH
Cordially
Pascal
 
F

Frederick Kawa

Pascal,

Thank you for responding so quickly and thank you for answering the
question. As soon as I read the code, I slapped my forehead.

I think we sometimes get so wrapped up in what we're doing that we overlook
the obvious and that's exactly what I did with my program.

You know, I'm very frustrated with the reference books out there on VBA. I
paid handsomely for the Excel 2000 Developer's Handbook and could have
bought a plain cheese pizza instead for a lot less money. The book claims
to have info for advanced programmer's and most of it is a reprint of the
Help files in Excel, and the author glossed over anything she didn't know
instead of looking it up. I thought I was reading another Microsoft (buy
book number 2, 3 and 4 for the answer to that question) book.

Can you recommend a good VBA programming book?

Looking forward to your next reply, I thank you again.

Fred.
 
Top