Using userforms to edit cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 here:

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


--
Jim
|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
 
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
 
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.
 
Back
Top