How to Creating Array

[K]

Hi all, I got macro (see below)

Private Function LabelNormal()
On Error Resume Next
Dim ctl As Control


For Each ctl In Me.Controls
If TypeOf ctl Is Label And ctl.Name <> "Label10" And ctl.Name <>
"Label9" Then
With ctl
.SpecialEffect = 1 'Raised
.BackColor = 8421504 'Grey
.ForeColor = 16777215 'White
.FontWeight = 400 'Normal
End With
End If
Next

End Function


the above macro fine but I want to change the line where it say
If TypeOf ctl Is Label And ctl.Name <> "Label10" And ctl.Name <>
"Label9" Then

to
If TypeOf ctl Is Label And ctl.Name <> Array("Label10" , "Label9")
Then

I tried it but its not working. Basically I want macro to go through
all the names I put in Array and change only those controls which
names are not array. Please can any friend can help as I always had
problem creating array in macros

 

[Bob Phillips]

Try

If TypeOf ctl Is Label And IsError(Application.Match(ctl.Name,
Array("Label10","Label9"),0) Then

 

[Ryan H]

This should help you. You have to setup an array and loop thru the values of
the array to do what you are wanting to do. By the way, I'm not sure why you
have On Error Resume Next, but I would recommend you not put that in your
code, because it can cause unwanted results if an error is thrown of which
you are not expecting.

I hope this helps! If so, let me know. Click "YES" below.

Option Explicit

Private Function LabelNormal()

Dim ctl As Control
Dim myArray As Variant
Dim i As Long

myArray = Array("Label9", "Label10")

For Each ctl In Me.Controls
If TypeOf ctl Is Label Then
For i = LBound(myArray) To UBound(myArray)
If ctl.Name <> myArray(i) Then
With ctl
.SpecialEffect = 1 'Raised
.BackColor = 8421504 'Grey
.ForeColor = 16777215 'White
.FontWeight = 400 'Normal
End With
End If
Next i
End If
Next ctl

End Function