Solved

Type mismatch using "for each" with controls on a form.

Posted on 2003-11-07
6
363 Views
Last Modified: 2010-05-01
This must seem very basic. I haven't used for each much before, but I seem to remember seeing similar examples to this somewhere...

In the example below, I have 4 textboxes on a form and a command button. I want to change the background of all the textboxes.  When I run the program, the textboxes do change colour, but I get "run time error 13 - type mismatch.". What am I doing wrong?

Private Sub Command1_Click()
 Dim t As TextBox
 For Each t In Me
    t.BackColor = RGB(100, 0, 0)
 Next
End Sub
0
Comment
Question by:molar
6 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 9700390
I do not think the error is raised here.  Since you only have 4 TextBoxes on the form, I suggest you step through the code and see exactly which line causes the error.  Also, launch your application with full compile (Ctl+F5), there maybe compilation errors which are causing your message.

Leon
0
 
LVL 11

Expert Comment

by:dbrckovi
ID: 9700391
Try this:

Private Sub Command1_Click()
   
      For Each TextBox In Me
           TextBox.BackColor = RGB(100, 0, 0)
      Next

End Sub
0
 
LVL 11

Expert Comment

by:dbrckovi
ID: 9700399
Doesn't have to be TextBox

It can be anything:


Private Sub Command1_Click()
   
   For Each aaa In Me
       aaa.BackColor = RGB(100, 0, 0)
   Next

End Sub
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 11

Expert Comment

by:dbrckovi
ID: 9700447
Molar!

According to MSDN help on For---Each Statement, your example won't work becouse you use an object for itteration, and it should be a variable.

Here is the whole text from MSDN:

---------------------------------------------------------------------------------
For Each...Next Statement

Repeats a group of statements for each element in an array or collection.

Syntax

For Each element In group
[statements]
[Exit For]
[statements]

Next [element]

The For...Each...Next statement syntax has these parts:

Part Description
- element              Required.            Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable,                                                       or any specific object variable. For arrays, element can only be a Variant variable.
- group                 Required.             Name of an object collection or array (except an array of user-defined types).
- statements          Optional.             One or more statements that are executed on each item in group.


Remarks

The For...Each block is entered if there is at least one element in group. Once the loop has been entered, all the statements in the loop are executed for the first element in group. If there are more elements in group, the statements in the loop continue to execute for each element. When there are no more elements in group, the loop is exited and execution continues with the statement following the Next statement.

Any number of Exit For statements may be placed anywhere in the loop as an alternative way to exit. Exit For is often used after evaluating some condition, for example If?Then, and transfers control to the statement immediately following Next.

You can nest For...Each...Next loops by placing one For...Each...Next loop within another. However, each loop element must be unique.

Note   If you omit element in a Next statement, execution continues as if element is included. If a Next statement is encountered before its corresponding For statement, an error occurs.

You can't use the For...Each...Next statement with an array of user-defined types because a Variant can't contain a user-defined type.
-----------------------------------------------------------------
0
 
LVL 1

Accepted Solution

by:
Crazy_king_in earned 250 total points
ID: 9700466
Try this Simple

Dim Ctl As Control

For Each Ctl In Me
    If TypeOf Ctl Is TextBox Then
        Ctl.BackColor = vbRed
    End If
Next
0
 
LVL 2

Author Comment

by:molar
ID: 9700612
Bingo.

I guess my code must have been trying to set the backcolor of the command button as well.

Doh!

0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question