I posted a similar question some time ago but couldn't get things work.

I have a sheet with loads of data in and I wish to create a userform to edit
the data that is already in the sheet. I want the userform to scroll though
the records to one that I want, thus enabling me to edit the data. I have
already set up the userform to input the data, but the facility to edit has
now arisen.

Can anyone help.

PS I'm not an experienced programmer, just someone who uses it now and again.

Tom

Consider using the built-in Data, Form feature. Assign the name "database"
to your data range first.

John Walkenbach has an enhanced version of this you might look at. The
fourth item he

http://www.j-walk.com/ss/excel/files/general.htm


--
Jim
"Tom" (no spam ha ha) wrote in message
...
|I posted a similar question some time ago but couldn't get things work.
|
| I have a sheet with loads of data in and I wish to create a userform to
edit
| the data that is already in the sheet. I want the userform to scroll
though
| the records to one that I want, thus enabling me to edit the data. I have
| already set up the userform to input the data, but the facility to edit
has
| now arisen.
|
| Can anyone help.
|
| PS I'm not an experienced programmer, just someone who uses it now and
again.
|
| Tom

Hi there

You could also try using something like the code below which will
search the named worksheet for the value entered in textbox1 if it is
found it will show the details of the adjacent cells in textbox2 and
textbox3.

Once you have the data shown in your form you can then edit it and use
the code for the second button to put the values back into the
worksheet.

This should work OK providing you now what you are searching for and
that each reference is unique. If your data doesn't fall under that
umbrella then the code should be modified easily enough to allow for
this, in theory. It's probably not the cleanest way to do it but it
will work providing the conditions are right.

Option Explicit
Dim FCell
Dim ImLookingFor As String

Private Sub CommandButton1_Click()

On Error Resume Next 'Set this to trap the reference not being found

ImLookingFor = TextBox1.Value

If ImLookingFor < "" Then 'Ensure something has been entered to look
for

With Sheets("Sheet1") 'change this to the name of your sheet

Set FCell = Cells.Find(What:=ImLookingFor, LookAt:=xlWhole)

If FCell Is Nothing Then 'If you don't find what your
looking for

MsgBox ImLookingFor & " not found in Last Tab!"

Resume Next

Else 'If you do find what your looking for

FCell.Activate

TextBox2.Value = ActiveCell.Offset(0, 1).Value

TextBox3.Value = ActiveCell.Offset(0, 2).Value

End If

End With

End If

End Sub

Private Sub CommandButton2_Click()

ActiveCell.Value = TextBox1.Value 'Put the values to the cells

ActiveCell.Offset(0, 1).Value = TextBox2.Value

ActiveCell.Offset(0, 2).Value = TextBox3.Value

End Sub

Hope this helps you out

S

Is there any way of amending the Form that comes with Excel. I would like to
alter the layout and add some other buttons.

Tom

"Incidental" wrote:

Hi there

You could also try using something like the code below which will
search the named worksheet for the value entered in textbox1 if it is
found it will show the details of the adjacent cells in textbox2 and
textbox3.

Once you have the data shown in your form you can then edit it and use
the code for the second button to put the values back into the
worksheet.

This should work OK providing you now what you are searching for and
that each reference is unique. If your data doesn't fall under that
umbrella then the code should be modified easily enough to allow for
this, in theory. It's probably not the cleanest way to do it but it
will work providing the conditions are right.

Option Explicit
Dim FCell
Dim ImLookingFor As String

Private Sub CommandButton1_Click()

On Error Resume Next 'Set this to trap the reference not being found

ImLookingFor = TextBox1.Value

If ImLookingFor < "" Then 'Ensure something has been entered to look
for

With Sheets("Sheet1") 'change this to the name of your sheet

Set FCell = Cells.Find(What:=ImLookingFor, LookAt:=xlWhole)

If FCell Is Nothing Then 'If you don't find what your
looking for

MsgBox ImLookingFor & " not found in Last Tab!"

Resume Next

Else 'If you do find what your looking for

FCell.Activate

TextBox2.Value = ActiveCell.Offset(0, 1).Value

TextBox3.Value = ActiveCell.Offset(0, 2).Value

End If

End With

End If

End Sub

Private Sub CommandButton2_Click()

ActiveCell.Value = TextBox1.Value 'Put the values to the cells

ActiveCell.Offset(0, 1).Value = TextBox2.Value

ActiveCell.Offset(0, 2).Value = TextBox3.Value

End Sub

Hope this helps you out

S

Is there any way of aletering the look of the standard form in excel, eg add
a OK?cancel button or move the text boxes?

Tom

"Jim Rech" wrote:

Consider using the built-in Data, Form feature. Assign the name "database"
to your data range first.

John Walkenbach has an enhanced version of this you might look at. The
fourth item he

http://www.j-walk.com/ss/excel/files/general.htm


--
Jim
"Tom" (no spam ha ha) wrote in message
...
|I posted a similar question some time ago but couldn't get things work.
|
| I have a sheet with loads of data in and I wish to create a userform to
edit
| the data that is already in the sheet. I want the userform to scroll
though
| the records to one that I want, thus enabling me to edit the data. I have
| already set up the userform to input the data, but the facility to edit
has
| now arisen.
|
| Can anyone help.
|
| PS I'm not an experienced programmer, just someone who uses it now and
again.
|
| Tom

Not the builtin version.

But John Walkenbach does provide access to the code ($20 US, IIRC). Then you
can do as much damage <vbg as you want.

Tom wrote:

Is there any way of aletering the look of the standard form in excel, eg add
a OK?cancel button or move the text boxes?

Tom

"Jim Rech" wrote:

Consider using the built-in Data, Form feature. Assign the name "database"
to your data range first.

John Walkenbach has an enhanced version of this you might look at. The
fourth item he

http://www.j-walk.com/ss/excel/files/general.htm


--
Jim
"Tom" (no spam ha ha) wrote in message
...
|I posted a similar question some time ago but couldn't get things work.
|
| I have a sheet with loads of data in and I wish to create a userform to
edit
| the data that is already in the sheet. I want the userform to scroll
though
| the records to one that I want, thus enabling me to edit the data. I have
| already set up the userform to input the data, but the facility to edit
has
| now arisen.
|
| Can anyone help.
|
| PS I'm not an experienced programmer, just someone who uses it now and
again.
|
| Tom




--

Dave Peterson