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.

Append existing cell value
 

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.

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.