RE: Trouble with editing data from a user form
From: Marcotte A (MarcotteA_at_discussions.microsoft.com)
Date: 07/01/04
- Next message: Nigel: "Re: Sort Method question"
- Previous message: NorTor: "Combobox -> to specific row?"
- In reply to: ryssa: "Trouble with editing data from a user form"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 1 Jul 2004 10:12:02 -0700
"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.
- Next message: Nigel: "Re: Sort Method question"
- Previous message: NorTor: "Combobox -> to specific row?"
- In reply to: ryssa: "Trouble with editing data from a user form"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
