Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Append existing cell value
I am working with Excel 2003 - I have a worksheet that loads combo and text
boxes on a user form. I need to be able to take the information (changes) a user makes and append the specific combo box text box value with the new value on the worksheet. Code I am using to load the input box from the worksheet is: Dim rng As Range Columns("A:A").Select Selection.AutoFilter With Selection .AutoFilter Field:=1, Criteria1:=frmINPUT.cmbNewProject.Value With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, ..Columns.Count) _ .SpecialCells(xlCellTypeVisible).Cells(1) rng.Select On Error GoTo 0 End With End With ' With ActiveCell With Selection frmINPUT.txtTitle.Value = .Offset(0, 0).Value frmINPUT.cmbType.Value = .Offset(1, 1).Value frmINPUT.cmbOffice.Value = .Offset(1, 13).Value frmINPUT.txtResources.Value = .Offset(1, 126).Value frmINPUT.txtProjStart.Value = .Offset(1, 2).Value frmINPUT.txtProjEnd.Value = .Offset(1, 3).Value frmINPUT.txtActStart.Value = .Offset(1, 4).Value frmINPUT.txtActEnd.Value = .Offset(1, 5).Value frmINPUT.cmbCommander.Value = .Offset(1, 9).Value frmINPUT.cbDeputy.Value = .Offset(1, 128).Value frmINPUT.cmbFlightChief.Value = .Offset(1, 8).Value frmINPUT.txtLead.Value = .Offset(1, 6).Value frmINPUT.txtCoLead.Value = .Offset(1, 11).Value End With this works for loading - am at a loss how to append with user changes and pressing of a SAVE command button. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Append existing cell value
Thanks Nigel - i knew it had to be simple just couldn't make it work. This
helps tremendously. "Nigel" wrote: Hi Your 'Selection' created after applying the autofilter refers to a worksheet range, you should be able to reverse the from 'range'- 'form-control' to write back the data. E.g frmINPUT.cmbType.Value = .Offset(1, 1).Value becomes .Offset(1, 1).Value = frmINPUT.cmbType.Value -- Regards, Nigel "Gnerks" wrote in message ... I am working with Excel 2003 - I have a worksheet that loads combo and text boxes on a user form. I need to be able to take the information (changes) a user makes and append the specific combo box text box value with the new value on the worksheet. Code I am using to load the input box from the worksheet is: Dim rng As Range Columns("A:A").Select Selection.AutoFilter With Selection .AutoFilter Field:=1, Criteria1:=frmINPUT.cmbNewProject.Value With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _ .SpecialCells(xlCellTypeVisible).Cells(1) rng.Select On Error GoTo 0 End With End With ' With ActiveCell With Selection frmINPUT.txtTitle.Value = .Offset(0, 0).Value frmINPUT.cmbType.Value = .Offset(1, 1).Value frmINPUT.cmbOffice.Value = .Offset(1, 13).Value frmINPUT.txtResources.Value = .Offset(1, 126).Value frmINPUT.txtProjStart.Value = .Offset(1, 2).Value frmINPUT.txtProjEnd.Value = .Offset(1, 3).Value frmINPUT.txtActStart.Value = .Offset(1, 4).Value frmINPUT.txtActEnd.Value = .Offset(1, 5).Value frmINPUT.cmbCommander.Value = .Offset(1, 9).Value frmINPUT.cbDeputy.Value = .Offset(1, 128).Value frmINPUT.cmbFlightChief.Value = .Offset(1, 8).Value frmINPUT.txtLead.Value = .Offset(1, 6).Value frmINPUT.txtCoLead.Value = .Offset(1, 11).Value End With this works for loading - am at a loss how to append with user changes and pressing of a SAVE command button. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Append print job to an existing PDF | Excel Programming | |||
Append columns to existing worksheet? | Excel Programming | |||
How do I paste to append instead of replace existing data? | Excel Discussion (Misc queries) | |||
How do I create an excel macro to append to a cell with existing i | Excel Discussion (Misc queries) | |||
How to append existing series? | Charts and Charting in Excel |