Help with a Form function please

[Peter]

Hi,

I have, with the kind help of people in this group, created a form
from which I can download info into a spreadsheet.

Each entry is contained in a row of about fifteen columns - what I
want to do now is by using the unique entry in column A of each line
recall a particular line to the form so a final data entry may be made
- Can anyone please point me towards a solution for this?

--
Cheers

Peter

Remove the INVALID to reply

 

[Bob Phillips]

What is the worksheet data like, and where in the form do you want to put
it?

 

[Tom Ogilvy]

Assume you populate Combobox1 with the users choice

and you are writing you data to Sheet1 starting in Cell A2

Private Sub combobox1_Click()
dim res as Variant, rng as Range, rng1 as Range
With worksheets("sheet1")
set rng = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
End with
if rng(1).row = 1 then exit sub
res = application.Match(combobox1.Value,rng,0)
if iserror(res) then
MsgBox "Invalid entry"
exit sub
Else
set rng1 = rng(res)
Textbox1.Value = rng1.offset(0,1).Value
Textbox2.Value = rng1.offset(0,2).Value
. . .
End If
End Sub

The above pseudo code represents a general approach.

 

[Peter]

What is the worksheet data like, and where in the form do you want to put
it?

Hi,


What I have is data in columns A - L. The data is in the form of text,
e.g. A1, B1, C1, will have text, D1 will have a date (dd/mmm/yy -
hopefully, but that will be a later question!) etc.

All the cells in column A will have an unique entry - something along
the lines of BDE001 or WNJ034. At some point I will need to add data,
again in the form of text and dates, to columns M - W of a particular
row. E.g. I will want to make my form appear via a macro and either
add new data to a new row, or alternatively add data to an already
existing row - say row 12 which is uniquely identified by having
BDE001 in cell A12. I see now that it isn't necessary to have the form
show what has already been entered in row 12, columns A - L, but I do
want to be able to use maybe a combo box and select BDE001, enter the
additional data into the form and have the form write this to cells M
- W of row 12.

Possibly not the clearest way of explaining what I'm hoping to
achieve, but can't think of a better way of describing it.

--
Cheers

Peter

Remove the INVALID to reply