how to use an array and a range

J

Jake

I’m having difficulty managing an array and a range in my program. I want
the user to choose a whole range on a worksheet, which I assign to a range
variable, or to select a range in a list box on a form. I populate an array
with the user’s selection in the list box—at least I think I do, I’ve little
experience with arrays. This way the user can select all the items or choose
a smaller range in the list box. Then I want to take the user’s selection,
either all the items or the list box selection and iterate through those
items.
This code loads form and manages list box:

sCostCentreSelection = MsgBox("To run all Cost Centres select Yes" & vbCrLf
& "Select No to choose Cost Centres" & vbCrLf _
& "Choose range on sheet", vbYesNo, "Select Cost Centres to run")
If sCostCentreSelection = vbNo Then
frmCostCentres.Show
' *** code to set rngCostCentres to array***
Else
Set rngCostCentres = Range("inpCostCentres") 'this is a range on the
Matrix sheet for all the cost centres
End If

This code is from form:
Private Sub cmdOK_Click()
'test code to extract list box cost centre selection
'arrayCostCentres in general declarations calling procedure
Dim sMsg As String
Dim iCounter As Integer
Dim sResponse As String
sMsg = ""
For iCounter = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(iCounter) Then _
sMsg = sMsg & ListBox1.List(iCounter) & vbCrLf
ReDim arrayCostCentres(0 To ListBox1.ListCount)
arrayCostCentres(iCounter) = ListBox1.Selected(iCounter)
Next iCounter
sResponse = MsgBox("You Selected: " & vbCrLf & sMsg & vbCrLf & "Click
Yes to accept range" & _
vbCrLf & "Click No to select another range" & vbCrLf & "Click Cancel
to halt program", vbYesNoCancel, "Cost Centres")
If sResponse = vbYes Then
MsgBox "Run Cost Centres"
Unload frmCostCentres
ElseIf sResponse = vbCancel Then
End
End If
End Sub

Any help appreciated.
Thanks,
Jake
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top