In the following code I'm trying to pass a variable value from one form
to another another form using the list index function, first off can
this be done?

Ultimately I need to populate the second form with data from the same
row in the worksheet that the first form looked at. I was hoping to
simply use the offset command to select the appropriate cells in the
worksheet to populate all the text boxes in the second user form.

please see the following code, the code runs fine it dispays the first
user form, allows selection of a part no, the msg box displays the
appropriate product code, the last user form is shown depending upon
the product code, but the text boxes are empty, nothing shown. Whats
going wrong. Does the list index lose it's focus when passing from one
form to another, how can this be overcome.

Thanks
burl_rfc

--------------------------------------------------------------------------

this allows the user to select the part no. using part no. form


Private Sub Userform_Initialize() 'Get Part Number Form
Dim SourceWB As Workbook
Dim myRng As Range


With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "12;0" 'hide the second column
.Clear
Set SourceWB = Workbooks.Open("C:\MyFolder\MyWorkbook.xls",
False, True)
With SourceWB.Worksheets(1)
Set myRng = .Range("A3:B" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
End With
.List = myRng.Value
SourceWB.Close False
End With
End Sub


-------------------------------------------------------------------------

this sub will re-direct the user to the appropriate user form depending

upon the product code
in the adjacent cell to the part number (same row but in column B)


'Option Explicit


Dim myVar As Variant 'String/Long/double???


With Me.ComboBox1
If .ListIndex -1 Then
myVar = .List(.ListIndex, 1) '<-- second column!
MsgBox myVar 'for testing only
Select Case myVar
Case Is = "Metal"
frmMetalQuoteForm.Show
Case Is = "Glass"
frmGlassQuoteForm.Show
End Select
End If
End With
End Sub


---------------------------------------------------------------------------*--

this is one of the forms that requires loading with data


Private Sub Userform_Initialize() 'Metals Quote Form
myVar1 = .List(.ListIndex, 0)
myVar2 = .List(.Listindex, 1)
myVar3 = .List(.Listindex, 2)


frmMetalQuoteForm.txtQuote.Value = myVar1
frmMetalQuoteForm.txtQuote.Value = myVar2
frmMetalQuoteForm.txtQuote.Value = myVar3


End Sub

Private Sub Userform_Initialize() 'Metals Quote Form

with NameofFirstForm.Combobox1
myVar1 = .List(.ListIndex, 0)
myVar2 = .List(.Listindex, 1)
' the list only has two columns, so next
' line is a no go
'myVar3 = .List(.Listindex, 2)
End With

frmMetalQuoteForm.txtQuote.Value = myVar1
frmMetalQuoteForm.txtQuote.Value = myVar2
frmMetalQuoteForm.txtQuote.Value = myVar3


End Sub


--
Regards,
Tom Ogilvy

"burl_rfc" wrote in message
oups.com...
In the following code I'm trying to pass a variable value from one form
to another another form using the list index function, first off can
this be done?

Ultimately I need to populate the second form with data from the same
row in the worksheet that the first form looked at. I was hoping to
simply use the offset command to select the appropriate cells in the
worksheet to populate all the text boxes in the second user form.

please see the following code, the code runs fine it dispays the first
user form, allows selection of a part no, the msg box displays the
appropriate product code, the last user form is shown depending upon
the product code, but the text boxes are empty, nothing shown. Whats
going wrong. Does the list index lose it's focus when passing from one
form to another, how can this be overcome.

Thanks
burl_rfc

--------------------------------------------------------------------------

this allows the user to select the part no. using part no. form


Private Sub Userform_Initialize() 'Get Part Number Form
Dim SourceWB As Workbook
Dim myRng As Range


With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "12;0" 'hide the second column
.Clear
Set SourceWB = Workbooks.Open("C:\MyFolder\MyWorkbook.xls",
False, True)
With SourceWB.Worksheets(1)
Set myRng = .Range("A3:B" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
End With
.List = myRng.Value
SourceWB.Close False
End With
End Sub


-------------------------------------------------------------------------

this sub will re-direct the user to the appropriate user form depending

upon the product code
in the adjacent cell to the part number (same row but in column B)


'Option Explicit


Dim myVar As Variant 'String/Long/double???


With Me.ComboBox1
If .ListIndex -1 Then
myVar = .List(.ListIndex, 1) '<-- second column!
MsgBox myVar 'for testing only
Select Case myVar
Case Is = "Metal"
frmMetalQuoteForm.Show
Case Is = "Glass"
frmGlassQuoteForm.Show
End Select
End If
End With
End Sub


---------------------------------------------------------------------------*
--

this is one of the forms that requires loading with data


Private Sub Userform_Initialize() 'Metals Quote Form
myVar1 = .List(.ListIndex, 0)
myVar2 = .List(.Listindex, 1)
myVar3 = .List(.Listindex, 2)


frmMetalQuoteForm.txtQuote.Value = myVar1
frmMetalQuoteForm.txtQuote.Value = myVar2
frmMetalQuoteForm.txtQuote.Value = myVar3


End Sub