Trouble with editing data from a user form
 

Hello all,
I am having trouble with editing a record with a user form.
I currently have an entry form which works with a save button to ente
records into a spreadsheet which goes like this:

Private Sub cmdSave_Click()

'Select workbook & move selection to cell A1
ActiveWorkbook.Sheets("Contacts").Activate
Range("A1").Select

'Move selection down until it finds an empty cell
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

'Write contents of form onto worksheet
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = txtDesignation.Value
ActiveCell.Offset(0, 2) = txtCompany.Value
ActiveCell.Offset(0, 3) = txtAddress.Value
ActiveCell.Offset(0, 4) = txtPhone.Value
ActiveCell.Offset(0, 5) = txtCellPhone.Value
ActiveCell.Offset(0, 6) = txtFax.Value
ActiveCell.Offset(0, 7) = txtEmail.Value
ActiveCell.Offset(0, 8) = txtEmailRefNo.Value
ActiveCell.Offset(0, 11) = txtWebsite.Value
ActiveCell.Offset(0, 9) = cboType.Value
ActiveCell.Offset(0, 10) = cboIndustry.Value
ActiveCell.Offset(0, 12) = cboFestival.Value
ActiveCell.Offset(0, 13) = txtProject.Value
ActiveCell.Offset(0, 16) = txtSFGAccountStatus.Value
ActiveCell.Offset(0, 15) = cboEIC.Value
ActiveCell.Offset(0, 14) = txtRemarks.Value

'Move selection back to beginning of worksheet for a new entry
Range("A1").Select
Call UserForm_Initialize
ActiveWorkbook.Save
End Sub

I have an edit button on the worksheet which opens a form to ask th
user for the row number to be edited. It then opens another form fo
the user to edit that record.

What I do not know how to do is how to get the information from tha
row in the sheet into the textboxes in the form for the user to edi
and save the new version of the record into the same row in the sheet.

Any help is greatly appreciated, thanks!

ryss

--
Message posted from http://www.ExcelForum.com

Trouble with editing data from a user form
 

"ryssa " wrote:

Hello all,
I am having trouble with editing a record with a user form.
I currently have an entry form which works with a save button to enter
records into a spreadsheet which goes like this:

Private Sub cmdSave_Click()

'Select workbook & move selection to cell A1
ActiveWorkbook.Sheets("Contacts").Activate
Range("A1").Select

'Move selection down until it finds an empty cell
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

'Write contents of form onto worksheet
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = txtDesignation.Value
ActiveCell.Offset(0, 2) = txtCompany.Value
ActiveCell.Offset(0, 3) = txtAddress.Value
ActiveCell.Offset(0, 4) = txtPhone.Value
ActiveCell.Offset(0, 5) = txtCellPhone.Value
ActiveCell.Offset(0, 6) = txtFax.Value
ActiveCell.Offset(0, 7) = txtEmail.Value
ActiveCell.Offset(0, 8) = txtEmailRefNo.Value
ActiveCell.Offset(0, 11) = txtWebsite.Value
ActiveCell.Offset(0, 9) = cboType.Value
ActiveCell.Offset(0, 10) = cboIndustry.Value
ActiveCell.Offset(0, 12) = cboFestival.Value
ActiveCell.Offset(0, 13) = txtProject.Value
ActiveCell.Offset(0, 16) = txtSFGAccountStatus.Value
ActiveCell.Offset(0, 15) = cboEIC.Value
ActiveCell.Offset(0, 14) = txtRemarks.Value

'Move selection back to beginning of worksheet for a new entry
Range("A1").Select
Call UserForm_Initialize
ActiveWorkbook.Save
End Sub

I have an edit button on the worksheet which opens a form to ask the
user for the row number to be edited. It then opens another form for
the user to edit that record.

What I do not know how to do is how to get the information from that
row in the sheet into the textboxes in the form for the user to edit
and save the new version of the record into the same row in the sheet.

Any help is greatly appreciated, thanks!

ryssa

You could store the row number in a Global variable (EditRow), then in the initialize event of the edit userform do:

txtDesignation.Value = cells(EditRow, 1).value
txtCompany.Value = cells(EditRow, 2).Value
etc.

This should load the preexisting data into the Userform. Then do something similar to your code above to save it to the worksheet.