PDA

View Full Version : Can I delete a control from a running form with code?



xltrader100
08-31-2008, 10:04 PM
I have a userform where Excel has "lost" some of the controls. They aren't visible in the form editor and don't show up in the drop down list in the Properties Window so I can't select them, and I can't rename another control to the name of a missing control because I get an Ambiguous name error. The code still compiles, missing controls included, without any complaints about undefined variables, but I can't bring them into view in the running form, i.e. I can dimension them as visible at the top left of the form, and there's nothing there.

I found out how to create new controls with code: http://support.microsoft.com/kb/204330 but nothing on how to delete existing controls. Is this possible? Those missing control names were part of a numerical sequence for looping purposes and now there are gaps in the sequence.

mikerickson
08-31-2008, 10:12 PM
This might help you find out what's going onDim i as long
Dim oneControl as Object
For each oneControl in UserForm1.Controls
i = i+1
With oneControl
Cells(i,1).Value = .Name
Cells(i,2).value = .Left
Cells(i,3).Value = .Top
Cells(i,4).Value = .Width
Cells(i,5).Value = .Height
.Visible = True
End With
Next oneControl
and this is one thing that you might use
ThisWorkbook.VBProject.VBComponents("UserForm1").designer.Controls.Remove "CommandButton1"

JimmyTheHand
08-31-2008, 10:35 PM
Probably those controls are beyond the visible borders of the form. Try to enlarge the form as much as possible, maybe you can see and move/delete the controls then.

As a last resort, you can try this:
1) right click on the form, then Select All
2) Format (menu) -> Center in Form -> Horizontally
3) Format (menu) -> Center in Form -> Vertically

This way all controls go to the center of the form. You will need to move each one again to their proper positions, which might be a lot of work, but at least you will see all controls, even those that have been hiding so far.

Jimmy

shamsam1
08-31-2008, 10:49 PM
try this

http://www.vbaexpress.com/kb/getarticle.php?kb_id=599

xltrader100
08-31-2008, 11:46 PM
Thanks for the quick response, guys.

Shamsam, that ref. was to do with controls on a sheet, mine are on a form.

Jimmy, they aren't beyond the form borders, because Mike's first procedure actually found them, sort of.

They're all listed in the printout, in about the right area and about the right size where they should be, but they just aren't visible. I moved everything on the form down to the far right end, did a sweep with selection rectangles to make sure I didn't miss anybody, commented out all the initialization code that normally positions the controls, and then started up the form and ran that procedure again. All the missing controls were listed in the same position.

I think I know what causes this because I actually saw it happen once when an image control disappeared, and I have no trouble in duplicating the process now that I know what to look for. Try this and you'll see what I mean:

Take a form with a frame and an image control, this also works with textboxes and labels, but not Command buttons. Remove the text from the border of the frame. Put the image control inside the frame, let it go so the frame knows it belongs there, then grab the image ctl by the bottom border and drag it up just beyond the top border of the frame until you see the little outline appear to show the frame has released it. Then pull it back until the outline just disappears and drop it right there on the border. It's gone, baby gone. If you try to enlarge the frame to get it back, it just pushes the invisible image ctl up with it and you never get it back.

If you know which frame has swallowed your control, and if you know which border of the frame is the culprit, and if you still remember the name of the control, and if that control is still listed in the Properties Window dropdown list, then you can get it back by resizing the missing control until it pokes it's nose back down into the frame. That's 4 ifs, but the big one is the Properties dropdown list. After you add a couple more controls to the form, the missing control is no longer on the list, and at that point it enters the great void.

So, I don't know it that helps with my original question, but I'd sure like to get rid of these suckers.