passing variables between 2 forms

in the following code, i'm collecting a range of data from a worksheet,


With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "12;0" 'hide the second column
.Clear
Set SourceWB = Workbooks.Open("Z:\DT\DT Common\DT Quote
Models\DT Quote Log.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

this data is then displayed in a user form, using the following code

With Me.ComboBox1
If .ListIndex > -1 Then
myVar = .List(.ListIndex, 1) '<-- second column!
MsgBox myVar 'for testing only
Select Case myVar
Case Is = "Metals"
frmMetalsQuoteForm.Show
Case Is = "Glass"
'test
End Select
End If
End With

the issue i have is that the data is not displayed in the user form
frmMetalsQuoteForm, do I need to do something to pass this value along
to the form?

thanks
burl_rfc

this code should load the data in combobox1 (assume it is on a userform)

With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "12;0" 'hide the second column
.Clear
Set SourceWB = Workbooks.Open("Z:\DT\DT Common\DT Quote
Models\DT Quote Log.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

The second bit of code you show should be using the data that is already
there.

If the code is running at the proper time, I would expect the combobox to
have the data.

--
Regards,
Tom Ogilvy


"burl_rfc" wrote:

> in the following code, i'm collecting a range of data from a worksheet,
>
>
> With Me.ComboBox1
> .ColumnCount = 2
> .ColumnWidths = "12;0" 'hide the second column
> .Clear
> Set SourceWB = Workbooks.Open("Z:\DT\DT Common\DT Quote
> Models\DT Quote Log.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
>
> this data is then displayed in a user form, using the following code
>
> With Me.ComboBox1
> If .ListIndex > -1 Then
> myVar = .List(.ListIndex, 1) '<-- second column!
> MsgBox myVar 'for testing only
> Select Case myVar
> Case Is = "Metals"
> frmMetalsQuoteForm.Show
> Case Is = "Glass"
> 'test
> End Select
> End If
> End With
>
> the issue i have is that the data is not displayed in the user form
> frmMetalsQuoteForm, do I need to do something to pass this value along
> to the form?
>
> thanks
> burl_rfc
>
>

Tom,

Thanks for your reply, the combo box does load correctly with the data
from the source worksheet, onto the first form, but what i need to do
is pass along the variables to another form. Just to refresh myself
I'll explain further.
The first block of code works great, the data is collected from the
source worksheet.
The second block of code displays the combo box on a user form, the
combo box is indead populated with the data from the myRng variable.
Once I've selected the record of interest from the combo box it does
display the product type in the msgbox using myVar.
Now heres the fun part, if I were to expand the myRng to "A3:Z" to get
more data, how should I pass along the data from the same row on the
source workbook to another form, in some cases I will not need all the
data from column A to Z, the data I want will depend upon the product
type, then the product type will determine which form to load. I tryed
the following code and it didn't put any data onto the user form

Let's say that I selected 123456 as a part number from the combo box,
for arguments sake let's say it was in cell A5 in the source workbook,
now the cell adjacent has a product code of Metals in B5, now the
Metals product code triggers the user form "MetalsQuoteForm" to show
(Select Case coding), I then need to populate many text boxes, firstly
with the part number from the combo box selection, then the product
code then many other cells from the same row.
Perhaps I'm going about this completely wrong maybe I should take the
data in myRng and place this into a new workbook, then depending upon
the product type open the corresponding user form and populate the text
books from the new workbook.

Private Sub Userform_Initialize() 'Metals Quote Form
myVar1 = .List(.ListIndex, 0) ' column A data from source workbook
myVar2 = .List(.Listindex, 1) 'column B data from source workbook
myVar3 = .List(.Listindex, 5) 'column E data from source workbook
myVar4 = .List(.ListIndex, 8) 'column H data from source workbook


frmMetalQuoteForm.txtQuote.Value = myVar1
frmMetalQuoteForm.txtPartNo.Value = myVar2
frmMetalQuoteForm.txtCustomer.Value = myVar3
frmMetalQuoteForm.txtSaleperson.Value = myVar4


End Sub

thanks
burl_rfc_h

Private Sub Userform_Initialize() 'Metals Quote Form
myVar1 = .List(.ListIndex, 0) ' column A data from source workbook
myVar2 = .List(.Listindex, 1) 'column B data from source workbook
myVar3 = .List(.Listindex, 5) 'column E data from source workbook
myVar4 = .List(.ListIndex, 8) 'column H data from source workbook

shoud raise an error for the reason previously stated.



--
Regards,
Tom Ogilvy

"burl_rfc_h" <milliela@connecttime.net> wrote in message
news:1144980708.864137.133630@i40g2000cwc.googlegroups.com...
> Tom,
>
> Thanks for your reply, the combo box does load correctly with the data
> from the source worksheet, onto the first form, but what i need to do
> is pass along the variables to another form. Just to refresh myself
> I'll explain further.
> The first block of code works great, the data is collected from the
> source worksheet.
> The second block of code displays the combo box on a user form, the
> combo box is indead populated with the data from the myRng variable.
> Once I've selected the record of interest from the combo box it does
> display the product type in the msgbox using myVar.
> Now heres the fun part, if I were to expand the myRng to "A3:Z" to get
> more data, how should I pass along the data from the same row on the
> source workbook to another form, in some cases I will not need all the
> data from column A to Z, the data I want will depend upon the product
> type, then the product type will determine which form to load. I tryed
> the following code and it didn't put any data onto the user form
>
> Let's say that I selected 123456 as a part number from the combo box,
> for arguments sake let's say it was in cell A5 in the source workbook,
> now the cell adjacent has a product code of Metals in B5, now the
> Metals product code triggers the user form "MetalsQuoteForm" to show
> (Select Case coding), I then need to populate many text boxes, firstly
> with the part number from the combo box selection, then the product
> code then many other cells from the same row.
> Perhaps I'm going about this completely wrong maybe I should take the
> data in myRng and place this into a new workbook, then depending upon
> the product type open the corresponding user form and populate the text
> books from the new workbook.
>
> Private Sub Userform_Initialize() 'Metals Quote Form
> myVar1 = .List(.ListIndex, 0) ' column A data from source workbook
> myVar2 = .List(.Listindex, 1) 'column B data from source workbook
> myVar3 = .List(.Listindex, 5) 'column E data from source workbook
> myVar4 = .List(.ListIndex, 8) 'column H data from source workbook
>
>
> frmMetalQuoteForm.txtQuote.Value = myVar1
> frmMetalQuoteForm.txtPartNo.Value = myVar2
> frmMetalQuoteForm.txtCustomer.Value = myVar3
> frmMetalQuoteForm.txtSaleperson.Value = myVar4
>
>
> End Sub
>
> thanks
> burl_rfc_h
>