Selecting controls in a For loop

[FlintBeastwood]

I would like to select control items in a For loop. For example:

For i=1 to 10
If ThisWorkbook.Worksheets("sheet1&quot.Cells(6, i)= "" Then
TextBox(i).Enabled = False
next i

Of course the TextBox(i) call is wrong, how can I select the controls in a loop so I do not have to call them all individually (is there a numbered property)?

 

[SkipVought]

Hi,
There is a shapes collection...

Sub test()
    For Each sp In ActiveSheet.Shapes
        If sp.DrawingObject.ProgId Like "*TextBox*" Then
            sp.OLEFormat.Object.Enabled = False
        End If
    Next
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[acron]

Something to get you started :

Sub SetControls()
    Dim ctrl As OLEObject
    For Each ctrl In Worksheets("Sheet1").OLEObjects
        ctrl.Enabled = True
    Next
End Sub

A.C.

 

[RobBroekhuis]

How many of these controls do you have? If a limited number, it's really no big deal to spell them out individually. Otherwise, use a loop like suggested by Skip and Acron (which one depends on the type of control you're using), and parse the name of the control to find out which column to look in to see whether to disable it. For example, if you have your textboxes named "txt01"..."txt99", then

if left(ctrl.name,3)="txt" then
col=val(right(ctrl.name,2))
ctrl.enabled=cells(6,col)<>&quot;&quot;
end if


Rob

 

[FlintBeastwood]

I forgot to mention that the control objects are in a UserForm.
There are 10 CheckBoxes, 10 OptionButtons, 10 TextBoxes, 10 SpinButtons and 20 labels. I do not want to call each of them at once, but in order, linked to a cell, hence the for loop.
Example: If cell 1 in the worksheet is empty, I want CheckBox1, OptionButton1, TextBox1, SpinButton1, Label1 and Label11 in the UserForm to be invisible and disabled.
If I have to do this individually, I would need about 70 lines....

 

[SkipVought]

How do the 20 labels map?

How 'bout giving up ALL the info we need so we don't have to sit here trying to read your mind?

Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]

 

[RobBroekhuis]

Okay, assume, once again, that all your controls are named
txt01, txt02, label01, label02, option01, etc. All controls that are not affected by the values on your sheet are named such that they do not end in numbers. Now use can do

for each ctrl in me.controls
if val(right(ctrl.name),2)>0 then
ctrl.enabled=cells(6,val(right(ctrl.name,2)))<>&quot;&quot;
end if
next ctrl

Would that work?



Rob

 

[SkipVought]

We're playing a guessing game, Rob!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[FlintBeastwood]

I'm not exactly sure what you mean. But I'll try to clarify.

I have an excel worksheet with 2 to 10 names. When I open up the userform I want it to only to display the names in the worksheet.

To each name corresponds:
1 Label with the name,
1 optionbutton,
1 checkbox,
1 spinbutton and
another label.

If there are less than 10 names, I do not want to see the controls that are not in use. So you might say Label1, Label11, optionbutton1, checkbox1 and spinbutton1 correspond as a group to cell 1. The same applies for all controls 2 to 10.
For the labels this means Label2 and label12 correspond to cell 2.

 

[FlintBeastwood]

Rob, it might work, I'll try it.

 

[RobBroekhuis]

I'm not guessing - I'm suggesting Flint rename his labels to make his coding easier. ;-)


Rob

 

[SkipVought]

What is cell 1, cell 2...?

Common! This is like PULLING TEETH!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[FlintBeastwood]

