Modeless userform won't stay on top when event code is added through VBA

[dasatryan]

My macro creates a simple modeless userform that lists all the worksheets as labels and assigns macro to each label to switch the activesheet when user clicks a worksheet name on the userform. Everything is done through vba.
The problem is that the userform doesn't stay on top after macro ends. What is interesting it works when i comment the lines that add code lines to the userform (myForm.codemodule.insertlines).
Did anyone had the same problem before? I really appreciate your help. Thank you! See my macro below:

Sub SwitchTab()


Application.VBE.MainWindow.Visible = False
Dim myForm As Object, x As Long, CodeLine As String
Dim NewLabel As MSForms.Label


Application.VBE.MainWindow.Visible = False
Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3) 'vbext_ct_MSForm


With myForm
    .Properties("StartUpPosition") = 0 'Manual
    .Properties("Left") = 780
    .Properties("Caption") = "Switch between Tabs"
    '.Properties("Name") = "Switch"
    .Properties("Width") = 100
    '.Properties("Height") = 12
End With


For x = 1 To ActiveWorkbook.Worksheets.Count
    Set NewLabel = myForm.designer.Controls.Add("Forms.Label.1")
    myForm.Properties("Height") = 12 * x + 40
    With NewLabel
        .Name = "Label" & x
        .Caption = ActiveWorkbook.Worksheets(x).Name
        .Top = 12 * x
        .Left = 10
        .Width = 80
        .Height = 10
        .Font.Size = 8
        .Font.Name = "Tahoma"
        .Font.Underline = True
        .ForeColor = &HFF0000 '&H00FF0000&
        .MousePointer = fmMousePointerCustom
        '.MouseIcon = "C:\Windows\Cursors\hand.cur"
        
        CodeLine = "Private Sub " & NewLabel.Name & "_Click()"
        myForm.codemodule.insertlines (x - 1) * 3 + 1, CodeLine
        CodeLine = "Activeworkbook.Worksheets(""" & NewLabel.Caption & """).Activate"
        myForm.codemodule.insertlines (x - 1) * 3 + 2, CodeLine
        myForm.codemodule.insertlines (x - 1) * 3 + 3, "End Sub"
        
    End With
Next


myForm.Properties("ShowModal").Value = False
VBA.UserForms.Add(myForm.Name).Show


End Sub

 

[RoryA]

That is, in my opinion, really not a good way to achieve this. It would be much better to create the form at design time (without any labels), create a class to handle the label clicks (since they're all basically the same) and then have the form's Initialize event create the labels, assign them to instances of the class and then store them in an array or Collection.

 

[RoryA]

For example:

Class called CLabelHandler:

Option Explicit

Public WithEvents lb As MSForms.Label

Private Sub lb_Click()
    ActiveWorkbook.Worksheets(lb.Caption).Activate
End Sub

Userform code:

Option Explicit
Private colLabels As Collection
Private Sub UserForm_Initialize()
    Dim oHandler As CLabelHandler
    Dim x As Long
    Dim NewLabel As MSForms.Label
    Dim lSheetCount As Long
    Set colLabels = New Collection
    
    lSheetCount = ActiveWorkbook.Worksheets.Count
    Me.Height = 12 * lSheetCount + 40
    
For x = 1 To lSheetCount
    Set NewLabel = Me.Controls.Add("Forms.Label.1", "Label" & x, True)
    With NewLabel
        .Caption = ActiveWorkbook.Worksheets(x).Name
        .Top = 12 * x
        .Left = 10
        .Width = 80
        .Height = 10
        .Font.Size = 8
        .Font.Name = "Tahoma"
        .Font.Underline = True
        .ForeColor = &HFF0000 '&H00FF0000&
        .MousePointer = fmMousePointerCustom
        '.MouseIcon = "C:\Windows\Cursors\hand.cur"
        Set oHandler = New CLabelHandler
        Set oHandler.lb = NewLabel
        colLabels.Add oHandler
        
    End With
Next
End Sub

 

[dasatryan]

Thank you for the advice, Rory.

The macro I posted actually is being called from another Sub. In general this will be an application that creates user reports based on user selection. It can be from 1 to 60. The application places the reports into a new book and can be run from different users desktops. I don't want to install a template on their computers and also don't want to make the app read anything from network. So I had to create it programmatically. I wrote this simple code to try it (as it would be really confusing for users to work with 60 sheets) and came up to this problem. I don't know is it an excel limitation or there is something that I don't know. I would appreciate any help with this.

 

[RoryA]

When you write code into the form you change the project. That causes a reset after that routine finishes, hence your form disappears. That's why I recommended the class approach.

 

[dasatryan]

Thank you for the explanation RoryA! It explains my problem.