Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing variables between 2 forms
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing variables between 2 forms
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing variables between 2 forms
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" wrote in message oups.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Variables | Excel Discussion (Misc queries) | |||
Passing variables between forms | Excel Programming | |||
passing variables | Excel Programming | |||
Passing Variables | Excel Programming | |||
Passing Variables | Excel Programming |