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. |
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 |