Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write data back from a form to sheet | Excel Discussion (Misc queries) | |||
Info from User Form to Data sheet and back | Excel Programming | |||
Can I switch back to Excel sheet while a form is shown? | Excel Programming | |||
From form to sheet and back using proc | Excel Programming | |||
How to set focus back to sheet after showing a user form | Excel Programming |