Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Data On Sheet From UserForm TextBoxes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Data On Sheet From UserForm TextBoxes
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 On Mon, 30 Jul 2007 02:21:31 -0400, "Gary Keramidas" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Data On Sheet From UserForm TextBoxes
you can email it to me if you don't receive a response here.
-- Gary "Minitman" wrote in message ... 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 On Mon, 30 Jul 2007 02:21:31 -0400, "Gary Keramidas" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send and Receive Data From Web Site Into UserForm TextBoxes | Excel Discussion (Misc queries) | |||
Need help w/ userform with 12 textboxes with data going into colum | Excel Discussion (Misc queries) | |||
userform textboxes saving numerical data as text in worksheet | Excel Programming | |||
Getting From UserForm TextBoxes To Lastrow On Active Sheet | Excel Programming | |||
UserForm TextBoxes | Excel Discussion (Misc queries) |