Get data from sheet to form and back to a different sheet




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)


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! :)


Dave Peterson

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 =
.cells(nextrow,"B").value =
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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question