How do I get a set of values and insert them into a VB Form

[tudor30]

I know I can use the ControlSource Property in VB Userform to get a value from a cell on the spreadsheet.

What I would like to do is have the code look through column A,find a specific NUMBER, and then place the set of data into the VB Form.

Is this possible to do?

Thanks for any assistance,
John

 

[tudor30]

I've tried the suggestions but the code seems to stop everytime the vb form is opened.

Would it help to view the excel file to show where the code is?

John

 

[mattdrinks]

John,
I am not sure what you mean by

The code seems to stop everytime the vb form is openned

I am guessing that you have one userform with a Submit button on it, and that the user types the Part Number into a textbox on that form and then clicks the Submit button, which runs the code to load the data from the sheet. Then if the user changes any data on the form it is written back to the sheet

I am assuming that all the code is in the form's code module.

Some Questions.
What code is meant to be running when the form is open?
Does the user change data on the sheet while the form is open?
Could you have a Save button on the sheet instead of updating the data on each controls Change event?
Which version of Excel are you using?

I'm sorry I am a bit lost.
Matt

 

[waynerenaud]

Why not lust put a listbox on your form, with the following properties.
set the rowsource to your data range ie A1:F100
set the textcolumn to 2 so it is looking for a match in column B of your data
set the columncount to however many columns you need to show (ie 6 if you want to show all columns for A:F (your data range))
set matchentry to 1 (matches each character as you type)
set the focus to that property when you open the userform Private Sub UserForm_Activate()
ListBox1.SetFocus
End Sub
then just start typing whatever you want to search for.

Or have I completely missed the plot here ??

 

[tudor30]

Here's the code that worked for me.
Thanks for all the suggestions.
john


Private Sub SubmitMainChanges_Click()
Dim c As Range

With Sheets("Master Tracking&quot.Range("B1:B1000&quot
Set c = .Find(PartNumberBox.Value, LookIn:=xlValues)

c.Offset(0, -1).Value = OptionCodeBox.Value
c.Offset(0, 0).Value = PartNumber.Value
c.Offset(0, 1).Value = LetterStateBox.Value
c.Offset(0, 2).Value = PartDescriptionBox.Value
c.Offset(0, 3).Value = PiecesPerEngineBox.Value
c.Offset(0, 4).Value = RefPartNumberBox.Value

End With

End Sub