Userform - listboxes filed from different workbooks

[ogringo]

I've built my (first ever) userform and use it to select any sheet from a list for anyone of 3 workbooks.

It works but while filling the lists obviously goes to the worksheets and thus when you activate the userform from any sheet it jumps to the active sheet in the last selected workbook.

I would like to adjust the code to fill the lists and popup the userform, but without the active sheet changing.

So far I've drawn a blank - any suggestions greatfully received.

Code:

Private Sub UserForm_Initialize()

'Fill the lists with the sheetnames from the different workbooks

' Variable Definition for those used only for UserForm

Dim cSheets As Integer
Dim i1 As Integer

' Calculate Dropdown list for Individual Streamer Workbook

Windows("rms_streamer.xls").Activate

cSheets = Sheets.Count

List_Str_Sheets.Clear

For i1 = 1 To cSheets
List_Str_Sheets.AddItem Sheets(i1).Name
Next
List_Str_Sheets.ListIndex = 1

' Calculate Dropdown list for RMS Channel Workbook

Windows("rms_channel.xls").Activate

cSheets = Sheets.Count

List_Channel_Sheets.Clear

For i1 = 1 To cSheets
List_Channel_Sheets.AddItem Sheets(i1).Name
Next
List_Channel_Sheets.ListIndex = 1

' Calculate Dropdown list for RMS Shot Workbook

Windows("rms_shot.xls").Activate

cSheets = Sheets.Count

List_Shot_Sheets.Clear

For i1 = 1 To cSheets
List_Shot_Sheets.AddItem Sheets(i1).Name
Next
List_Shot_Sheets.ListIndex = 1

End Sub

 

[dk]

Hi,

Here is an example of how you can change the code for List_Str_Sheets. You can use the same technique with the other listboxes:-

Private Sub UserForm_Initialize()

'Fill the lists with the sheetnames from the different workbooks

' Variable Definition for those used only for UserForm

    Dim sht As Worksheet

    ' Calculate Dropdown list for Individual Streamer Workbook


    List_Str_Sheets.Clear

    For Each sht In Workbooks("rms_streamer.xls").Worksheets
        List_Str_Sheets.AddItem sht.Name
    Next sht
    List_Str_Sheets.ListIndex = 1

    'Rest of your code
    '
    '
    '
End Sub

 

[ogringo]

Thanks Dan, excellent - works a treat.


The problem I find with starting this all off is that each new thing I learn throws up 5 questions that appear to have no answer!!



Dave