CODEWITHSUNDEEP
excelvbamultipage
R. Patrick-White
R. Patrick-White

Reputation: 93

How do I permanently add a new multipage tab and name it?

I'm attempting to add a new tab to a multipage userform however I want the change to be permanent and still be there the next time I run the program.

I found a way to add in a tab but that only worked for that runtime. Then I found the code that follows and changed my trust settings to trust access to the VBA Project Model, however I keep encountering that the Object doesn't support this property?

Sub Test()

Dim vbComp As Object
Dim objCntrl As Control

Set vbComp = ThisWorkbook.VBProject.VBComponents("TabStripUserForm")

Set objCntrl = vbComp.Designer.Controls("ProductListTabStrip")

'add page

objCntrl.Pages.Add

'remove page

objCntrl.Pages.Remove (1)

End Sub

I want it to add a another tab to the userforms multipage however it says that the Object doesnt support this property or method. I've triple checked that the name of the userform and multipage are correct.

Upvotes: 2

Views: 812

Answers (1)

Vityata
Vityata

Reputation: 43595

If you are asking, to permanently add something here:

enter image description here

then, fortunately it cannot be done easily (probably there is some foggy way-around with some kind of reflection, but I am not sure it is a good idea). As probably noticed, if the code below is ran, then this should be shown only once:

enter image description here

Sub TestMe()

    Dim tabObj As Control
    Set tabObj = Form.Controls("Tabs")

    With tabObj
        .Tabs.Add "Gosho"
        .Tabs.Add "Pesho"
        .Tabs.Add "Atanas"
        Debug.Print .Object.Count
    End With

    Form.Show

End Sub

Why?

The reason is that the form is a blueprint, it is like a class. And adding a property to a class from code is somehow not a good practice.

Workaround?

The best workaround is to use the form with OOP and separation of concerns. Then, in the application, whenever it is called, the object which is created after the blueprint would appear. Instead of "breaking" the object, the form would be hidden. Then, through the application, if it is called again, it would be shown through the Hide option. Examples:

What about if the Excel file is closed completely?Would it work when it is opened with the new tabs?

No. But for this case, there should be another workaround, writing the tabs somewhere in a settings-worksheet and calling the form, taking into account the values of the settings-worksheet.

Upvotes: 3

Related Questions