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

 

[HighAndWilder]

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

Without knowing why you want numerous Command Buttons on the Userform can I suggest a Listbox which you can refresh when sheets are added or deleted or renamed.

It would be even easier to refresh the Listbox or even the collection of Command Buttons when the Userform became active.

Bear in mind that each Command Button would need an Event Handler procedure and you only need one such procedure for the Listbox irrespective of how many items in it.

 

[Formula11]

OK thanks for the suggestion.

The reason I went for command buttons is that colour of sheet was carried over into buttons and made it easier to see.
Was going to have a tall narrow form on one side where you could see a lot of sheets.

 

[PrakashGunabalan]

OK thanks for the suggestion.

The reason I went for command buttons is that colour of sheet was carried over into buttons and made it easier to see.
Was going to have a tall narrow form on one side where you could see a lot of sheets.

pls share the file for reference and testing

 

[HighAndWilder]

OK thanks for the suggestion.

The reason I went for command buttons is that colour of sheet was carried over into buttons and made it easier to see.
Was going to have a tall narrow form on one side where you could see a lot of sheets.

So you still have to create the code under the button.

The sheet tab is not coloured when the NewSheet event handler runs.

You may want to look at this :
VBA Express : Excel - Group All Command Buttons on a Userform

 

[PrakashGunabalan]

sorry i couldnt download sample excel file ... pls share

 

[Formula11]

OK thanks for comments.
With regard to Excel file, there's nothing additional to code in original post.
Just add worksheets and test.