Problems with List Box Array

[romij29]

Hi,
Haven't been on here in a while. Have a slight problem(at least i suspect it is) which i would like some help with:
I have a macro thatopens a userform on which a button is clicked to copy and paste data into sheets 2 and 3 of the excel control worksheet. Right after that a RUN button is clicked after making either a single or multiple selection of criteria from a listbox which then opens several destination folders to which data copied into sheets 2 and 3 of contol worksheets is in turn copied and distributed to the respective destination worksheets specified in the listbox selection.Here is the code underlying the RUN button.

My code stops at the

"If lstRegions.Selected(i) = True Then" line

Public Sub Create_Click()
Dim i As Integer, intRow As Integer
Dim fs, a, b
Dim blnfail As Boolean
Dim strTradRegion As String, strRetRegion As String

Set fs = CreateObject("Scripting.FileSystemObject")
'Assigns a value for the objects, in this case the folders
'determined to the following variables
'The macro will later test the value, if true the folder exists
'and the macro will continue, if false an error message will
'appear stating that destination folder does not exist in the
'assigned path
strTradRegion = Workbooks("Trading Accounts.xls").Sheets("Control").Range("M3")
strRetRegion = Workbooks("Trading Accounts.xls").Sheets("Control").Range("M4")
a = fs.folderexists(txtPath & strTradRegion)
b = fs.folderexists(txtPath & strRetRegion)

Workbooks("Trading Accounts.xls").Sheets("Control").Range("J2:K20").Clear
intRow = 2
i = 0
If cbValidate.Value = True Then Call validation(blnfail)
If blnfail = True Then Exit Sub
Do While Range("F" & i + 2) = "" = False
If lstRegions.Selected(i) = True Then ---------> macro stops at this line
Range("J" & intRow) = Range("G" & i + 2)
Range("K" & intRow) = Range("H" & i + 2)
intRow = intRow + 1
End If
i = i + 1
Loop

If Trading_Accs.Value = True And Retail_Reps.Value And cbClose = True Then
If b = False Then
MsgBox ("Could not find folder 'Trading Accounts' from path ") & txtPath, vbCritical
Exit Sub
End If
By_Region
Retail_Regions
Save
ElseIf Trading_Accs.Value And Retail_Reps.Value = True Then
If b = False Then
MsgBox ("Could not find folder 'Trading Accounts' from path ") & txtPath, vbCritical
Exit Sub
End If
By_Region
Retail_Regions
ElseIf Trading_Accs.Value And cbClose.Value = True Then
If b = False Then
MsgBox ("Could not find folder 'Trading Accounts' from path ") & txtPath, vbCritical
Exit Sub
End If
By_Region
Save
ElseIf Retail_Reps.Value And cbClose.Value = True Then
If a = False Then
MsgBox ("Could not find folder 'Retail Reports' from path ") & txtPath, vbCritical
Exit Sub
End If
Retail_Regions
Save
ElseIf Trading_Accs.Value = True Then
If b = False Then
MsgBox ("Could not find folder 'Trading Accounts' from path ") & txtPath, vbCritical
Exit Sub
End If
By_Region
ElseIf Retail_Reps.Value = True Then
If a = False Then
MsgBox ("Could not find folder 'Retail Reports' from path ") & txtPath, vbCritical
Exit Sub
End If
Retail_Regions
Else
MsgBox ("Please select at least one option"), vbCritical
End If
Trad_Accs.Hide
End Sub


I suspect that it has something to do with my boolean variables.I must pt out that when i first click on the first button (with a seperate code) to copy and paste data into sheets 2 and 3 and close the form ,then open again and click on the RUN button, it runs perfectly.
However the problem arises when i decide to subsequently click the RUN button after the first button has been clicked.

Appreciate all help, Thanks

romij29

Have inserted this infront of the faulty line code:

If i > = lstRegions.ListCount Then Exit Do

But it doesnt seem to run at all.
After I click on first bur-tton to import data into the control sheets 2 and 3, and clicking on the RUN button, the macro does not seem to run nor it does it show any runtime errors.
I think its gto something to do with my range of options within the list box..as I get this message:

"Could not get the Selected property.Invalid argument"

Hope this explains my problem and some help would be vastly appreciated

 

[Zathras]

Your loop is controlled by the number of rows of data, but when you have more rows of data in the worksheet than rows in the list box (and it is a multi-select list box), then you will see the error message you saw.

Your fix should have worked. It did for me.

Here is a stripped-down version of the routine that you can experiment with (set the list box Multi-Select property to multi-select):

Option Explicit

Private Sub UserForm_Initialize()
  lstRegions.AddItem ("aaaaaa")
  lstRegions.AddItem ("bbbbbb")
  lstRegions.AddItem ("cccccc")
  lstRegions.AddItem ("dddddd")
  lstRegions.AddItem ("eeeeee")
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
  i = 0
  Do While i < (lstRegions.ListCount + 1)[COLOR=green]
 'If i >= lstRegions.ListCount Then Exit Do[/color]
 If lstRegions.Selected(i) = True Then
      MsgBox "selected " & i
    End If
  i = i + 1
  Loop
  MsgBox "finished"
End Sub

If that isn't the problem, try to do what I have done -- set up a simplified version of the routine that fails and then post back. I don't have time to reverse engineer your code to set up an evironment to test your original post.