View Full Version : Solved: get cell data into textbox on userform
dgraham62
12-01-2007, 12:27 AM
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
12-01-2007, 04:06 AM
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
12-01-2007, 05:08 AM
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
12-01-2007, 10:34 AM
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
12-01-2007, 08:17 PM
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
12-02-2007, 01:34 AM
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
12-03-2007, 11:03 PM
Thanks Richard,
Works a treat now.
Regards
David
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.