Convert repetitive IF statements to array loop?
 

Hey all,

I have a series of IF statements below that checks MatchFound and if
false, removes the erroneous data from the field.

A couple of questions for improving this.

1.) Can I create an array of the specified combo boxes for use in a
loop? I don't want to loop through ALL combo boxes on this particular
form, just the specified ones below.

2.) Can I pass the elements of the array such that they are recognized
as form fields below (eg Me.[ArrayElement1])?

Thanks everyone!

- Brian



Code:
--------------------
If Me.cboTestA.MatchFound = False Then
Me.cboTestA = ""
End If

If Me.cboTestB.MatchFound = False Then
Me.cboTestB = ""
End If

If Me.cboTestC.MatchFound = False Then
Me.cboTestC = ""
End If
--------------------


--
bntringa
------------------------------------------------------------------------
bntringa's Profile: http://www.excelforum.com/member.php...o&userid=30523
View this thread: http://www.excelforum.com/showthread...hreadid=505863

Convert repetitive IF statements to array loop?
 

Hello Bntringa,

Here is a routine to deals with the IF statements. Just change
UserForm1 to whatever form the ComboBoxes are on.

Sub CboClear()
For Each Ctrl In UserForm1.Controls
If TypeName(Ctrl) = "ComboBox" Then
With Ctrl
If .MatchFound = False Then .Value = ""
End With
End If
Next Ctrl
End Sub

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=505863

Convert repetitive IF statements to array loop?
 

1) You could, but it hardly seems worth the effort for this. In adding it to
the array, you would have to test each one to see if it fits your criteria.

2) Yes you can

myFunction Me.cboTestA

....

Function myfunction(cbo As MSForms.ComboBox)
MsgBox cbo.Value
End Function

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"bntringa" wrote in
message ...

Hey all,

I have a series of IF statements below that checks MatchFound and if
false, removes the erroneous data from the field.

A couple of questions for improving this.

1.) Can I create an array of the specified combo boxes for use in a
loop? I don't want to loop through ALL combo boxes on this particular
form, just the specified ones below.

2.) Can I pass the elements of the array such that they are recognized
as form fields below (eg Me.[ArrayElement1])?

Thanks everyone!

- Brian



Code:
--------------------
If Me.cboTestA.MatchFound = False Then
Me.cboTestA = ""
End If

If Me.cboTestB.MatchFound = False Then
Me.cboTestB = ""
End If

If Me.cboTestC.MatchFound = False Then
Me.cboTestC = ""
End If
--------------------


--
bntringa
------------------------------------------------------------------------
bntringa's Profile:
http://www.excelforum.com/member.php...o&userid=30523
View this thread: http://www.excelforum.com/showthread...hreadid=505863

Convert repetitive IF statements to array loop?
 

Hi Leath,

Thanks for the input. The only problem with your recommendation is
that I do not want to run this against all combo boxes so I am using an
array instead with a bit of trouble. Here's what I've come up with:


Code:
--------------------
Dim ComboBoxArray(1 To 3) As String

ComboBoxArray(1) = "cboTestA"
ComboBoxArray(2) = "cboTestB"
ComboBoxArray(3) = "cboTestC"

For I = 1 To UBound(ComboBoxArray)
With Me(ComboBoxArray)
If .MatchFound = False Then .Value = ""
End With
Next I
--------------------


I am getting a type mismatch on the With Me(ComboBoxArray) line.

Any thoughts?

thanks!


--
bntringa
------------------------------------------------------------------------
bntringa's Profile: http://www.excelforum.com/member.php...o&userid=30523
View this thread: http://www.excelforum.com/showthread...hreadid=505863

Convert repetitive IF statements to array loop?
 

Hello Bntringa,

Try this...

Dim cbo As MSForms.ComboBox
Dim ComboBoxArray(1 To 3) As String

ComboBoxArray(1) = "cboTestA"
ComboBoxArray(2) = "cboTestB"
ComboBoxArray(3) = "cboTestC"

For I = 1 To UBound(ComboBoxArray)
Set cbo = Me.Controls(ComboBoxArray(I))
If cbo.MatchFound = False Then cbo.Value = ""
Next I

Sincerely,
Leith ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=50586

Convert repetitive IF statements to array loop?
 

Leath,

I figured it out - I just forgot to put the reference to I in the With
Me line below:

With Me(ComboBoxArray) should be:

With Me(ComboBoxArray(I))

Thanks for your help pointing me in the right direction!

- Brian



Code:
--------------------
Dim ComboBoxArray(1 To 3) As String

ComboBoxArray(1) = "cboTestA"
ComboBoxArray(2) = "cboTestB"
ComboBoxArray(3) = "cboTestC"

For I = 1 To UBound(ComboBoxArray)
With Me(ComboBoxArray(I))
If .MatchFound = False Then .Value = ""
End With
Next I
--------------------


I am getting a type mismatch on the With Me(ComboBoxArray) line.

Any thoughts?

thanks!


--
bntringa
------------------------------------------------------------------------
bntringa's Profile: http://www.excelforum.com/member.php...o&userid=30523
View this thread: http://www.excelforum.com/showthread...hreadid=505863