passing variables between 2 forms

[burl_rfc]

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 Ogilvy]

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.

 

[burl_rfc_h]

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

 

[Tom Ogilvy]

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.