Programmatically remove a control from userform

[craig.penny]

Greetings,

Suppose I have a userform named myUserForm and a checkbox on it named myCheckBox. How on earth do I remove myCheckBox from myUserForm at run time? I've tried every which way I can think and I always get "error 444". Some things I've tried are:

myUserForm.Controls.Remove "myCheckBox"
myUserForm.Controls.Remove(myCheckBox)
myUserForm.Controls("myCheckBox").Remove
myUserForm.Controls.RemoveControls "myCheckBox"

I've put this in a standard module, the ThisWorkbook module, even in the myUserForm code module and each time it's the same error.

Thanks in advance!

 

[craig.penny]

First, let me address the obvious fact that my code is barely commented and there's tonnes of variables. Sorry. I would take the time right now to make it more understandable but I'm too busy at work. Please ask me about specifics if any of it looks like what you might need.

  Set MDSByYear = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
 
  MDSByYear.Properties("Caption") = "MDS By Year - Case " & SelCaseCount
  MDSByYear.Properties("Height") = (mdsSize + 2) * 25
  MDSByYear.Properties("Width") = (maxYear - minYear + 2) * 45 + 40
  Randomize
  MFName = "UF" & CStr(Int(Rnd * 100000) + 1)
  MDSByYear.Properties("Name") = MFName
 
  For loopCount = 1 To mdsSize
    Set MDSHandle(loopCount, minYear - 1, SelCaseCount) = MDSByYear.Designer.Controls.Add("forms.frame.1", , True)
    With MDSHandle(loopCount, minYear - 1, SelCaseCount)
      .Width = (maxYear - minYear + 2) * 45 + 35
      .Height = 25
      .Top = (loopCount - 1) * 25
      .Left = 10
      .ZOrder (0)
      .Caption = MDSinputs(loopCount)
    End With
 
    For YrLoopCount = minYear To maxYear
      Set MDSHandle(loopCount, YrLoopCount, SelCaseCount) = MDSHandle(loopCount, minYear - 1, SelCaseCount) _
          .Controls.Add("Forms.checkbox.1", , True) 'don't need to use the .designer thing because it's adding a
          'control to another control that's already been created
      With MDSHandle(loopCount, YrLoopCount, SelCaseCount)
        .Width = 44
        .Height = 25
        .Top = 0 'this is relative to the control that it's been added to
        .Left = 10 + (YrLoopCount - minYear) * 45
        .ZOrder (0)
        .Caption = YrLoopCount
        If SelCaseCount > 1 Then
          .Value = MDSHandle(loopCount, YrLoopCount, SelCaseCount - 1)
        Else
          .Value = False
        End If
        If dataExists(loopCount, YrLoopCount) = False Then .Enabled = False
      End With
      If SelCaseCount > 1 Then
        MDSHandle(loopCount, YrLoopCount, SelCaseCount).Value = _
          MDSHandle(loopCount, YrLoopCount, SelCaseCount - 1).Value
      End If
    Next YrLoopCount
 
    Set MDSHandle(loopCount, maxYear + 1, SelCaseCount) = MDSHandle(loopCount, minYear - 1, SelCaseCount) _
      .Controls.Add("Forms.checkbox.1", , True)
    With MDSHandle(loopCount, maxYear + 1, SelCaseCount)
      .Width = 75
      .Height = 25
      .Top = 0
      .Left = 10 + (YrLoopCount - minYear) * 45
      .ZOrder (0)
      .Caption = "All Years"
      .Value = False
      If Not (showYears) Then .Enabled = False
    End With
    If SelCaseCount > 1 Then
      MDSHandle(loopCount, YrLoopCount, SelCaseCount).Value = _
        MDSHandle(loopCount, YrLoopCount, SelCaseCount - 1).Value
    End If
 
  Next loopCount
 
  Set DoneButton = MDSByYear.Designer.Controls.Add("forms.commandbutton.1", "DoneButton", True)
    With DoneButton
      .Width = (maxYear - minYear + 2) * 45 + 20
      .Height = 25
      .Top = mdsSize * 25
      .Left = 10
      .ZOrder (0)
      .Caption = "Done"
      .Value = False
    End With
  With MDSByYear.CodeModule
    Line = .CountOfLines
 
    .InsertLines Line + 1, "Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)"
    .InsertLines Line + 2, "Cancel = True"
    .InsertLines Line + 4, "End Sub"
 
    .InsertLines Line + 5, "Private Sub DoneButton_Click()"
    For loopCount = 1 To mdsSize
 
      For YrLoopCount = minYear To maxYear + 1
 
        .InsertLines Line + 10 * (maxYear - minYear + 5) + YrLoopCount - minYear + 1, _
        "BootBoolean(" & loopCount & "," & YrLoopCount & "," & SelCaseCount & ") =" _
        & "me.checkbox" & (loopCount - 1) * (maxYear - minYear + 1 + 1) + (YrLoopCount - minYear + 1) & ".Value"
 
      Next YrLoopCount
 
    Next loopCount
 
    Line = .CountOfLines
    .InsertLines Line + 5, "Me.Hide"
    .InsertLines Line + 10, "End Sub"
 
    Line = .CountOfLines
    .InsertLines Line + 1, "Private Sub UserForm_Initialize()"
 
    For loopCount = 1 To mdsSize
 
      For YrLoopCount = minYear To maxYear + 1
 
        .InsertLines Line + 10 * (maxYear - minYear + 5) + YrLoopCount - minYear + 1, _
        " if BootBoolean(" & loopCount & "," & YrLoopCount & "," & SelCaseCount & ") =" _
        & "true then me.checkbox" & (loopCount - 1) * (maxYear - minYear + 1 + 1) + _
        (YrLoopCount - minYear + 1) & ".Value=true"
 
      Next YrLoopCount
 
    Next loopCount
 
    Line = .CountOfLines
    .InsertLines Line + 5, "End Sub"
 
  End With
 
  MacomSelection.PassTheGDName MFName

