Solved: get cell data into textbox on userform

[dgraham62]

Hi,

I am trying to get row data into textboxes on a userform, when I enter a number in textbox1, it should look up the number in column A then put the correstponding data from Columns B & C into textbox2 & textbox3. Is this possible?

Regards

David

[royUK]

Use the Find function to find the number like this


Option Explicit

Private Sub CommandButton1_Click()
Dim rng As Range
Dim cl As Range
Dim vFind
Dim R As Long

Set rng = Sheet1.Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

vFind = Me.TextBox1.Text
With rng
Set cl = .Find(vFind, LookIn:=xlValues)
If Not cl Is Nothing Then R = cl.Row
Me.TextBox2.Value = Cells(R, 2).Value
Me.TextBox3.Value = Cells(R, 3).Value
End With
End Sub

[dgraham62]

Thanks royUK,

That what I was looking for. Am I able to select the cell if the number is found, as I will either need to delete the found cell or edit it.

Regards

David

[RichardSchollar]

Hi David

In Roy's code, c1 is set to be a reference to the found value, so if you wanted to actually modify that cell, you could simply use the reference eg:


'code snippet...
c1.ClearContents 'delete contents of the cell
'or:
c1.Value = "SomeOtherValue" 'change contents of c1

Make sense?

Richard

[dgraham62]

Thanks Richard,

I wish it did make sense :) , I'm just a novice at VBA I'm afraid. I've used the code to select the cl cell in column A, are you able to tell me how to also select the corresponding cells in column C & D ie... if A26 is selected I also would like C26 & D26 selected (Not column B as this has a formula in it and dont want it deleted).

Regards

David

[RichardSchollar]

David

Assuming you have your reference c1 and it refers to the cell in the A column, to clear the contents in both C & D then you could use the Offset command to target the correct cells:


c1.Offset(0,2).Resize(1,2).ClearContents

What this is doing is starting with c1 (which we know is your A column cell), offsetting by 2 columns to the right (and no rows) so we get to the corresponding C cell, then resizing this single cell to one row deep (so no changes here) and 2 cells' wide (ie C & D are covered), then we are clearing them of their contents.

I hope this was easy enough to follow.

Richard

[dgraham62]

Thanks Richard,

Works a treat now.

Regards

David