Populating a listbox will a certain cell on multiple worksheets in the activeworkbook

[KimberlyC]

Hi!
I have a listbox in a user form...
I would like to populate the listbox ( on open) with cell A4 (this will be a
state or city that the user enters into cell A4 of each DS Details
worksheet) of all the worksheets in the activeworkbook that have the name
"DS Details" in the name. ( it's ok to have the ws name and cell A4 contents
in the listox)
These worksheets have numbers after them and appear as DS Details , DS
Details (2) , DS Details (3)...and so on...unlimited allowed (or Excel
allows)

After the list is populated with the contents of cell A4 of each worksheet
that has the name DS Details, I would like the user to select the one they
want (by clicking on it in the listbox)...and then when they click OK on the
user form... a worksheet (called OT) from my addin file (addin file is
called "Test" for now), will be copied to the activeworkbook "after" the "DS
Details" worksheet ....that the user selected in the Listbox from the
Userform...

I hope that made sense.. it was hard to explain..

I'm not sure if this can be done.....If it can... can someone please explain
how I do this with code...

Any help is always appreciated.
Thanks in advance!
Kimberly

 

[Guest]

This should get you started:

Private Sub UserForm_Initialize()

Dim wks As Worksheet

With UserForm1.ListBox1
For Each wks In Worksheets
If Left(wks.Name, 10) = "DS Details" Then
.AddItem wks.Range("A4").Value
End If
Next wks
End With
ListBox1.ListIndex = 0

End Sub


Private Sub OKButton_Click()

Dim wks As Worksheet
Dim aftersheet As Integer

For Each wks In Worksheets
If wks.Range("A4").Value = ListBox1.Value Then
aftersheet = wks.Index
End If
Next wks

Windows("Test.xls").Activate
Sheets("OT").Move Before:=Workbooks("Book1.xls").Sheets(aftersheet + 1)

Unload UserForm1

End Sub

Regards
Rowan

 

[KimberlyC]

Thanks!
I've got it working!!!

This should get you started:

Private Sub UserForm_Initialize()

Dim wks As Worksheet

With UserForm1.ListBox1
For Each wks In Worksheets
If Left(wks.Name, 10) = "DS Details" Then
.AddItem wks.Range("A4").Value
End If
Next wks
End With
ListBox1.ListIndex = 0

End Sub


Private Sub OKButton_Click()

Dim wks As Worksheet
Dim aftersheet As Integer

For Each wks In Worksheets
If wks.Range("A4").Value = ListBox1.Value Then
aftersheet = wks.Index
End If
Next wks

Windows("Test.xls").Activate
Sheets("OT").Move Before:=Workbooks("Book1.xls").Sheets(aftersheet + 1)

Unload UserForm1

End Sub

Regards
Rowan