Using VB forms to populate and reference excel cells
Using VB forms to populate and reference excel cells
(OP)
Hey,
I know that this may seem like a really stupid question but how do you get a value from a combo box which is on a VB form to go into the excel file that the form is associated to.
For example, a value is selected from a combo box on the VB form. I now want that value to appear in an excel sheet such that i can use it to run calculations etc.
Further to that, how do you then use a text box to look at a field in an excel table and display the value from the excel sheet?
Any help would be greatly appreciated.
Cheers
David Hair
JRa
I know that this may seem like a really stupid question but how do you get a value from a combo box which is on a VB form to go into the excel file that the form is associated to.
For example, a value is selected from a combo box on the VB form. I now want that value to appear in an excel sheet such that i can use it to run calculations etc.
Further to that, how do you then use a text box to look at a field in an excel table and display the value from the excel sheet?
Any help would be greatly appreciated.
Cheers
David Hair
JRa





RE: Using VB forms to populate and reference excel cells
1) Open the Excel spreadsheet
Set DataFileObj = GetObject("C:\DataFile.xls")
2) Put a value into cell A1
DataFileObj.Worksheets(1).Range("A1").Value = CellValue
2) Get a value from cell A1
CellValue = DataFileObj.Worksheets(1).Range("A1").Value
Did that help?
RE: Using VB forms to populate and reference excel cells
Set the ControlSource property to the linked cell address, e.g. "Sheet1!A1"
If you want a list of items from the spreadsheet to display in the ComboBox, set the RowSource property to the range address, e.g. "Sheet1!B1:B20"
Note that you can do this by VB code or by typing the appropriate references in the properties window (press F4) in the VB editor, when you have the userform displayed and the textbox/combobox selected.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.