Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
form that retrieves existing data from table and allows editing | Excel Discussion (Misc queries) | |||
trouble editing axis labels | Charts and Charting in Excel | |||
Trouble with saving user input in an Excel Form. | Excel Discussion (Misc queries) | |||
Textbox trouble on a user form | Excel Programming | |||
User form trouble | Excel Programming |