vba:Userform

[Guest]

I'd like to know how to make a userform that tells the

user to "Select Worksheets". Worksheet will be displayed

in the listbox as selected. Selected Worksheets will be

stored in an array for future processing, when command

button (Done) is click. Thanks in advance.

 

[Bob Phillips]

Alex,

Here is some code

Dim arysheets

Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
If j = 1 Then
ReDim arysheets(1 To 1)
Else
ReDim Preserve arysheets(1 To j)
End If
arysheets(j) = .List(i)
End If
Next i
End With

End Sub

Private Sub UserForm_ACtivate()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
Me.ListBox1.AddItem sh.Name
Next sh
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob,
Thank you very much. It automatically listed all the worksheets without me
selecting them. I guess i have to explain what I'm trying to do. I have a
workbook with worksheets A, B, C, D, E, & F. Range("J47:Q47") of worksheet
selected (either A or B or C or whatever woksheet selected) will be copied &
paste-linked to worksheet F, starting Range("C4:J4") & the name of the
worksheet will be put in Range(C3). Next selected worksheet range will be
copied & paste-linked to the next row (C5) & so forth & so on (sequentially
by row, for the next worksheets selected). When the code is run:
1. A window or userform will open.
2. a list box will prompt me to select just the worksheets to be copied
(without me
selecting the ranges).
3. Worksheet selected will be displayed in the listbox when selected.
4. Option to remove selected worksheet in error.
5. Click Done Button when selection is done.
6. Prompt user to select worksheet where to paste-link selected
worksheet-ranges
(in case not Worksheet F).
7. Click Run/Start button to finish & exit the program.
Thanks again in advance.