Update Data On Sheet From UserForm TextBoxes
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
--
Gary
"Minitman" wrote in message
...
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
|