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!

 

[dave3009]

Could you not just hide it?

myCheckBox.Visible = False

 

[Norie]

Craig

Why do you even want to do this?

Remove is probably what you need to use but I've tried lots of variations without any luck.

Adding/removing/manipulating controls at run-time is a difficult thing to attempt.

You'll almost inevitably end up with the error(s) you describe and also a very sore head.

So why bother? There's almost always another approach/solution.

 

[craig.penny]

Well, the form is going to get used many (but unknown how many or exactly what the use will be) times each time the code runs and for each different use it will need different controls based on unknown data that will be loaded by the user. I know the format of the data but not how many variations of the particular information there will be. I had originally written this as an entirely dynamic form so that nothing existed before running it and everything worked surprisingly well - except - if the user closed it then wanted to reopen and edit the information, anything entered was lost and had to be retyped. Now the bare form is created at design time, all the frames, checkboxs, command buttons, and all associated code is written at run time and the form is sized according to the controls that wind up on it. After the info from the form is stored in a dynamic array (necessary because the user can choose to work the data in a number of side-by-side comparisons), the code is deleted (which works fine) and now all I have left to do is get the controls off.

The entire application is 5000+ lines long or I would post it. I hope I'm explaining this properly. I guess the point is that I just need to know how to get the control off the form at run time.

Thanks again!

 

[Norie]

Craig

This sounds like an absolute nightmare.

You appear to be making things harder for yourself.

Writing code that dynamically creates controls, and also code for those controls is not for the faint-hearted.

Personally I wouldn't try it in a 'real' environment - might try it just out of curiosity but I wouldn't use it in reality.

Not only is it a hard thing to do but it might even raise some security issues, compatibility issues, sanity issues.

Since I don't know anything about your data or what you are dealing with I can't really make any suggestions of an alternative approach.

But I think you should perhaps have a bit of a rethink about the whole project.

 

[craig.penny]

Well, the thing has been in use now at a federal goverment level so I know there are no security issues and once I got the hang of dynamic forms, things are quite easy which honestly surprised me. Now everything is still good but the controls. I have had no issue creating them, their event handling code, or storing information from them. I just need to take them back off.

Believe me, I'm not making this harder than it has to be. I'm not interested in trying code just for the sake of philosophical interest.

 

[craig.penny]

Anyone?

 

[SeaDragon]

I'm in the same boat as you, Craig. I'm only posting because I'm surprised at Norie, with 46,000 posts, recommending that you abandon your dynamic form. I completely agree with you that dynamic forms are really quite simple once you dip your toes in the water, and are far too powerful to just abandon for something as silly as not knowing how to delete a control.

I also have a dynamic form, which I'm using to parse semi-colon delimited data from a raw XML table. (It's a tool meant to make it easier to edit this data across columns by doing it all together.) I'm also looking for code to delete controls. I'm working on an 'add item' button to create another row for users to enter data in. I'd like to do this the simple and elegant way, by calling the routine I use to write the data back into its semi-colon delimited cells (plus an additional semicolon), deleting the controls, and then re-running the routine I used to parse the data. I had this working already by just writing the data, unloading the form, and re-showing it, with the only drawback being that it kept defaulting the multipage control I'm using back to it's initial tab (annoying.)

(What's funny now is that in the process of typing this reply I just came up with a better way; I'm just going to store the last page worked with to a var and pick it up at initalize.)

(Also: sorry for necromancing.)

 

[SeaDragon]

initalize.

Ugh. Initialize.

Even though I'm sure you've figured this out, I'm going to post what I got to work for reference's sake. I used the .clear method on each of my pages.

From the VBA help / Remove Method (Forms) / Example:

Private Sub CommandButton2_Click()
    MultiPage1.Pages(0).Controls.Clear
End Sub

Private Sub CommandButton3_Click()
    If MultiPage1.Pages(0).Controls.Count > 0  Then
        MultiPage1.Pages(0).Controls.Remove "MyTextBox"
    End If
End Sub

 

[Norie]

SeaDragon

It's simple?

What about the adding code for the dynamically created controls?