CODEWITHSUNDEEP
vbaexcel
Sammir
Sammir

Reputation: 20

Manipulating objects in design view

I have worked on several projects now where I have been stumped by MS Excel and MS Access where I cannot use VBA to manipulate objects whilst in design view. They are very simple tasks such as changing the name of the labels on a userform in MS Excel or adding new textboxes to a report in MS Access but they seem to be impossible as any macro will only be used upon activation of said userform or report and so everything is just done in runtime.

To focus on one, In MS Excel I want to be able to run a code which will go through all the labels on my userform and rename them from "Label1", "Label2", etc... to "Lbl1", "Lbl2", etc... for instance but I cannot just click a button to run a macro on the design of the userform which would run through all the labels and change their names. If I am able to do this then I can be much more efficent with the rest of my runtime code. Without it, I have the option of renaming every single label I have but there are near 100 labels and I want to do this through VBA as I already can see that it would be very useful to know how to run code in design view of these Visual Basic elements.

Please could you advise whether it is possible to manipulate these VB objects in design view?

Upvotes: 0

Views: 599

Answers (3)

Rory
Rory

Reputation: 34075

You can run code from a normal module to alter the design of a userform. You must have trusted access to the VBA Project in your Trust Center settings. Then it's simply a question of using the Designer property of the relevant VBComponent:

Sub foo()
    Dim ctl                   As MSForms.Control
    Dim lCounter              As Long

    lCounter = 1

    For Each ctl In ThisWorkbook.VBProject.VBComponents("Userform1").Designer.Controls
        If TypeName(ctl) = "Label" Then
            ctl.Name = "lbl" & lCounter
            lCounter = lCounter + 1
        End If
    Next ctl
End Sub

The form must not be loaded when you run this.

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

No, it's not possible. If you have a designer open for Form1 and type Form1.Show in the immediate pane, the VBE will close the designer before it brings up the form. That's just the way it is.

Now, if you have labels Label1, Label2 and Label3, you have what I like to call meaningless identifiers. Renaming them to Lbl1, Lbl2 and Lbl3 will make things even worse.

And if you're referencing them anywhere in code, renaming them will break your code. It's even worse with buttons or anything else with events you're handling: renaming Button1 to Btn1 will not rename the Button1_Click() procedure to Btn1_Click().

Use meaningful names. If Label1 is sitting atop a textbox for, I don't know, some username, then name it lblUserName or UserNameLabel. Use meaningful names. Always.

I've written a refactoring tool for renaming things, that deals with event handlers and many other things; you could right-click a reference to Label1 in your code, select Refactor > Rename from the context menu, enter a new name, and the tool renames all references, and the control itself. The tool in question is called Rubberduck, and does quite a lot more than just letting you rename things, too.

If the labels aren't referenced anywhere in code, then they don't need a name. Why bother?

Upvotes: 1

Excel Hero
Excel Hero

Reputation: 14764

You can program the project from code if you set a special macro permission.

Details are on Chip's site: http://www.cpearson.com/excel/vbe.aspx

Upvotes: 1

Related Questions