Combobox selection to update textboxes dynamically from external workbook

QuestionEdit

QUESTION: Objective: Selection of Combobox item (listsource - sheetnames in externalfile)located in a Userform. Selection should dynamically populate Textboxes with values from selected Combobox item (external sheet tab name). No problem in pulling in external file sheetnames in drop down, but issue with pulling in values into Textboxes from specific static locations in each sheet of the external file, hope I'm clear. Could you perhaps create a simple working example for me that I can assimilate?

'Here's what I have so far:(note this code is in context other code in the Userform)

Private Sub cmbStepOut_Change()

Dim ListItems As Variant, i As Integer Dim SourceWS As Worksheet, SourceWB As Workbook

'Application.ScreenUpdating = False

'file to reference located on my desktop Set SourceWB = Workbooks.Open("C:\Documents and Settings\bjanuary\Desktop\Test Wrap Stepout (NEW).xls", _

           False, True)

 'this is the section I have the problem          

??With Worksheets("SourceWS")

????Textbox14 = .Range("E7").Offset(cmbStepOut.ListIndex)

   Textbox15 = .Range("E10").Offset(cmbStepOut.ListIndex)
   'etc
 End With
         
      SourceWB.Close False ' close the source workbook without saving changes
       Set SourceWB = Nothing
  

' Application.ScreenUpdating = True

' End With

End Sub

'*****************************

'Kind thanks for your input 'Barend

ANSWER: Barend,

Looks like you just need to establish a reference to the sheet to look in. I will assume its name is Sheet1 (change to the actual sheet name).

Private Sub cmbStepOut_Change()

Dim ListItems As Variant, i As Integer Dim SourceWS As Worksheet, SourceWB As Workbook

'Application.ScreenUpdating = False

'file to reference located on my desktop Set SourceWB = Workbooks.Open("C:\Documents and Settings\bjanuary\Desktop\Test Wrap Stepout (NEW).xls", _

          False, True)

'this is the section I have the problem          

set SourceWs = SourceWB.Worksheets("Sheet1")

   Textbox14 = SourceWs.Range("E7").Offset(cmbStepOut.ListIndex)
   Textbox15 = SourceWs.Range("E10").Offset(cmbStepOut.ListIndex)
  'etc

     SourceWB.Close False ' close the source workbook without saving changes
      Set SourceWB = Nothing
 

' Application.ScreenUpdating = True

' End With End Sub

---------- FOLLOW-UP ----------

QUESTION: Thanks for the quick response.

A follow up question. The combo box drop references sheets names in an externalfile located on my desktop as mentioned. However, the dropdown selection should pull in values from the currently selected sheet to the textboxes, not just from sheet1. For example, textbox1 should pull in values from cell(E7) in sheet 1 if sheet1 is selected and if sheet2 is selected, pull values values from cell(E7) in sheet 2 populating textbox1 dynamically. The various sheets in the external workbook are identical to eachother (template worksheet copied to other sheets in the same workbook)

The user updates cells respectively depending on the sheet names. 
 

How can the code be adjusted to accomadate this goal? Thanks, once again Barend

AnswerEdit

Baren,

Private Sub cmbStepOut_Change()

Dim ListItems As Variant, i As Integer Dim SourceWS As Worksheet, SourceWB As Workbook

'Application.ScreenUpdating = False

'file to reference located on my desktop Set SourceWB = Workbooks.Open("C:\Documents and Settings\bjanuary\Desktop\Test Wrap Stepout (NEW).xls", _

         False, True)

'this is the section I have the problem set SourceWs = SourceWB.Activesheet '

  Textbox14 = SourceWs.Range("E7").Offset(cmbStepOut.ListIndex)
  Textbox15 = SourceWs.Range("E10").Offset(cmbStepOut.ListIndex)
 'etc

    SourceWB.Close False ' close the source workbook without saving changes
     Set SourceWB = Nothing

' Application.ScreenUpdating = True

' End With End Sub

If I understand you description, this would use the activesheet for the source workbook. However, since the workbook is opened in code, there isn't much of an opportunity for anyone to make any specific sheet active like you describe the situation. Of course, since it is sample code, perhaps it will be implemented differently.