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

 

[dyarwood]

It is possible but could you provide a little more information and include the code you have so far.

 

[tudor30]

The code below reads across row 10 starting with column A. I would like to read to read Col B and find the "Part Number" and then insert the data from the row the "Part Number" is in. The input textbox is on a multipage form, UserFomr1. The textboxes to place data into are also on UserForm1. I have a pic of the form but I am not sure if there is a way to post it.

I believe I need a step before this to enter the part number, then lookup to find the number to identify the row the data should come from.

Yes, I am new to VBA. So any suggestions are appreciated.
John


Private Sub OptionCodeBox_Change()
Worksheets("Master Tracking&quot.Range("A10&quot.Value = UserForm1.OptionCodeBox
UserForm1.OptionCode.Value = Worksheets("Master Tracking&quot.Range("A10&quot.Value

End Sub

Private Sub PartNumberBox_Change()
Worksheets("Master Tracking&quot.Range("B10&quot.Value = UserForm1.PartNumberBox
UserForm1.PartNumber.Value = Worksheets("Master Tracking&quot.Range("B10&quot.Value

End Sub

Private Sub LetterStateBox_Change()
Worksheets("Master Tracking&quot.Range("C10&quot.Value = UserForm1.LetterStateBox
UserForm1.LetterState.Value = Worksheets("Master Tracking&quot.Range("C10&quot.Value

End Sub

Private Sub PartDescriptionBox_Change()
Worksheets("Master Tracking&quot.Range("D10&quot.Value = UserForm1.PartDescriptionBox
UserForm1.PartDescription.Value = Worksheets("Master Tracking&quot.Range("D10&quot.Value

End Sub

 

[dyarwood]

If you try this
number you want to find is 1


Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

This will activate the cell with 1 in it. Might have to set the matchcase to be true to avoid it finding 1111 etc as well as 1.


Then where you have range("A10&quot change to ActiveCell

and range("B10&quot to ActiveCell.Offset(0,1).

Think that might work for you.

 

[tudor30]

Can the number "1" be a cell value?

For example if I collect the part number and place the value in A1 and then look at A1.

If so how would that appear below?

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

 

[dyarwood]

Do you know the part number is going to be in A1 always? Then do you need to look for it after A1?

Cells.Find(What:=Range("A1&quot, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

The above should search for the value in cell A1.

 

[tudor30]

I declared ...
Public strPartNumber As String

Then inserted it to...
Cells.Find(What:=(strPartNumber), After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

I'm having difficulty passing the values from the row using ActiveCell.Offset to the VB Form.

Should this code be in the worksheet code or in the VB form code? If in the VB form code how do I pass active cell values?

Any suggestions or other info I can offer?

John

 

[tudor30]

Some code I'm working with....

Option Explicit

Public strOptionCode As String
Public strPartNumber As String
Public strLetterState As String
Public strPartDescription As String

Private Sub Submit_Click()

Dim mainform As New UserForm1

strPartNumber = PartNumberS1.Text

Cells.Find(What:=(strPartNumber), After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

ActiveCell.Offset(0, -1) = strOptionCode
ActiveCell.Offset(0, 0) = strPartNumber
ActiveCell.Offset(0, 1) = strLetterState
ActiveCell.Offset(0, 2) = strPartDescription


mainform.Show

strOptionCode = UserForm1.OptionCode.Value
strPartNumber = UserForm1.PartNumber.Value
strLetterState = UserForm1.LetterState.Value
strPartDescription = UserForm1.PartDescription.Value

End Sub

 

[tudor30]

Would it be easier to identify the activecell's location example: B15 and pass B15 to the VB Form code. If so, can the location be identified instead of the value in the cell?

 

[mattdrinks]

I am guessing a bit here, but if this code is trying to read the data from the Row where the part number was found and display it in the controls on the form. Then all you have done is got the assignments round the wrong way.
Try:

Option Explicit

Public strOptionCode As String
Public strPartNumber As String
Public strLetterState As String
Public strPartDescription As String

Private Sub Submit_Click()

Dim mainform As New UserForm1

strPartNumber = PartNumberS1.Text

        Cells.Find(What:=(strPartNumber), After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
        
        strOptionCode = ActiveCell.Offset(0, -1).Value
        strPartNumber = ActiveCell.Offset(0, 0).Value
        strLetterSate = ActiveCell.Offset(0, 1).Value
        strPartDescription = ActiveCell.Offset(0, 2).Value
        
        mainform.Show
          
        UserForm1.OptionCode.Value = strOptionCode
        UserForm1.PartNumber.Value = strPartNumber
        UserForm1.LetterState.Value = strLetterState
        UserForm1.PartDescription.Value = strPartDescription
        
End Sub

Hope that helps,
Matt

 

[dyarwood]

You could get the address of the activecell by using

ActiveCell.Address

Then you can set that as public but try this first

Option Explicit

Public strOptionCode As String
Public strPartNumber As String
Public strLetterState As String
Public strPartDescription As String

Private Sub Submit_Click()

Dim mainform As New UserForm1

strPartNumber = PartNumberS1.Text

Cells.Find(What:=(strPartNumber), After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

ActiveCell.Offset(0, -1) = strOptionCode
ActiveCell.Offset(0, 0) = strPartNumber
ActiveCell.Offset(0, 1) = strLetterState
ActiveCell.Offset(0, 2) = strPartDescription



'This should set all the variables to be used everywhere.


mainform.Show
'The below should pass the data into
'the textboxes
UserForm1.OptionCode.Text = strOptionCode
UserForm1.PartNumber.Text = strPartNumber
UserForm1.LetterState.Text = strLetterState
UserForm1.PartDescription.Text = strPartDescription

End Sub

 

[tudor30]

Run Time Error

Object doesn't support this property or method

First line highlighted in code is:

UserForm1.OptionCode.Value = strOptionCode

 

[dyarwood]

Try it without the refernce to the form
ie

OptionCode.Value = strOptionCode

 

[tudor30]

OptionCode.Value = strOptionCode

Error: Object not defined

Hmmm...any suggestions for passing the value of the celladdress and then using identifying the location of data on the worksheet while in the VB form code?

HEY I'm open for any suggestions.

Thanks to both of you for coming this far!

John

 

[mattdrinks]

John
You could also try

OptionCode.Text = strOptionCode
(as in dyarwood's post)

Also are all the controls on the form referenced by the code Textboxes?

 

[dyarwood]

You could get the address of the activecell by using

ActiveCell.Address

you can either do

Public x Dim String

Sub get_addy()

x = ActiveCell.Address

End Sub

Or

Sub addy()

addy = ActiveCell.Address

End Sub

Then call this sub in your other functions.

 

[tudor30]

Doesn't the public string need to be passed when going from a worksheet or module to VB form code?

In the worksheet:
Public x Dim String

Sub get_addy()
x = ActiveCell.Address
End Sub

In the VB Form code:
Sub OptionCode_Change(x)
Worksheets("Master Tracking&quot.ActiveCell.Range(x).Offset(0, -1) = UserForm1.OptionCode

Is that right?

 

[dyarwood]

I tested it using

Public x Dim String

Sub get_addy()
x = ActiveCell.Address
End Sub


Sub test()
MsgBox(x)
End Sub

The result of running the two gave me a message box with $A$1 (i was in A1) so i think you should not need to call it.

 

[tudor30]

Alright I have $B$13 in the message box, the part number was in B13 and now $B$13 = x

The VB form opens but data is not inserted into the form. How should the value of "x" be identified in the path?

Sub OptionCode1_Change()

Worksheets("Master Tracking&quot.Range(x).Offset(0, -1) = UserForm1.OptionCode

End Sub

 

[mattdrinks]

Ok I think I get what you are trying to do now.
If you want to write the values back to the cells when they are changed on the text boxes, then try using a Range object instead of a string.
eg.

Option Explicit

'ranges instead of strings
Public rngOptionCode As Range
Public rngPartNumber As Range
Public rngLetterState As Range
Public rngPartDescription As Range

Private Sub Submit_Click()

Dim mainform As New UserForm1
Dim strPartNumber
Dim strAddress As String

strPartNumber = PartNumberS1.Text

        Cells.Find(What:=(strPartNumber), After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
        
        'Set ranges
        Set rngOptionCode = ActiveCell.Offset(0, -1)
        Set rngPartNumber = ActiveCell.Offset(0, 0)
        Set rngLetterState = ActiveCell.Offset(0, 1)
        Set rngPartDescription = ActiveCell.Offset(0, 2)
        
        mainform.Show
          
        UserForm1.OptionCode.Text = rngOptionCode.Value
        UserForm1.PartNumber.Text = rngPartNumber.Value
        UserForm1.LetterState.Text = rngLetterState.Value
        UserForm1.PartDescription.Text = rngPartDescription.Value
End Sub

Sub OptionCode_Change()
'To update the cells on the sheet
rngOptionCode.Value = UserForm1.OptionCode
End Sub

You can still copy the data into string varibles aswell if you need to.

strOptionCode = rngOptionCode.Value
strPartNumber = rngPartNumber.Value
strLetterState = rngLetterState.Value
strPartDescription = rngPartDescription

but why not just use rngName.Value to get the string once you have Set the ranges.

Is it easier this way?