How to create an 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

[Stefan Hoffmann]

hi Agent,

On 16.12.2009 13:28, K wrote:
> 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

Use the In operator:

If TypeOf ctl Is Label And ctl.Name In ("Label10", "Label9") Then
End If

mfG
--> stefan <--

[John Spencer]

One way to handle this is as follows

If TypeOf ctl Is Label And Instr("Label10,Label9,",ctl.Name)>0 Then
'Do Stuff
End If

Another way is to assign a tag to those controls you want to modify and test
the tag property of the control.

If TypeOf Ctl is Label Then
If Ctl.Tag = "ModifyLabel" Then

End If
End If

If you want to use an array, then you would need a loop to search through the
array.

Dim tfFound As Boolean
Dim I As Long
Dim ctl As Control
Dim MyArray() As Variant

If TypeOf ctl Is Label Then

For I = LBound(MyArray) To UBound(MyArray)
tfFound = ctl.Name = MyArray(I)
If tfFound = True Then Exit For
Next I

If tfFound Then
'Do stuff
End If
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

[K]

Thanks lot

[Marshall Barton]

Stefan Hoffmann wrote:
>Use the In operator:
>
>If TypeOf ctl Is Label And ctl.Name In ("Label10", "Label9") Then
>End If

Stefan, In is not a VBA operator. It's only available via
the Expression Service (e.g. control source expressions and
SQL). To use In in a VBA procedure, the condition would
have to be wrapped in the Eval function:

If TypeOf ctl Is Label And Eval("""" & ctl.Name & """ In
(""Label10"", ""Label9"")") Then

But, using InStr is easier to type and faster to run.

--
Marsh
MVP [MS Access]

[K]

Thanks for the information Marshall as i was struggling with In
procedure . Just thinking that if i have more than two names like 4
or 5 then how can i use instr function? would it be
If TypeOf ctl Is Label And Instr
("Label10,Label9,Label8,Label7,Label6",ctl.Name)>0 Then
please can i have any comments

[Marshall Barton]

K wrote:
>Just thinking that if i have more than two names like 4
>or 5 then how can i use instr function? would it be
>If TypeOf ctl Is Label And Instr
>("Label10,Label9,Label8,Label7,Label6",ctl.Name)>0 Then

Looks good to me. Give it a try and see.

Personally, I prefer the Tag property approach. This way.
if you ever decide to change the label names to something
meaningful, you won't have to go back and change the code.

[Douglas J. Steele]

Sorry to argue with Marsh, but I think you'd be better off using

If TypeOf ctl Is Label And Instr(",Label10,Label9,Label8,Label7,Label6,",
"," & ctl.Name & ",")>0 Then

A problem could occur otherwise if you had controls Label1 and Label10. What
I'm suggesting means that each label has a comma before and after, and you
concatenate commas before and after the actual name of the control,

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"K" <kamra...@yahoo.co.uk> wrote in message
news:0ce4622c-27f4-4de7...@r14g2000vbc.googlegroups.com...

[Marshall Barton]

Douglas J. Steele wrote:

>Sorry to argue with Marsh, but I think you'd be better off using
>
>If TypeOf ctl Is Label And Instr(",Label10,Label9,Label8,Label7,Label6,",
>"," & ctl.Name & ",")>0 Then
>
>A problem could occur otherwise if you had controls Label1 and Label10. What
>I'm suggesting means that each label has a comma before and after, and you
>concatenate commas before and after the actual name of the control,

Good catch Doug.

I still prefer the Tag approach though.