Update userform

[Formula11]

Using this code to show worksheets on a userform.

If I add a new sheet, want the form to update, while it's still running, and the form to stay in the same position.

Tried Workbook_NewSheet event but form position reverts to the original position.

Is there a refresh method with the form to stay in the same position?

IN USERFORM "form_sheets"

Option Explicit

Private Sub UserForm_Initialize()
    Call tabs_in_form
End Sub

'CREATE FORM
Private Sub tabs_in_form()
    Dim Box As MSForms.CommandButton
    Dim box_top As Long, box_height As Long, box_left As Long, box_width As Long, box_gap As Long
    Dim box_Name As String
    Dim my_sht As Worksheet
    Dim Index As Long

    box_height = 24: box_top = 12: box_left = 12: box_width = 110: box_gap = 0
    Index = 1

    'Create boxes
    For Each my_sht In Worksheets
        box_Name = "MyButton" & Index
        Set Box = Me.Controls.Add("Forms.CommandButton.1", box_Name, True)
        With Box
            .Height = 24: .Top = box_top: .Left = box_left: .Width = box_width
            .Caption = my_sht.Name
            .BackColor = my_sht.Tab.Color
        End With
        Index = Index + 1
        box_top = box_top + box_height + box_gap
    Next my_sht
   
    Me.Height = box_top + 48
    Me.Width = box_width + 48

End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    MsgBox "new"
    Call tabs_in_form
End Sub

IN WORKBOOK

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Unload form_sheets
    form_sheets.Show vbModeless
End Sub

 

[Skyybot]

OK thanks
Do you know what RefersTo:="3" means

Names.Add method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Formula11]

Thanks, I did see this but it's not clear

 

[Skyybot]

I think it's "dummy" info because the method requires at least one of the RefersTo arguments?

 

[Formula11]

OK thanks