Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Creating Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Creating Array
Try
If TypeOf ctl Is Label And IsError(Application.Match(ctl.Name, Array("Label10","Label9"),0) Then --- HTH Bob Phillips "K" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Creating Array
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 -- Cheers, Ryan "K" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating an array | Excel Programming | |||
creating an array | Excel Programming | |||
Creating and using an array | Excel Programming | |||
creating an array on the fly | Excel Programming | |||
Creating an array | Excel Programming |