Get data from sheet to form and back to a different sheet
I would qualify those ranges:
' Get the data from the spreadsheet to the form
with activesheet
ContactForm.Company = .Cells(ActiveCell.Row, 2).value
ContactForm.Contact = .Cells(ActiveCell.Row, 4).value
ContactForm.Address = .Cells(ActiveCell.Row, 5).value
ContactForm.City = .Cells(ActiveCell.Row, 7).value
end with
And to put it back...
Dim NextRow as long
with worksheets("Contacts")
'based on the data in column A of Contacts
nextrow = .cells(.rows.count,"A").end(xlup).row + 1
.cells(nextrow,"A").value = contactform.company
.cells(nextrow,"B").value = contactform.contact
...
end with
If all this code is in the module associated with the userform, I'd change all
the ContactForm references to Me.
Me is a keyword that refers to the object that owns the code--in this case, that
would be the ContactForm itself.
Dan wrote:
Hello,
I'm new to VBA. I'm using VBA within Excel 2007.
I am building a workbook to record customer contacts. The customers are
listed on a sheet, the user scrolls around on the sheet and calls the form
with <Shift<CTRL<C when they find a customer they want to contact.
The module for the macro starts out like this:
' Get the data from the spreadsheet to the form
ContactForm.Company = Cells(ActiveCell.Row, 2)
ContactForm.Contact = Cells(ActiveCell.Row, 4)
ContactForm.Address = Cells(ActiveCell.Row, 5)
ContactForm.City = Cells(ActiveCell.Row, 7)
...
ContactForm.Show
Then, when the user clicks on an "AddAttempt" command button on the form,
I'd like to record the form values in the 'next' row in a DIFFERENT sheet
(named 'Contacts')
I can get the data to the form, but am struggling with getting the form data
back to a different worksheet.
Any help with the syntax to do this would be appreciated! :)
--Dan
--
Dave Peterson
|