VBA listbox - need to highlight selection when user form initializes

  • Thread starter Thread starter Frederick Kawa
  • Start date Start date
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.
 
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
 
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.
 
Back
Top