Fill Array from Selected Items in Text Box
I'm working this out by trial and error and could use some help. I've
got a UserForm with a ListBox called lbProcess. It's RowSource property is a two column range in a worksheet. If I select two or more items, it seems to work ok. If I only select one item, I get a "Subscript Out of Range" error and debug highlights the line marked below. I assume that I'm not declaring the array correctly. Any guidance would be appreciated. Private Sub cmdProcessesPicked_Click() cnt = 0 '''''''''''''''''''''''''''''''''''''''''determine number of selected items For i = 0 To lbProcess.ListCount - 1 If lbProcess.Selected(i) Then cnt = cnt + 1 Next i If cnt < 1 Then MsgBox "No processes were selected." Unload frmProcessList Exit Sub End If ''''''''''''''''''''redim the array based on number of selections; fill array with just selections ReDim arrProcess(1 To cnt, 1 To cnt) ar = 1 For i = 0 To lbProcess.ListCount - 1 If lbProcess.Selected(i) Then arrProcess(ar, 1) = lbProcess.List(i, 0) arrProcess(ar, 2) = lbProcess.List(i, 1) ' <<<<<Problem Here ar = ar + 1 End If Next i Unload frmProcessList End Sub |
Fill Array from Selected Items in Text Box
Hi John Michl,
Try changing: ReDim arrProcess(1 To cnt, 1 To cnt) to ReDim arrProcess(1 To cnt, 1 To 2) --- Regards, Norman "John Michl" wrote in message oups.com... I'm working this out by trial and error and could use some help. I've got a UserForm with a ListBox called lbProcess. It's RowSource property is a two column range in a worksheet. If I select two or more items, it seems to work ok. If I only select one item, I get a "Subscript Out of Range" error and debug highlights the line marked below. I assume that I'm not declaring the array correctly. Any guidance would be appreciated. Private Sub cmdProcessesPicked_Click() cnt = 0 '''''''''''''''''''''''''''''''''''''''''determine number of selected items For i = 0 To lbProcess.ListCount - 1 If lbProcess.Selected(i) Then cnt = cnt + 1 Next i If cnt < 1 Then MsgBox "No processes were selected." Unload frmProcessList Exit Sub End If ''''''''''''''''''''redim the array based on number of selections; fill array with just selections ReDim arrProcess(1 To cnt, 1 To cnt) ar = 1 For i = 0 To lbProcess.ListCount - 1 If lbProcess.Selected(i) Then arrProcess(ar, 1) = lbProcess.List(i, 0) arrProcess(ar, 2) = lbProcess.List(i, 1) ' <<<<<Problem Here ar = ar + 1 End If Next i Unload frmProcessList End Sub |
Fill Array from Selected Items in Text Box
Thanks. That did the trick. Arrays confuse me a bit and the examples
I've seen haven't be clear enough for me to understand completely. - John Norman Jones wrote: Hi John Michl, Try changing: ReDim arrProcess(1 To cnt, 1 To cnt) to ReDim arrProcess(1 To cnt, 1 To 2) --- Regards, Norman "John Michl" wrote in message oups.com... I'm working this out by trial and error and could use some help. I've got a UserForm with a ListBox called lbProcess. It's RowSource property is a two column range in a worksheet. If I select two or more items, it seems to work ok. If I only select one item, I get a "Subscript Out of Range" error and debug highlights the line marked below. I assume that I'm not declaring the array correctly. Any guidance would be appreciated. Private Sub cmdProcessesPicked_Click() cnt = 0 '''''''''''''''''''''''''''''''''''''''''determine number of selected items For i = 0 To lbProcess.ListCount - 1 If lbProcess.Selected(i) Then cnt = cnt + 1 Next i If cnt < 1 Then MsgBox "No processes were selected." Unload frmProcessList Exit Sub End If ''''''''''''''''''''redim the array based on number of selections; fill array with just selections ReDim arrProcess(1 To cnt, 1 To cnt) ar = 1 For i = 0 To lbProcess.ListCount - 1 If lbProcess.Selected(i) Then arrProcess(ar, 1) = lbProcess.List(i, 0) arrProcess(ar, 2) = lbProcess.List(i, 1) ' <<<<<Problem Here ar = ar + 1 End If Next i Unload frmProcessList End Sub |
All times are GMT +1. The time now is 02:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com