I'm afraid I may have bitten of more than I can chew.....

I created a user form whereby a combo box is used to list all available
records in column A on a workbook (listing starts from A3 to last
record in column A), the appropriate part number in this case, is then
selected from the combo box, the record adjacent to the part number
selected (in Column B) is then looked at to see what type of product
code the part number is.
Then I want to initialize a user form for that product type and load it
with data from the same row the part number came from, so the
difficulty I'm experiencing currently is the macro first runs the
Private Sub Userform_Initialize() seen below, it permits me to select a
part number from the combo box. Then I press a command button to run
the Private Sub CommandButton1_Click() macro, it fails on the Call
rmMetalQuoteForm line, I get a compile error, invalid property. How can
I initialze this form and then populate the text boxes.

To populate the text boxes see the third sub routine listed below,
would the listindex be still valid even though it was called from a
different sub routine, or am I going in the wrong direction?

If any one can help me out it would be greatly appreciated.

--------------------------------------------------------------------------
this allows the user to select the part no.

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 LCase(myVar)
Case Is = "Metal"
Call frmMetalQuoteForm
Case Is = "Glass"
Call frmGlassQuoteForm
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

Just a couple of ideas:

To "call" a userform, don't use Call but
frmMetalQuoteForm.Show

Before that though, you should make the changes you need to the userform,
e.g. populate the combo box with some lines before the Show line.

Also, I can't remember if Case Is is case-sensitive but you probably
shouldn't be comparing LCase (lower case) with proper case (Metal).

Good luck - hopefully someone else will have more time to go through your
code a bit more carefully.

"burl_rfc" wrote:

I'm afraid I may have bitten of more than I can chew.....

I created a user form whereby a combo box is used to list all available
records in column A on a workbook (listing starts from A3 to last
record in column A), the appropriate part number in this case, is then
selected from the combo box, the record adjacent to the part number
selected (in Column B) is then looked at to see what type of product
code the part number is.
Then I want to initialize a user form for that product type and load it
with data from the same row the part number came from, so the
difficulty I'm experiencing currently is the macro first runs the
Private Sub Userform_Initialize() seen below, it permits me to select a
part number from the combo box. Then I press a command button to run
the Private Sub CommandButton1_Click() macro, it fails on the Call
rmMetalQuoteForm line, I get a compile error, invalid property. How can
I initialze this form and then populate the text boxes.

To populate the text boxes see the third sub routine listed below,
would the listindex be still valid even though it was called from a
different sub routine, or am I going in the wrong direction?

If any one can help me out it would be greatly appreciated.

--------------------------------------------------------------------------
this allows the user to select the part no.

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 LCase(myVar)
Case Is = "Metal"
Call frmMetalQuoteForm
Case Is = "Glass"
Call frmGlassQuoteForm
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

Martin,

Thanks for your reply, I have gotten a little further thanksd to your
help, I'm no able to at least show the last form, but am unable to
populate the text boxes, I'm feeling that the list index must lose it's
focus or the variables are not public. perhaps if i could declare the
the cells required to populate the last form just before i run the
frmMetalQuoteForm.Show it may work better, per example below.


'Option Explicit


Dim myVar, myVar1, myVar2 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 LCase(myVar)
Case Is = "Metal"
myVar1 = .list(.listindex, 2)
myVar2 = .list(.listindex, 3)
' the rest of the data
.....
.....
Call frmMetalQuoteForm
Case Is = "Glass"
Call frmGlassQuoteForm
End Select
End If
End With
End Sub


Thanks
burl_rfc