none
VBA - Delete Specific UserForm Control RRS feed

  • Question

  • Hello friends,

    I have a label that I am trying to delete from a userform.

    This is what I have so far.
    The error is on the line

     If InStr(myCtrl.Name, "Label") = 1 And myCtrl.Caption = "Report" Then

    =================
    Sub Delete_Label()

    Dim myCtrl As control
    Dim i As Long

        With ThisDocument.VBProject.vbComponents("UserForm1").Designer

        For Each myCtrl In .Controls

         If InStr(myCtrl.Name, "Label") = 1 And myCtrl.Caption = "Report" Then

                .Controls.remove myCtrl.Name

            End If
        Next myCtrl

        End With
    End Sub
    ==========================


    please can you advise what I have done wrong.

    I have searched on google and tried many ways to fix this but its not working.


    thank you very much :)

    pw


    ps - i am not asking any one to write code for me i have done the above -

    its not working so pros please advise if you can

                           
    Monday, June 6, 2016 1:56 PM

Answers

  • It is working for me.

    Maybe VBA is messing the Control data type?

    Try changing your declaration to:

    Dim myCtrl As MSForms.Control


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Dan_CS Monday, June 6, 2016 5:34 PM
    Monday, June 6, 2016 4:35 PM

All replies

  • It is working for me.

    Maybe VBA is messing the Control data type?

    Try changing your declaration to:

    Dim myCtrl As MSForms.Control


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Dan_CS Monday, June 6, 2016 5:34 PM
    Monday, June 6, 2016 4:35 PM
  • Hello Felipe,

    If InStr(myCtrl.Name, "Label") = 1 Then

    works it deletes all the labels in the userform.

    I wanted to target only the label that has the caption "Report"

    When i add the second part

    And myCtrl.Caption = "Report" Then

    The error i get it Error 438 object does not support property :(


    • Edited by Dan_CS Monday, June 6, 2016 5:30 PM
    Monday, June 6, 2016 5:25 PM
  • Hello Felipe,

    I got rid of the  second part and  put in the label number that worked to delete the label

    Thank you for looking at this :)

    Monday, June 6, 2016 5:34 PM
  • There was no need to add-in the label number. You could equally have used

    For Each myCtrl In .Controls
        If myCtrl.Name Like "Label*" And _
            myCtrl.Caption = "Report" Then
            .Controls.Remove myCtrl.Name
         End If
    Next myCtrl

    or better still

    For Each myCtrl In .Controls
        If TypeName(myCtrl) = "Label" And _
            myCtrl.Caption = "Report" Then
            .Controls.Remove myCtrl.Name
        End If
    Next myCtrl

    Both of which remove only the requisite label

    The aim of what you are doing is not clear, but if you are making a userform that changes according to selections, rather than delete the control, which requires that the user has the security option set to trust the VBA Project Object Model, which you cannot reply upon, you could simply set the control's 'left' setting to a position to the right of the userform's right margin, so that it is effectively off screen. It's then just as easy to bring it back when required.


    Graham Mayor - Word MVP
    www.gmayor.com


    • Edited by Graham MayorMVP Tuesday, June 7, 2016 6:28 AM
    • Proposed as answer by Harry Soren Saturday, May 4, 2019 10:49 PM
    • Unproposed as answer by Harry Soren Saturday, May 4, 2019 10:50 PM
    Tuesday, June 7, 2016 6:23 AM
  • Thanks For the help 

    I ended with

    Sub DeleteFormFrameType(FormNa$, FrameNa$, TypeNa$)
       Dim wCtrl As MSForms.control
       With ActiveWorkbook.VBProject.VBComponents(FormNa).Designer
          For Each wCtrl In .Controls
             If wCtrl.Parent.Name = FrameNa And TypeName(wCtrl) = TypeNa Then
                .Controls.Remove wCtrl.Name
             End If
          Next wCtrl
       End With
    End Sub

    ' and the and's can be extended or decreased for whatever wCtrl.Properties exist


    farmer

    Saturday, May 4, 2019 10:59 PM