Results 1 to 26 of 26

For Loop Combo Boxes with If Statement

Threaded View

  1. #1
    Registered User
    Join Date
    02-15-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    27

    For Loop Combo Boxes with If Statement

    Hi, I am a beginner to VBA looking for some help with a For Loop

    I have a Userform with 18 Comboboxes named mid1, mid2, mid3 etc which would hold an item description and then 18 textboxes named mia1, mia2, mia3 etc which would hold corresponding currency amount (There are other textboxes on the userform as well)

    On Userform Activate, I am trying to disable a mia textbox if the corresponding mid combobox is empty.

    eg if mid3 is empty then mia3 needs to be Disabled.

    This is what I have got so far:


    Private Sub UserForm_Activate() 
    
    Dim coll As New Collection
        Dim item As TextBox
        Dim i As Integer
    
        coll.Add Me.mia1
        coll.Add Me.mia2
        coll.Add Me.mia3
        coll.Add Me.mia4
        coll.Add Me.mia5
        coll.Add Me.mia6
        coll.Add Me.mia7
        coll.Add Me.mia8
        coll.Add Me.mia9
        coll.Add Me.mia10
        coll.Add Me.mia11
        coll.Add Me.mia12
        coll.Add Me.mia13
        coll.Add Me.mia14
        coll.Add Me.mia15
        coll.Add Me.mia16
        coll.Add Me.mia17
        coll.Add Me.mia18
        
    Set i = 1
    With i
        For Each item In coll
            If Me.Controls("mid" & i).text = ""
        Then
            item.Enabled = False
            item.BackColor = vbGrey
            i = i + 1
            Next item
            
        End With
    End Sub
    I keep getting a compile error when i is set to 1, where am I going wrong.

    Also is a for loop the best way to achieve this?

    Many thanks for all help.
    Last edited by bssm; 02-21-2015 at 07:52 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to loop through cells and populate combo boxes using VBA and Excel?
    By chowee21 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2014, 10:44 AM
  2. [SOLVED] Help with code for userform text boxes, combo boxes and excel
    By innerise in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-20-2014, 09:07 AM
  3. [SOLVED] cascading combo boxes to run loop
    By union in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-11-2013, 02:29 PM
  4. Loop through ActiveX Combo boxes
    By Commercial_Coder in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-12-2012, 11:07 AM
  5. Loop Worksheet Combo Boxes
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-25-2011, 10:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1