Hey keizi

Thanks for the reply.

Also thanks for the description of your test workbook, I had to many
excess controls and other stuff on my UserForm to give it out as a
sample. Yours will do the job with a much smaller file size.

I could not get your sample to work until I expanded it to include the
ComboBox RowSource loading. Here is what I came up with. It still
shows what the problem is (I was hoping that by simplifying the code
the problem would go away - no such luck)

I set up Sheet1 with a header row in A1:C1, containing these three
headings:

| Textbox 1 | Textbox 2 | Textbox 3 |

And ComandButton1 somewhere (not important where) on the sheet with
this code:

'_________________________________________________ ______________
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
'_________________________________________________ ______________

in the Sheet1 code module.

Next, I setup UserForm1 with a ComboBox called CB1, three TextBoxes
called TB1, TB2 and TB3. Last, I setup the two CommandButtons.
CommandButton1 labeled "Create" and CommandButton2 labeled "Modify"

Placement of these controls is not important.

I Expanded your code to this:

'_________________________________________________ _______________
Option Explicit
Dim i As Integer
Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow As Integer
Dim ListRange As Range
Dim lRowToModify As Long

Private Sub CB1_Change()
'This auto loads the three TextBoxes with the data
'from the chosen item in the CB1 list

i = 0
If Not CB1.ListIndex < 0 Then
If CB1.ListIndex = 0 Then
For i = 1 To 3
Me.Controls("TB" & i).Text = ""
Next i
Else
For i = 1 To 3
Me.Controls("TB" & i).Text = _
CB1.List(CB1.ListIndex, i - 1)
Next i
End If
End If

End Sub

Private Sub CommandButton1_Click()
sub1
End Sub

Private Sub CommandButton2_Click()
sub2
End Sub

Sub sub1()
'This saves the modified record at the bottom of the list as a new
'record. ***WORKING*** '

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
lRowToModify = LastRow + 1
With Range("A" & lRowToModify)
For i = 1 To 3
.Offset(0, i - 1).Value = _
Me.Controls("TB" & i).Value
Next i
End With
Unload Me

End Sub

Sub sub2()
'This is supposed to take the changes to the record_
'shown in CB1 and overwrite that record.
' ***NOT WORKING*** '
'Instead, it overwrites ONLY the first cell in the correct row!
'It acts like it is not going thru the For/Next loop.

lRowToModify = CB1.ListIndex + 1
With ws.Cells(lRowToModify + 1, "A")
For i = 1 To 3
.Offset(0, i - 1).Value = _
Me.Controls("TB" & i).Value
Next i
End With
Unload Me

End Sub

Private Sub UserForm_Initialize()

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")
ws.Activate
LastRow = Range("A65536").End(xlUp).Row
Set ListRange = ws.Range("A2:C" & LastRow)

'This clears the three TextBoxes
For i = 1 To 3
Me.Controls("TB" & i).Value = vbNullString
Next i

'This sets the RowSource for CB!
With CB1
.RowSource = ListRange.Address
.ColumnCount = ListRange.Columns.Count
.ListIndex = 0 '0 selects first record
End With

'This clears the display of CB1
CB1.Value = vbNullString

End Sub
'_________________________________________________ ________

You should be able to paste my expanded code into your test workbook
and see what I am talking about.

The problem is that I can't seem to get the "Modify" button to modify
all three cells in the chosen row, only the first cell.

At first I thought this was a syntax problem, now I'm not so sure.

Any ideas are appreciated

-Minitman


On Wed, 1 Aug 2007 17:08:16 +0900, "kounoike"
wrote:

I tested your code and it worked without problem for me except when not
select Combobox and push CommandButton2 .
I put three Textboxes named TB1, TB2, TB3 , one Combobox named CB1 and two
CommandButtons on Userform1.
My tested code is something like this.

Sub test()
UserForm1.Show
End Sub

Private Sub CommandButton1_Click()
sub1
End Sub

Private Sub CommandButton2_Click()
sub2
End Sub

Sub sub1()
With Range("A65536").End(xlUp)
For i = 1 To 3
.Offset(0, i).Value = _
Me.Controls("TB" & i).Value
Next i
End With
End Sub

Sub sub2()
Dim vRowToModify As Long
vRowToModify = CB1.ListIndex + 1
With Range("A" & vRowToModify)
For i = 1 To 3
.Offset(0, i).Value = _
Me.Controls("TB" & i).Value
Next i
End With
End Sub

keizi

"Minitman" wrote in message
.. .
Thanks for the reply,

I am not getting any errors, except for the results. The only cell
that is modified is when i=1 (TB1.value is placed into column C of the
chosen row right where it is supposed to be). It is as if the code is
not doing the For/Next loop at all. I even tried replacing the
variable rRowToModify with the row number - Same result.

Any Ideas, thoughts, direction or links the might shed some light are
most welcome?

-Minitman


On Tue, 31 Jul 2007 17:02:01 -0700, JLGWhiz
wrote:

Are you getting an error message, and if so, what is it?

"Minitman" wrote:

Greetings,

I have two subs to put data from some TextBoxes (TB1 thru TB34) on a
UserForm onto a row on a sheet.

The first sub places the data in the first empty row - this one works.
Here is the code:

With Range("A65536").End(xlUp)
For i = 1 To 34
.Offset(0, i).Value = _
Me.Controls("TB" & i).Value
Next i
End With

The other sub is supposed to overwrite the row chosen from a ComboBox.
These two subs are identical except for the choice of which row to put
the data into.
Here is the second sub:

vRowToModify = CB1.ListIndex + 1

With Range("A" & vRowToModify)
For i = 1 To 34
.Offset(0, i).Value = _
Me.Controls("TB" & i).Value
Next i
End With

The variable vRowToModify does return the correct row number but I'm
not sure how to use it. the Range statement is not right and that is
messing up the sub

Can someone help me with the syntax of this "With" statement? Nothing
I have tried works.

Any help is greatly appreciated,

-Minitman