Best method for changing control properties at design time

[Cloust]

Hi

I have set up 20+ user forms with approximately 50 controls in each one. When designing the forms, it seemed inefficient to give a unique meaningful name to each and every control, so they are currently named randomnly, i.e CheckBox1 could be beside CheckBox101 but still related to the same topic.

What makes each control meaningful is a separate label control beside it. I'm looking for the best way to capture the label control text, which is never further than 10 points to the right and 3 points up or down from the control, and paste it as the control's name, tag or caption property.

Note that for checkboxes, I don't use the caption field because the text placement is too restrictive. I prefer to use a separate label control and not show the checkbox's caption.

From there, I can save the result of the user's input next to the new (meaningful) control name on my worksheet as a field identifier. For example, if CheckBox1 has a label with the text "Side 1 good" beside it, I'll save the new control name (ex: Side1good" or the tag or caption property set as "Side 1 good") in column A and the control's value in column B.

When I test to change a control property at design time with code, my procedure never seems to permanently change the targeted property. Debug.print will show me that the property is changed but the Explorer Window does not reflect that. Why? (Please note that it would not be a problem if I were to have only a few controls, however, I have more 1000 of these to integrate in my forms and I need to find a quick and/or efficient way to set it up.)

Thanks in advance for any help you can give me.

 

[offthelip]

I would do this in two stages, first writing out a list of the checkboxes and the labels andwhere they are in two lists. Thne check this list to see that the label associated with a checkbox is the correct checkbox. Then only once the list is correct go and rename all the checkboxes. Thse two subroutines do this.
Note i added a counter into the rename routing just ot make sure the names were all unique. ( you can't name a checkbox with the same name as a label)

Sub Listobjects()
    Dim tt As String
    Dim ole As OLEObject
  indc = 1
  indlab = 1
    'Loop through all the active x controls
    For Each ole In Sheet1.OLEObjects
           If TypeName(ole.Object) = "CheckBox" Then
            Cells(indc, 1) = ole.Name
            Cells(indc, 2) = ole.TopLeftCell.Address
            indc = indc + 1
           End If
           If TypeName(ole.Object) = "Label" Then
            Cells(indlab, 3) = ole.Name
            Cells(indlab, 4) = ole.TopLeftCell.Address
            indlab = indlab + 1
           End If
           
    Next ole


End Sub


Sub renamechk()
    Dim tt As String
    
    Dim ole As OLEObject
  indc = 1
 
    'Loop through all the active x controls
    For Each ole In Sheet1.OLEObjects
           If TypeName(ole.Object) = "CheckBox" Then
           If Cells(indc, 1) = ole.Name Then
            ole.Name = Cells(indc, 3) & indc
            ole.Object.Caption = Cells(indc, 3) & indc
            indc = indc + 1
           End If
           End If
    Next ole


End Sub

 

[Cloust]

Thanks offthelip

Your suggestion to create 2 lists for verification is excellent.
My controls are all inside Userforms not Worksheets. Wouldn't looping through checkboxes and labels yield Name and Position but not necessarely the desired label, closest to the checkbox? Will the code be similar for checking all Userforms?

 

[offthelip]

I have broken this down into three steps, first list all the checkboxes and labels on all the userforms, the calculate the distance to the nearest label for each checkbox, then rename all the checkboxes. Obviously you may need to rearrange the list after the distance calculation.

Sub Listobjects()
    Dim tt As String
    Dim cntr As Control
    Dim usf As Object
    
  indc = 1
  indlab = 1
    'Loop through all the active x controls
    For Each usf In ThisWorkbook.VBProject.VBComponents
       If usf.Type = 3 Then
         For Each cntr In usf.designer.Controls


           If TypeName(cntr) = "CheckBox" Then
            Cells(indc, 1) = usf.Name
            Cells(indc, 2) = cntr.Name
            Cells(indc, 4) = cntr.Top
            Cells(indc, 5) = cntr.Left
            indc = indc + 1
            End If
           If TypeName(cntr) = "Label" Then
            Cells(indlab, 6) = usf.Name
            Cells(indlab, 7) = cntr.Name
            Cells(indlab, 8) = cntr.Top
            Cells(indlab, 9) = cntr.Left
            indlab = indlab + 1
            End If
         
         Next cntr
        End If


           
    Next usf




End Sub

Sub calcdistance()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 9))
  For i = 1 To lastrow
    mindist = 1E+27
    For j = 1 To lastrow
      dx = inarr(j, 8) - inarr(i, 4)
      dy = inarr(j, 9) - inarr(i, 5)
       dissqd = (dx * dx) + (dy * dy)
       If dissqd < mindist Then
         mindist = dissqd
         nearestlab = inarr(j, 7)
       End If
    Next j
    Cells(i, 10) = nearestlab
    Cells(i, 11) = mindist
  Next i
End Sub
Sub renamechk()
    Dim tt As String
    Dim cntr As Control
    Dim usf As Object
        
  indc = 1
 
    'Loop through all the active x controls
     For Each usf In ThisWorkbook.VBProject.VBComponents
       If usf.Type = 3 Then
         For Each cntr In usf.designer.Controls
   
            If TypeName(cntr) = "CheckBox" Then


             If Cells(indc, 2) = cntr.Name Then
            cntr.Name = Cells(indc, 7) & "chkbox"
            cntr.Object.Caption = Cells(indc, 7) & "chkbox"
            indc = indc + 1
           End If
           End If
       Next cntr
      End If
     Next usf


End Sub

 

[Cloust]

Thank you very much offthelip, this is great help!

A few questions.
- You seem to be catching any labels, left or right closest to the control? If so, you might catch some wrong labels to the left (some are stacked pretty tight).
- .Top, .Left will not work directly since I have controls (not all) grouped inside Frames. Inside a frame .Top, .Left are from the Frame position,not the UserForm. Is there an easy way around it? Do I have to loop inside every frames also?
- Why are you using a "Designer" property. Why do you need to access this graphical part of the UserForm? I'm not familiar with this?

 

[offthelip]

I have shown you the basic method I would use to approach your problem Unfortunately the actual solution depends very much on how your spreadsheet is laid out, because the vba has to search through each layer of objects, (userform, frames, controls )to find what you are looking for.
when trying find out how to address a particular item within an object, one method I use is to set up the loop through the objects. e.g the userforms

 For Each usf In ThisWorkbook.VBProject.VBComponents

and then put a breakpoint on the next line and look at the object in debug. I then browse within that object on debug to find what I am looking for and it usually gives me the correct way to address the child objejct or what ever I am looking for. That is how I found the correct way to address the control:

For Each cntr In usf.designer.Controls

I only specified top and left as the properties to record as an example, you could record other parameters to help try and identify which label relates to which control. (e,g Bottom as well as top).
In terms of designing the calculation to correctly identify which label is associated with which checkbox, that is something you need to do because it depends totally on how the controls are laid out. My calculation was simply taking the nearest to the top left corner, What I would suggest is recording the "Right" property for checkboxes and the "left" parameter for labels and just check for the minimum distance between, and the two tops being within some tolerance. But you will have to experiment to find the best solution, ( that is why I suggested doing in it stages).

 

[Cloust]

Thanks offthelip

I was wondering why the caption changes didn't reflect permanently in design mode (in the property window) until I realized that I need to use the Designer object.
Thanks a lot for your help!