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

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

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