For loop in VBA

[VBAmalta89]

I have a number of frames in a user form. Now, i need to create a line of code that does something like this:

For each frame enabled do...

Whats the coding i need to use?

 

[Richard Schollar]

Hi

You could use code along these lines:

Dim ctrl As Control
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "Frame" Then
        If ctrl.Object.Enabled Then
            MsgBox ctrl.Name
        End If
    End If
Next ctrl

 

[VBAmalta89]

thanks but what are TypeName and Name

sry if this may be a stupid question but im new to VBA and need some getting used to it

 

[Richard Schollar]

TypeName is a VBA function that can be used to distinguish different objects/variables (look it up in VBA Help for the definition) and Name is a property of a Control. I used MsgBox Ctrl.Name to demonstrate the code doing something once it had identified an Enabled Frame Control on the userform.

 

[VBAmalta89]

i still dont understand though hehe

THis is what im trying to do:

I have a frame and a text box beneath it. I want to create a validation in the text box to make sure it isnt left blank when the user clicks done. However, this validation is to be run only if the frame is enabled.

any idea?

 

[Richard Schollar]

Assuming you know the names of the the frame and textbox it can be as straightforward as:

If Me.Controls("Frame1").Object.Enabled = True Then   'amend name of Frame as required
  If Len(Me.Controls("TextBox1").Value) = 0 Then  'ie Len()=0 so Textbox is empty
     MsgBox "Critical textbox is empty - you must fill it!"
     Exit Sub  'or whatever is appropriate - depends on your current code
  End If
End If

Make sense?

 

[VBAmalta89]

teh code seems to work but heres the thing

i hve 8 frames which are enabled by a user. now i need the text box beneath each enabled frame to be inputted.

with the code u gave me it works wen all frames are enabled but when say 3 of the 8 frames are activated it still prompts the message that a text box is empty. THis is because it is checking the disabled frames as well for some reason

 

[Richard Schollar]

Please post the code you are using

 

[VBAmalta89]

This is a section of the code for the click button

Private Sub done_Click()

If Me.Controls("Frame1").Object.Enabled = True Then   
  If Len(Me.Controls("AZone1").Value) = 0 Then
     MsgBox "Please enter zone area!"
     Exit Sub
  End If
End If

If Me.Controls("Frame2").Object.Enabled = True Then
  If Len(Me.Controls("AZone2").Value) = 0 Then
     MsgBox "Please enter zone area!"
     Exit Sub
  End If
End If

If Me.Controls("Frame3").Object.Enabled = True Then
  If Len(Me.Controls("AZone3").Value) = 0 Then
     MsgBox "Please enter zone area!"
     Exit Sub
  End If
End If

If Me.Controls("Frame4").Object.Enabled = True Then
  If Len(Me.Controls("AZone4").Value) = 0 Then
     MsgBox "Please enter zone area!"
     Exit Sub
  End If
End If

If Me.Controls("Frame5").Object.Enabled = True Then
  If Len(Me.Controls("AZone5").Value) = 0 Then
     MsgBox "Please enter zone area!"
     Exit Sub
  End If
End If

If Me.Controls("Frame6").Object.Enabled = True Then
  If Len(Me.Controls("AZone6").Value) = 0 Then
     MsgBox "Please enter zone area!"
     Exit Sub
  End If
End If

If Me.Controls("Frame7").Object.Enabled = True Then
  If Len(Me.Controls("AZone7").Value) = 0 Then
     MsgBox "Please enter zone area!"
     Exit Sub
  End If
End If

If Me.Controls("Frame8").Object.Enabled = True Then
  If Len(Me.Controls("AZone8").Value) = 0 Then
     MsgBox "Please enter zone area!"
     Exit Sub
  End If
End If

 

[Richard Schollar]

Hi

That could be condensed down to:

Private Sub done_Click()

Dim i As Long

For i = 1 To 8 Step 1
  If Me.Controls("Frame" & i).Object.Enabled = True Then
    If Len(Me.Controls("AZone" & i).Value) = 0 Then
       MsgBox "Please enter AZone" & i & " value to proceed!"
       Exit Sub
    End If
  End If
Next i

'here needs to go the bit of code that happens if all the enabled frames have populated textboxes:

MsgBox "Success!"

End Sub