I don't think that's not really relevant, they are just consecutive cells! But if you want it:
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 1)
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 2)
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 3)
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 4)
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 5)
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 6)
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 7)
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 8)
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 9)
ThisWorkbook.Worksheets(&quot;sheet1&quot.Cells(6, 10)

 

[Zathras]

I will assume that you already have the logic to know how many &quot;lines&quot; to show on the form. Given that that is the case, here is some code to handle the initializtion:
[blue]

Private Sub CommandButton1_Click()
  Me.Hide
End Sub

Private Sub UserForm_Initialize()

[green]

' Global variable NumberOfNames contains
' number of lines of objects to display

[/color]

Const MAX_NAMES = 10
Const LEFT_TEST = 12
Const TOP_TEST = 10
Const COMMAND_BUTTON_ADJUST = 20
Const HEIGHT_ADJUST = 70
Dim oControl As Control
Dim nVisibleBreakLine As Single

[green]

   ' Calculate visibility break line

[/color]

   If NumberOfNames < MAX_NAMES Then
     nVisibleBreakLine = GetVisibleBreakLine(NumberOfNames, LEFT_TEST, TOP_TEST)
   Else
     nVisibleBreakLine = Me.Height - HEIGHT_ADJUST
   End If

[green]

   ' Set visibility above/below visible break line

[/color]

   For Each oControl In Controls
     oControl.Visible = (oControl.Top < nVisibleBreakLine)
   Next oControl

[green]

   ' Position command buttons and make visible

[/color]

   For Each oControl In Controls
     If oControl.Name Like "CommandButton*" Then
       oControl.Visible = True
       oControl.Top = nVisibleBreakLine + COMMAND_BUTTON_ADJUST
     End If
   Next oControl

[green]

   ' Re-size form for aesthetics

[/color]

   Me.Height = nVisibleBreakLine + HEIGHT_ADJUST
   
End Sub

Private Function GetVisibleBreakLine(NumberOfNames As Integer, _
             LeftToTest As Single, FirstTop As Single) As Single
Dim Tops(15) As Single
Dim nTops As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As Single
Dim oControl As Control

[green]

  ' Populate array of Top positions used by first
  ' item on each row (where below "FirstTop")

[/color]

  For Each oControl In Controls
    With oControl
      If (.Left < LeftToTest) And (.Top >= FirstTop) Then
        Tops(nTops) = .Top
        nTops = nTops + 1
      End If
    End With
  Next oControl

[green]

  ' Sort the array

[/color]

  For i = 0 To nTops - 2
    For j = i + 1 To nTops - 1
      If Tops(j) < Tops(i) Then
        Temp = Tops(i)
        Tops(i) = Tops(j)
        Tops(j) = Temp
      End If
    Next j
  Next i

[green]

  ' Return the value between NumberOfNames-1 and NumberOfNames

[/color]

  GetVisibleBreakLine = (Tops(NumberOfNames - 1) + Tops(NumberOfNames)) / 2
      
End Function

[/color]

And here is the supporting code module:
[blue]

Option Explicit
Public NumberOfNames As Integer

Sub ShowForm()
  NumberOfNames = 4
  UserForm1.Show
End Sub

[/color]

 

[Nelviticus]

You could do it this way:

For i = 1 to 10
   If ThisWorkbook.Worksheets("sheet1").Cells(6, i) = "" Then
   myForm.Controls("TextBox" & Str(i)).Enabled = False
next i

N.

 

[Nelviticus]

Oops, correction - you need a

Trim

in there, like this:

For i = 1 to 10
   If ThisWorkbook.Worksheets("sheet1").Cells(6, i) = "" Then
   myForm.Controls("TextBox" & Trim(Str(i))).Enabled = False
next i

N.

 

[FlintBeastwood]

Thanks Nelviticus, that's exactly what I needed.

 

[Zathras]

I must be in the wrong line of business. I could have sworn that FlintBeastwood said

....If there are less than 10 names, I do not want to see the controls that are not in use...

So some code that simply disables (not hides) the controls is

...exactly what I needed...

[flush]

 

[FlintBeastwood]

Well, OK not exactly, I needed to replace the &quot;.enabled&quot; with &quot;.visible&quot; and add an &quot;end if&quot; but, that's just details!