Update Data On Sheet From UserForm TextBoxes

[Minitman]

Greetings,

I have two Commandbuttons on a UserForm. One is called "Create" and
the other is called "Modify". They are designed to put the data from
several of the UserForm TextBoxes (TB1 thru TB34) either into the
first empty row on Sheet1 (Create) or to overwrite (Modify) the data
in the Sheet1 row chosen in the UserForm ComboBox (CB1).

Here is the code for the "Create" button:

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

This code works. However, when I try to reuse this code to modify the
chosen row, I cannot seem to get it to work.

Here is my last attempt:

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

When I check, I get the proper number for the chosen row
(vRowToModify). I just can't seem to get VBE to use it!!! MicroSoft
Help doesn't seem to get into that much detail. I suspect the line
with the <<<<<< after it, as being the problem, but I don't know how
to fix it!!!

Can anyone see where I messed up?

Any help is greatly appreciated.

-Minitman

 

[Gary Keramidas]

what do you mean by doesn't work?

you should also qualify the ranges with the sheet name:
dim ws as worksheet
set ws = worksheets("sheet1")

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

this works for me i if hard code it to row 6.

Dim ws As Worksheet
Dim vRowToModify As Long
Dim i As Long
Set ws = Worksheets("sheet1")

vRowToModify = 6 'CB1.ListIndex + 1
With ws.Range("A" & vRowToModify) '<<<<<<
For i = 1 To 34
.Offset(0, i + 1).Value = Me.Controls("TextBox" & i + 1).Value
Next i
End With

 

[Minitman]

Hey Gary,

Thanks for the response.

What I mean when I said "It doesn't work" is that in this
configuration, the code runs but does not modify the chosen row.
I added the ws. to the Range, but that did not change the way it did
not work. I also tried ws.Range("A" & vRowToModify & ":BS" &
vRowToModify) and that ran but the data was wrong (I changed the value
in the TextBox that went to column P and ALL of the cells in that row
had a 3 instead of the column number - the default for this test)

I have a sample of the workbook with all of the sensitive data removed
if you would like to take a look at it. This was made in Excel 2003.
Zipped it is about 101kb

Let me know, thanks.

-Minitman

 

[Gary Keramidas]

you can email it to me if you don't receive a response here.