Yeah I know, it's a mess. Really though I will try to remember and explain how everything works but specific parts would be better right now.

Regards!

Edit: On looking at it again I'm having a hard time seeing where I took the controls off. I think maybe they were automatically removed when I got rid of the form altogether.

ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=MDSByYear
Set MDSByYear = Nothing

 

[SeaDragon]

SeaDragon
It's simple?

What about the adding code for the dynamically created controls?

Are you asking to see the code, or just disagreeing?

I did qualify that statement with 'once you dip your toes in the water', and they are pretty straightforward compared to the alternatives (given that your situation really does beg for dynamic userforms.)

Besides that semi-colon delimited data I was referring to, I'm also dynamically populating a user options dialog box from a table hidden in an add-in (I just mucked it up by adding frames to it, unfortunately, or I'd post the code (tinkering with writing the values back to the table))

 

[natekris8183]

I am certain, as old as this post is, there's a solution that's been made but I know as I was googling for this topic I couldn't find anything. This is the specific solution to this individual programming issue, BUT I was working with a userform that looked up results within a worksheet and listed the results by adding labels with captions relative to the results themselves. However, I wanted to labels to be removed upon updating of the combobox being changed. So this is in reference to the change in the combobox and removing the dynamically added labels for that same userform.

Private Sub ComboBox1_AfterUpdate()
Dim cCtrl As Control
Dim lName As String
For Each cCtrl In Me.Controls
    If TypeName(cCtrl) = "Label" Then
        lName = cCtrl.Name
        ViewTeamsMenu.Controls.Remove lName
    End If
Next cCtrl
End Sub

Again, the ONLY labels on this particular forms are the ones being added and removed, so my qualified of TypeName being "Label" could be more complex if you speficially named the labels (ie. I have "teams" in all the label names, so I could have also added "And If cCtrl.Name Like "team") to assure I don't accidentally delete a label I want to keep.

 

[mikerickson]

The Controls.Remove method will only remove controls that are added at run-time. Controls added at design time cannot be removed by this method.

The argument of the Controls.Remove method is a string, the name of the control to be removed.

 

[natekris8183]

Precisely. You can't remove something that doesn't exist, but that is not how I interpreted the issue. The original post present the problem of removing "MyCheckBox". The following is both creating the label and then removing the same label within the same loop.

Private Sub IndSTSenterbutton_Click()
Dim Lo As ListObject
Dim nTeams As Long
Dim ws As Worksheet
Dim tLevel, lName As String
Dim theLabel As Object
Dim cCtrl As Control
tLevel = Me.ComboBox1.Value
Set ws = Worksheets("Lists")

For Each Lo In ws.ListObjects
    If Lo.DataBodyRange.Cells(0, 1) = tLevel Then
        nTeams = Lo.DataBodyRange.Count
        
        For i = 1 To nTeams
            Set theLabel = ViewTeamsMenu.Controls.Add("Forms.Label.1", "team" & i, True)
            With theLabel
                .Caption = Lo.DataBodyRange.Cells(i, 1)
                .Height = 22
                .Top = 164 + (20 * i)
                .Left = 38
                .Width = 244
                .TextAlign = 2
                .FontSize = 9
                .Font.Bold = True
            End With
        
            For Each cCtrl In Me.Controls
                If TypeName(cCtrl) = "Label" Then
                    lName = cCtrl.Name
                    Me.Controls.Remove lName
                End If
            Next cCtrl
        
        Next i
    End If
Next
End Sub