Action on Multiselect Listbox

[Sige]

Hello Dearest Wizards,

I have been able to load my listbox ... with Items in Column A:

Private Sub Worksheet_Activate()
Dim cell As Range
Dim Rng As Range

'Me.ListBox1.ListFillRange = ""
'Me.ListBox1.Clear

With ThisWorkbook.Sheets("Sheet1")
Set Rng = .Range("a1", .Range("a1").End(xlDown))
End With

For Each cell In Rng.Cells
Me.ListBox1.AddItem cell.Value
Next cell
End Sub

My Problems:
1. Upon loading I would like to keep the items which were checked
already!
2. Above procedure adds again the same list to the already existing (if
you do not clear it ...)
Main Issue:
3. On each "check item" i would like to run a macro. Like:

Select Case
case1 item1 checked? then Run Macro1
case2 item2 not_checked then nothing
case3 item3 checked? run Macro3

End Select

- Ok my nr of items in the listbox can vary and my selection (of
macros) to be activated limited; but: If Nothing then MsgBox "no macro
assigned"

Any experienced wizard to help me out? Sige

 

[Bob Umlas]

You can load your list without looping:
Private Sub UserForm_Initialize()
Me.ListBox1.List = Application.Transpose(Range(Range("A1"),
Range("A1").End(xlDown)))
End Sub

you can run the macros with each click with:
Private Sub ListBox1_change()
Application.Run "Macro" & Me.ListBox1.ListIndex + 1
End Sub

and you'd need a macro for each one, if that's what you want, in a regular
module
Sub macro1()
....
end sub

etc

but it'd be better to run the same macro & pass a parameter, like
Private Sub ListBox1_change()
MyMacro me.listbox1.listindex+1
End Sub

in regular module:
Sub MyMacro(MyNum as integer)
Select case MyNum
Case 1
....
Case 2
...
'etc.
End Select
End Sub

Bob Umlas

 

[SIGE]

Thx a lot Bob,
I think I will get my way through with this!

Sige

"NOSPAM" to be removed for direct mailing...

 

[SIGE]

Hi Bob,

I am working though with List-Items which I can check/ un-check.

So each List-item can get a True or False-status.
Like:
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Run "Do"
Else
Run "UnDo"
End If
End Sub

How do I read , and keep the status in a listbox?
Cheers Sige

"NOSPAM" to be removed for direct mailing...