Need a macro to loop evaluation of many textboxes in a UserForm.

[excelnut1954]

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.

 

[Bob Phillips]

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]

Thanks Bob
I'll check this out tonight at home.
I appreciate your response.
J.O.

 

[excelnut1954]

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.

 

[Bob Phillips]

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.

 

[excelnut1954]

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

 

[excelnut1954]

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.