Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting From UserForm TextBoxes To Lastrow On Active Sheet
Greetings,
I am trying to populate the first 12 columns in the last row of the active sheet (Food_List) from 12 UserForm TextBoxes (TB5 thru TB16). And/or populate the first 6 columns in the last row of the active sheet (Daily_Log) from 6 UserForm TextBoxes (TB1 thru TB6). It depends on which CommandButton is clicked. Code for 'Post To Daily Log' CommandButton: Private Sub PostToDailyLogButton_Click() Set WS2 = Worksheets("Daily_Log") WS2.Activate vLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A" & vLastRow & ":AE" & vLastRow).Copy _ Destination:=Range("A" & vLastRow + 1 & ":AE" & vLastRow + 1) Application.CutCopyMode = False With Range("A65536").End(xlUp) For i = 1 To 6 .Offset(0, i - 1).Value = Me.Controls("TB" & i).Value MsgBox "Cycle " & i Next i End With Unload Me End Sub Code for 'Post To Food List' CommandButton: Private Sub PostToFoodListButton_Click() Set WS1 = Worksheets("Food_List") WS1.Activate vLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A" & vLastRow & ":L" & vLastRow).Copy _ Destination:=Range("A" & vLastRow + 1 & ":L" & vLastRow + 1) Application.CutCopyMode = False With Range("A65536").End(xlUp) For i = 1 To 12 .Offset(0, i - 1).Value = Me.Controls("TB" & i + 4).Value MsgBox "Cycle " & i Next i End With Unload Me End Sub Problem: If I click on the Daily Log button, the message box comes up once each cycle (eg. Cycle 1, Cycle 2, Cycle 3, Cycle 4, Cycle 5 & Cycle 6) and the result is as anticipated. However, if I click on the Food List button the message box comes up only once with Cycle 13 (which is not a legitimate response, Cycle 1 thru Cycle 12 are legitimate responses). The code copies the last row of the active sheet to the next row down (which is good) and then copies the first of the 12 TextBoxes to the first of the 12 cells in the now last row (over writing the contents but not the formats of that cell, which is still good). But there it stops (which is NOT good). Why does this code work on one sheet but not the other? Anyone have any ideas as to why and how to fix it? I have a sample workbook with the 2 sheets and the UserForm ready if anyone is interested in seeing it. Thanks for looking at my post and for any help you may be able and willing to offer. -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting From UserForm TextBoxes To Lastrow On Active Sheet
Email me your xls ..I'll take a look
Minitman wrote: Greetings, I am trying to populate the first 12 columns in the last row of the active sheet (Food_List) from 12 UserForm TextBoxes (TB5 thru TB16). And/or populate the first 6 columns in the last row of the active sheet (Daily_Log) from 6 UserForm TextBoxes (TB1 thru TB6). It depends on which CommandButton is clicked. Code for 'Post To Daily Log' CommandButton: Private Sub PostToDailyLogButton_Click() Set WS2 = Worksheets("Daily_Log") WS2.Activate vLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A" & vLastRow & ":AE" & vLastRow).Copy _ Destination:=Range("A" & vLastRow + 1 & ":AE" & vLastRow + 1) Application.CutCopyMode = False With Range("A65536").End(xlUp) For i = 1 To 6 .Offset(0, i - 1).Value = Me.Controls("TB" & i).Value MsgBox "Cycle " & i Next i End With Unload Me End Sub Code for 'Post To Food List' CommandButton: Private Sub PostToFoodListButton_Click() Set WS1 = Worksheets("Food_List") WS1.Activate vLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A" & vLastRow & ":L" & vLastRow).Copy _ Destination:=Range("A" & vLastRow + 1 & ":L" & vLastRow + 1) Application.CutCopyMode = False With Range("A65536").End(xlUp) For i = 1 To 12 .Offset(0, i - 1).Value = Me.Controls("TB" & i + 4).Value MsgBox "Cycle " & i Next i End With Unload Me End Sub Problem: If I click on the Daily Log button, the message box comes up once each cycle (eg. Cycle 1, Cycle 2, Cycle 3, Cycle 4, Cycle 5 & Cycle 6) and the result is as anticipated. However, if I click on the Food List button the message box comes up only once with Cycle 13 (which is not a legitimate response, Cycle 1 thru Cycle 12 are legitimate responses). The code copies the last row of the active sheet to the next row down (which is good) and then copies the first of the 12 TextBoxes to the first of the 12 cells in the now last row (over writing the contents but not the formats of that cell, which is still good). But there it stops (which is NOT good). Why does this code work on one sheet but not the other? Anyone have any ideas as to why and how to fix it? I have a sample workbook with the 2 sheets and the UserForm ready if anyone is interested in seeing it. Thanks for looking at my post and for any help you may be able and willing to offer. -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting From UserForm TextBoxes To Lastrow On Active Sheet
I think found your problem
You have "i" declared on a module level ( in the declaration section) When you select from the TB5 list box it leaves i with value of 12. you can either: Remove Dim i as integer from the declarations section and declare in each procedure. or Change the food list procedure to use say j instead of i and Dim j as an integer at the start of the procedure. Minitman wrote: Greetings, I am trying to populate the first 12 columns in the last row of the active sheet (Food_List) from 12 UserForm TextBoxes (TB5 thru TB16). And/or populate the first 6 columns in the last row of the active sheet (Daily_Log) from 6 UserForm TextBoxes (TB1 thru TB6). It depends on which CommandButton is clicked. Code for 'Post To Daily Log' CommandButton: Private Sub PostToDailyLogButton_Click() Set WS2 = Worksheets("Daily_Log") WS2.Activate vLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A" & vLastRow & ":AE" & vLastRow).Copy _ Destination:=Range("A" & vLastRow + 1 & ":AE" & vLastRow + 1) Application.CutCopyMode = False With Range("A65536").End(xlUp) For i = 1 To 6 .Offset(0, i - 1).Value = Me.Controls("TB" & i).Value MsgBox "Cycle " & i Next i End With Unload Me End Sub Code for 'Post To Food List' CommandButton: Private Sub PostToFoodListButton_Click() Set WS1 = Worksheets("Food_List") WS1.Activate vLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A" & vLastRow & ":L" & vLastRow).Copy _ Destination:=Range("A" & vLastRow + 1 & ":L" & vLastRow + 1) Application.CutCopyMode = False With Range("A65536").End(xlUp) For i = 1 To 12 .Offset(0, i - 1).Value = Me.Controls("TB" & i + 4).Value MsgBox "Cycle " & i Next i End With Unload Me End Sub Problem: If I click on the Daily Log button, the message box comes up once each cycle (eg. Cycle 1, Cycle 2, Cycle 3, Cycle 4, Cycle 5 & Cycle 6) and the result is as anticipated. However, if I click on the Food List button the message box comes up only once with Cycle 13 (which is not a legitimate response, Cycle 1 thru Cycle 12 are legitimate responses). The code copies the last row of the active sheet to the next row down (which is good) and then copies the first of the 12 TextBoxes to the first of the 12 cells in the now last row (over writing the contents but not the formats of that cell, which is still good). But there it stops (which is NOT good). Why does this code work on one sheet but not the other? Anyone have any ideas as to why and how to fix it? I have a sample workbook with the 2 sheets and the UserForm ready if anyone is interested in seeing it. Thanks for looking at my post and for any help you may be able and willing to offer. -Minitman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting From UserForm TextBoxes To Lastrow On Active Sheet
Thanks Steve,
That was indeed the problem and both of your solutions fixed the problem. I'll have to watch my declarations in the future! -Minitman On 12 Sep 2006 12:22:53 -0700, "stevebriz" wrote: I think found your problem You have "i" declared on a module level ( in the declaration section) When you select from the TB5 list box it leaves i with value of 12. you can either: Remove Dim i as integer from the declarations section and declare in each procedure. or Change the food list procedure to use say j instead of i and Dim j as an integer at the start of the procedure. Minitman wrote: Greetings, I am trying to populate the first 12 columns in the last row of the active sheet (Food_List) from 12 UserForm TextBoxes (TB5 thru TB16). And/or populate the first 6 columns in the last row of the active sheet (Daily_Log) from 6 UserForm TextBoxes (TB1 thru TB6). It depends on which CommandButton is clicked. Code for 'Post To Daily Log' CommandButton: Private Sub PostToDailyLogButton_Click() Set WS2 = Worksheets("Daily_Log") WS2.Activate vLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A" & vLastRow & ":AE" & vLastRow).Copy _ Destination:=Range("A" & vLastRow + 1 & ":AE" & vLastRow + 1) Application.CutCopyMode = False With Range("A65536").End(xlUp) For i = 1 To 6 .Offset(0, i - 1).Value = Me.Controls("TB" & i).Value MsgBox "Cycle " & i Next i End With Unload Me End Sub Code for 'Post To Food List' CommandButton: Private Sub PostToFoodListButton_Click() Set WS1 = Worksheets("Food_List") WS1.Activate vLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A" & vLastRow & ":L" & vLastRow).Copy _ Destination:=Range("A" & vLastRow + 1 & ":L" & vLastRow + 1) Application.CutCopyMode = False With Range("A65536").End(xlUp) For i = 1 To 12 .Offset(0, i - 1).Value = Me.Controls("TB" & i + 4).Value MsgBox "Cycle " & i Next i End With Unload Me End Sub Problem: If I click on the Daily Log button, the message box comes up once each cycle (eg. Cycle 1, Cycle 2, Cycle 3, Cycle 4, Cycle 5 & Cycle 6) and the result is as anticipated. However, if I click on the Food List button the message box comes up only once with Cycle 13 (which is not a legitimate response, Cycle 1 thru Cycle 12 are legitimate responses). The code copies the last row of the active sheet to the next row down (which is good) and then copies the first of the 12 TextBoxes to the first of the 12 cells in the now last row (over writing the contents but not the formats of that cell, which is still good). But there it stops (which is NOT good). Why does this code work on one sheet but not the other? Anyone have any ideas as to why and how to fix it? I have a sample workbook with the 2 sheets and the UserForm ready if anyone is interested in seeing it. Thanks for looking at my post and for any help you may be able and willing to offer. -Minitman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform Listbox using an active sheet | Excel Discussion (Misc queries) | |||
userform - active sheet | Excel Programming | |||
Accessing a non-active sheet from a userform | Excel Programming | |||
userform & textboxes | Excel Programming | |||
userform textboxes | Excel Programming |