MultiSelect ListBox Help

[VBA_Newbie79]

Hello everyone,
I haven’t received a response on my original posting, so I thought I would
try again.

I need to find a way for VBA to accept the text of items chosen from a
MultiSelect listbox as either sheet names or named ranges.
------------------------------------------
Code I posted in my original posting:
Private Sub CommandButton2_Click()

Dim res As Variant
Dim Last As Integer

For i = 0 To Change_Region.ListBox1.ListCount - 1
If Change_Region.ListBox1.Selected(i) Then
res = Application.VLookup(Change_Region.ListBox1.Text = i,
Worksheets("LISTS").Range("Regions"), 1, False)
If Not IsError(res) Then
Last = Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
Change_Region.Hide
Application.ScreenUpdating = False
Worksheets(res).Select
Worksheets(res).Range(i).Copy Worksheets("Master").Range(Last +
1, 1)
Else
Change_Region.Hide
MsgBox "Match not made. Please try again."
Change_Region.Show
End If
Else
Change_Region.ListBox1.Clear
MsgBox "Please choose a region or click Cancel."
End If
Next i

End Sub
------------------------------------------
Altered code based on research:
Private Sub CommandButton2_Click()

Dim selCount As Long: selCount = 0
Dim selItems() As String
Dim ndx As Long
Dim arrndx As Long

selCount = selCount + 1
arrndx = 0
ReDim selItems(0 To selCount - 1)

For i = 0 To Change_Region.ListBox1.ListCount - 1
If Change_Region.ListBox1.Selected(i) = True Then
selItems(arrndx) = ListBox1.List(i)
arrndx = arrndx + 1
Application.VLookup(arrndx,
Worksheets("LISTS").Range("RegionRange"), 4, False) = k
Application.Goto (k)
Change_Region.Hide
Application.ScreenUpdating = False
Selection.Copy
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select 1
ActiveSheet.Paste
Else
Change_Region.Hide
MsgBox "Please choose a region or click Exit."
Change_Region.Show
End If
Next i
------------------------------------------

 

[Tom Ogilvy]

Here is a guess - it isn't clear what is in the listbox or what is in column
4 of the lookup range.


Private Sub CommandButton2_Click()

Dim selCount As Long
Dim selItems() As String
Dim ndx As Long
Dim arrndx As Long

selCount = 0
ReDim selItems(0 To selCount)


For i = 0 To Change_Region.ListBox1.ListCount - 1
If Change_Region.ListBox1.Selected(i) = True Then
selItems(SelCount) = ListBox1.List(i)
SelCount = SelCount + 1
Redim Preserve selItems(0 to SelCount)
End if
Next
if selCount = 0 then exit sub
selCount = selCount - 1
Redim Preserve selItems(0 to SelCount)
for i = 0 to selCount
res = Application.VLookup(selItems(i), _
Worksheets("LISTS").Range("RegionRange"), 4, False)
if not iserror(res) then
Application.Goto Range(res)
Change_Region.Hide
Application.ScreenUpdating = False
Selection.Copy
Worksheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select 1
ActiveSheet.Paste
End If
Next i

 

[VBA_Newbie79]

Tom,
Thank you for helping me out. What I am trying to accomplish is to use the
userform (Change_Region) to choose from one or more regions from the 5
regions list below (found in ListBox1). If I choose two or more regions,
such as Northeast, Midwest, and Southeast, I need the following to happen:

When I click a button, the data from the Northeast worksheet would be pasted
into the Master worksheet, starting in the second row. Then the data from
the Midwest worksheet would be pasted below the Northeast data in the
"Master" worksheet, and then the data from the Southeast worksheet would be
pasted below the Midwest data in the Master worksheet.

<Regions>
Northeast
Southeast
Midwest
Southwest
West

The number 4 in the VLOOKUP was meant to pull in the names of the named
ranges so that I could select the data that needs to be copied. I was using
VLOOKUP because I was hoping to use the order the regions are in ListBox1 to
access the named range of each region.

Any help you can provide would be appreciated.