Having problems:Multiple selection,listbox, boolean??

[romij29]

Hello to everybody,
Havent 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

 

[PHV]

Any error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[romij29]

Hello PHV,
yes there is.stupid of me to not have put it on there.
It says;
"Could not get the Selected property.Invalid argument"
Apparently my F-Range on the error line is the listbox contents and these come up to 11.Whatever choice I make in that listbox upon running and debugging lists i to b equal to 11?
any ideas as to why this is occuring

 

[PHV]

Add this line before the one raising the error:
If i >= lstRegions.ListCount Then Exit Do

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[romij29]

Thanks PHV,tried that 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.

 

[romij29]

Also on the control worksheet 3 where data is pasted to columns A:H I seem to have data from G6:H10 copied to J2:K5 depending on the number of selections made on listbox.e.g. if i make 3 selections on listbox, i get data copied from G6:H9 TO J2:K4, That is not supposed to happen.It ight seem vague but here is my code for running the first button called "FMA data".I click on this button and data is copied from tow files into worksheets 2 and 3 of excel control wkbook.Sheets 2 and 3 are called FMA DATA and RETAIL FMA resp.

Code


Sub mnuFileOpen_Click()
Dim FullFileName As String
Dim strFolder As String
Dim wbThis As Workbook, wbTrad As Workbook, wbRet As Workbook, wbTradAccs As Workbook

Set wbThis = ActiveWorkbook

FullFileName = Application.GetOpenFilename("Excel Files (*.xls),*.xls", False)

Application.StatusBar = "Opening " & FullFileName

If FullFileName = "False" Then
Application.StatusBar = False
Exit Sub
End If

Set wbTrad = Workbooks.Open(FullFileName)

strPath = Workbooks("Trading Accounts.xls").Sheets("Control").Range("M2")
strCost = Workbooks("Trading Accounts.xls").Sheets("Control").Range("M6")
strPrd = Workbooks("Trading Accounts.xls").Sheets("Control").Range("M7")

If wbTrad.Path = strPath & strCost Then

Application.StatusBar = "Copying " & "FMA Data"
wbTrad.Sheets(1).Range("A:H").Copy
wbThis.Sheets("FMA Data").Range("A1").PasteSpecial xlValues

Application.CutCopyMode = False

Application.StatusBar = "Closing " & FullFileName
wbTrad.Close

Application.StatusBar = False

FullFileName = Application.FindFile
Set wbRet = ActiveWorkbook

Application.StatusBar = "Copying " & "Retail FMA"
wbRet.Sheets(1).Range("A:H").Copy
wbThis.Sheets("Retail FMA").Range("A1").PasteSpecial xlValues

wbThis.Activate
ActiveWorkbook.Sheets("Retail FMA").Range("A1:H1").Select
Selection.Delete Shift:=xlUp

Application.CutCopyMode = False
Application.StatusBar = "Closing Retail Workbook"

wbRet.Close

ElseIf wbTrad.Path = strPath & strPrd Then

Application.StatusBar = "Copying " & "Retail FMA"

wbTrad.Sheets(1).Range("A:H").Copy
wbThis.Sheets("Retail FMA").Range("A1").PasteSpecial xlValues

wbThis.Activate
ActiveWorkbook.Sheets("Retail FMA").Range("A1:H1").Select
Selection.Delete Shift:=xlUp

Application.CutCopyMode = False

Application.StatusBar = "Closing " & FullFileName
wbTrad.Close

Application.StatusBar = False

FullFileName = Application.FindFile
Set wbTradAccs = ActiveWorkbook

Application.StatusBar = "Copying " & "FMA Data"
wbTradAccs.Sheets(1).Range("A:H").Copy
wbThis.Sheets("FMA Data").Range("A1").PasteSpecial xlValues

Application.CutCopyMode = False

Application.StatusBar = "Closing OpstCine workbook"
wbTradAccs.Close


Else
MsgBox "File needs to be chosen from either " & strPath & strCost & " or " & strPath & strPrd _
& ". " & "Do not process trading and retail reports!", vbCritical, "Microsoft Excel Warning"


Application.StatusBar = "Closing " & FullFileName
wbTrad.Close

End If

Application.StatusBar = False