Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a macro to loop evaluation of many textboxes in a UserForm.
UserForm13 allows the user to identify different PO numbers they want
to delete from a list. TextBoxes 1-25 odd numbers are for PO numbers. TextBoxes 2-26 even numbers are for Taken By (a person's name0 TextBoxes 27-39 is for the number of pieces taken from this PO TextBox41 is the default date applied to each record to be deleted from the list. I have 13 textbox #s 1, 3 , 5, etc (odd numbers 1 - 25) that could contain these PO numbers to be deleted. Each of these textboxes goes with 3 other textboxes when the info is posted to the worksheet. Textbox1 goes with boxes 2 & 27 & 41. Textbox2 goes with boxes 4 & 28 & 41 (Box41 is a default date to be posted with any of these). When any of the Textboxes 1, 3, 5, etc are posted, then the other 3 boxes that go with it is also posted. (You can see an example of TextBox1 in the subs below.) The user may put a PO# in the TextBox1., TextBox2, or all 13 of the odd numbers 1 - 25. Also, the user might not start with TextBox1 as the 1st entry. Maybe he will for some reason click TextBox 3, and start there. So, I need to look at each of the textboxes that may contain a PO number to 1st make sure there is an entry in that box. Then, I need to make sure that PO number exists on the list, then I have it look to make sure there are no duplicates found (if so, they need to contact a supervisor... This is also in the code below). So, the code below works fine for doing all of this for evaluating TextBox1. My problem is that I would like to find out how to do this as a loop for the 13 textboxes (odd numbers 1 - 25) without having to duplicate the code just to change the textbox numbers in the code. Doing so would mean duplicating the code I have below 13 times. I suppose that would work fine, but I know there must be a better way to write this. I realize my coding may be crude, but I still want to keep the lines of code to a minimum. If you have any ideas, I would appreciate any feedback. Even if I don't understand your answer now, I can have something to point me in the right direction... something to study. Thanks, J.O. 'For all TextBoxes odd numbers 1-25. Assigns name for each. '***If I need to, I can do this for all the textboxes involved. Private Sub TextBox1_Change() PO1 = TextBox1.Value End Sub Sub PostDeletesBox1() 'This will check to make sure there is 1 and only 1 of this PO number on list. If TextBox1.Text < "" Then Worksheets("Official List").Activate CountPOtoValidate = Application.CountIf(Range("J:J"), PO1) End If If CountPOtoValidate < 1 Then MsgBox "This record does not exist on the list. Please check the PO number and try again" ElseIf CountPOtoValidate 1 Then MsgBox "There are duplicate records. Highlight this record on the list, then see the supervisor. " Else 'This will post the entries from TextBoxes 2, 27 & 41 for the PO# entered in TextBox1. Set rngToSearch = Sheets("Official List").Columns("J") Set rngFound = rngToSearch.Find(What:=PO1, _ LookIn:=xlValues) rngFound.Select ActiveWorkbook.Names.Add Name:="DeletePOCell", RefersTo:=ActiveCell Application.Goto Reference:="DeletePOCell" ActiveCell.Offset(0, 4).Select Application.Selection.Value = TextBox27.Text 'Pieces moved ActiveCell.Offset(0, 2).Select Application.Selection.Value = UCase(TextBox2.Text) 'Taken By ActiveCell.Offset(0, 1).Select Application.Selection.Value = TextBox41.Text 'Default date Cancel = False ActiveWorkbook.Names("DeletePOCell").Delete End If End Sub I need a way to perform all this above 13 times. One for each of the odd numbered Textboxes 1-26. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a macro to loop evaluation of many textboxes in a UserForm.
I think this is what you mean
Sub PostDeletesBox1() Dim i As Long For i = 1 To 2 '13 'This will check to make sure there is 1 'and only 1 of this PO number on list. If Me.Controls("TextBox" & i * 2 - 1).Text < "" Then ' Worksheets("Official List").Activate CountPOtoValidate = Application.CountIf(Range("J:J"), PO1) End If If CountPOtoValidate < 1 Then MsgBox "This record does not exist on the list." & vbNewLine & _ "Please check the PO number and try again" ElseIf CountPOtoValidate 1 Then MsgBox "There are duplicate records." & vbNewLine & _ "Highlight this record on the list, then see the supervisor." Else 'This will post the entries from TextBoxes 2, 27 & 41 'for the PO# entered in TextBox1, 3, 5, etc. Set rngToSearch = Sheets("Official List").Columns("J") Set rngFound = rngToSearch.Find(What:=PO1, _ LookIn:=xlValues) rngFound.Select ActiveWorkbook.Names.Add Name:="DeletePOCell", RefersTo:=ActiveCell Application.Goto Reference:="DeletePOCell" ActiveCell.Offset(0, 4).Select Application.Selection.Value = _ Me.Controls("TextBox" & 26 + i).Text 'Pieces moved ActiveCell.Offset(0, 2).Select Application.Selection.Value = _ UCase(Me.Controls("TextBox" & i * 2).Text) 'Taken By ActiveCell.Offset(0, 1).Select Application.Selection.Value = TextBox41.Text 'Default date cancel = False ActiveWorkbook.Names("DeletePOCell").Delete End If Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "excelnut1954" wrote in message ups.com... UserForm13 allows the user to identify different PO numbers they want to delete from a list. TextBoxes 1-25 odd numbers are for PO numbers. TextBoxes 2-26 even numbers are for Taken By (a person's name0 TextBoxes 27-39 is for the number of pieces taken from this PO TextBox41 is the default date applied to each record to be deleted from the list. I have 13 textbox #s 1, 3 , 5, etc (odd numbers 1 - 25) that could contain these PO numbers to be deleted. Each of these textboxes goes with 3 other textboxes when the info is posted to the worksheet. Textbox1 goes with boxes 2 & 27 & 41. Textbox2 goes with boxes 4 & 28 & 41 (Box41 is a default date to be posted with any of these). When any of the Textboxes 1, 3, 5, etc are posted, then the other 3 boxes that go with it is also posted. (You can see an example of TextBox1 in the subs below.) The user may put a PO# in the TextBox1., TextBox2, or all 13 of the odd numbers 1 - 25. Also, the user might not start with TextBox1 as the 1st entry. Maybe he will for some reason click TextBox 3, and start there. So, I need to look at each of the textboxes that may contain a PO number to 1st make sure there is an entry in that box. Then, I need to make sure that PO number exists on the list, then I have it look to make sure there are no duplicates found (if so, they need to contact a supervisor... This is also in the code below). So, the code below works fine for doing all of this for evaluating TextBox1. My problem is that I would like to find out how to do this as a loop for the 13 textboxes (odd numbers 1 - 25) without having to duplicate the code just to change the textbox numbers in the code. Doing so would mean duplicating the code I have below 13 times. I suppose that would work fine, but I know there must be a better way to write this. I realize my coding may be crude, but I still want to keep the lines of code to a minimum. If you have any ideas, I would appreciate any feedback. Even if I don't understand your answer now, I can have something to point me in the right direction... something to study. Thanks, J.O. 'For all TextBoxes odd numbers 1-25. Assigns name for each. '***If I need to, I can do this for all the textboxes involved. Private Sub TextBox1_Change() PO1 = TextBox1.Value End Sub Sub PostDeletesBox1() 'This will check to make sure there is 1 and only 1 of this PO number on list. If TextBox1.Text < "" Then Worksheets("Official List").Activate CountPOtoValidate = Application.CountIf(Range("J:J"), PO1) End If If CountPOtoValidate < 1 Then MsgBox "This record does not exist on the list. Please check the PO number and try again" ElseIf CountPOtoValidate 1 Then MsgBox "There are duplicate records. Highlight this record on the list, then see the supervisor. " Else 'This will post the entries from TextBoxes 2, 27 & 41 for the PO# entered in TextBox1. Set rngToSearch = Sheets("Official List").Columns("J") Set rngFound = rngToSearch.Find(What:=PO1, _ LookIn:=xlValues) rngFound.Select ActiveWorkbook.Names.Add Name:="DeletePOCell", RefersTo:=ActiveCell Application.Goto Reference:="DeletePOCell" ActiveCell.Offset(0, 4).Select Application.Selection.Value = TextBox27.Text 'Pieces moved ActiveCell.Offset(0, 2).Select Application.Selection.Value = UCase(TextBox2.Text) 'Taken By ActiveCell.Offset(0, 1).Select Application.Selection.Value = TextBox41.Text 'Default date Cancel = False ActiveWorkbook.Names("DeletePOCell").Delete End If End Sub I need a way to perform all this above 13 times. One for each of the odd numbered Textboxes 1-26. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a macro to loop evaluation of many textboxes in a UserForm.
Thanks Bob
I'll check this out tonight at home. I appreciate your response. J.O. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a macro to loop evaluation of many textboxes in a UserForm.
Bob,
If you check back on this, thanks. I appreciate the help. I tried this out, and what it will do as you wrote it, it will locate the PO# given in Textbox1 alright, but doesn't paste any info given, except for Textbox41, which is the easy one because it doesn't deal with any variables within this loop. I have a couple of questions : ######In the 1st section, For i = 1 To 2 '13 does the '13 represent where I implied that there were 13 textboxes that would have PO numbers? Does it mean I should change the code so that it repeats this 13 times? ######In the line CountPOtoValidate = Application.CountIf(Range("J:J"), PO1) The PO1 part comes from the Change Event for TextBox1. I originally used that to give that value a name. It works fine in my original coding, BUT, as I said before, my code will only work for Textbox1. Which is why I need help with the For-Next loop. Is there something I can put under the line If Me.Controls("TextBox" & i * 2 - 1).Text < "" Then That will give the textbox being evaluated a variable name to plug into this line. A name that would be used through each loop to stand for the next Textbox. I have other questions about all this. But, maybe I can deal with these 2 first. Sorry if this is confusing. I'm trying to explain this the best I can. This For-Next will still take some time for me to understand. Thanks J.O. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a macro to loop evaluation of many textboxes in a UserForm.
"excelnut1954" wrote in message oups.com... Bob, If you check back on this, thanks. I appreciate the help. I tried this out, and what it will do as you wrote it, it will locate the PO# given in Textbox1 alright, but doesn't paste any info given, except for Textbox41, which is the easy one because it doesn't deal with any variables within this loop. I have a couple of questions : ######In the 1st section, For i = 1 To 2 '13 does the '13 represent where I implied that there were 13 textboxes that would have PO numbers? Does it mean I should change the code so that it repeats this 13 times? Yes it means exactly that. I reduced the loop for my testing and forgot to revert back. ######In the line CountPOtoValidate = Application.CountIf(Range("J:J"), PO1) The PO1 part comes from the Change Event for TextBox1. I originally used that to give that value a name. It works fine in my original coding, BUT, as I said before, my code will only work for Textbox1. Which is why I need help with the For-Next loop. Is there something I can put under the line If Me.Controls("TextBox" & i * 2 - 1).Text < "" Then That will give the textbox being evaluated a variable name to plug into this line. A name that would be used through each loop to stand for the next Textbox. Is this what you mean? PO1 = Me.Controls("TextBox" & i * 2 - 1).Text I have other questions about all this. But, maybe I can deal with these 2 first. Sorry if this is confusing. I'm trying to explain this the best I can. This For-Next will still take some time for me to understand. Just fire any more if you have them. I will watch the thread. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a macro to loop evaluation of many textboxes in a UserForm.
Getting closer. This is all making much more sense as I break it apart.
I got everything as you originally posted above except that I added the last line you gave me. . The complete code is below. I tested it again, and I entered 3 records, that is, TextBoxes 1, 3 & 5 for the PO#, boxes 2, 4 & 6 for the Taken By, and boxes 27, 28 & 29 for the Pieces. (TextBox41 - Default date - goes with every record to be posted) I ran it, and the result was that the 1st 2 records were posted fine, but only the Date posted for the 3rd record. It didn't read TextBoxes6 & 29. It must have read TextBox5 ok, since it posted the date on that line. Well, here's the code up to now. I'll try it later on, filling up all 13 records to be posted. I suspect that after the 2nd record, only the Date will be posted for the other 11 records. Thanks again for the help, AND your time. J.O. Sub DeleteTest() Dim i As Long For i = 1 To 13 'This will check to make sure there is 1 'and only 1 of this PO number on list. If Me.Controls("TextBox" & i * 2 - 1).Text < "" Then PONum = Me.Controls("TextBox" & i * 2 - 1).Text ' Worksheets("Official List").Activate CountPOtoValidate = Application.CountIf(Range("J:J"), PONum) End If If CountPOtoValidate < 1 Then MsgBox "This record does not exist on the list." & vbNewLine & _ "Please check the PO number and try again" ElseIf CountPOtoValidate 1 Then MsgBox "There are duplicate records." & vbNewLine & _ "Highlight this record on the list, then see the supervisor." Else 'This will post the entries from TextBoxes 2, 27 & 41 'for the PO# entered in TextBox1, 3, 5, etc. Set rngToSearch = Sheets("Official List").Columns("J") Set rngFound = rngToSearch.Find(What:=PONum, _ LookIn:=xlValues) rngFound.Select ActiveWorkbook.Names.Add Name:="DeletePOCell", RefersTo:=ActiveCell Application.Goto Reference:="DeletePOCell" ActiveCell.Offset(0, 4).Select Application.Selection.Value = _ Me.Controls("TextBox" & 26 + i).Text 'Pieces moved ActiveCell.Offset(0, 2).Select Application.Selection.Value = _ UCase(Me.Controls("TextBox" & i * 2).Text) 'Taken By ActiveCell.Offset(0, 1).Select Application.Selection.Value = TextBox41.Text 'Default date Cancel = False ActiveWorkbook.Names("DeletePOCell").Delete End If Next i End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a macro to loop evaluation of many textboxes in a UserForm.
I've just tested it again. Here's what happens. If I do all 13 records,
then everything will post perfectly. If I do any less than 13, then the last record will only have the date posted, and the entries from The Taken By, and Pieces boxes will not post. Also, if I enter only 1, then again, just the date will post. Which ever record is the last will react this way. Maybe you can see something to cause this. I have no clue. Thanks J.O. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm TextBoxes | Excel Discussion (Misc queries) | |||
userform & textboxes | Excel Programming | |||
loop through textboxes only | Excel Programming | |||
userform textboxes again | Excel Programming | |||
userform textboxes | Excel Programming |