Update userform

F

Formula11

Active Member
Joined
Mar 1, 2005
Messages
468
Office Version
  1. 365
Platform
  1. Windows
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"
VBA Code: 
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
VBA Code: 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Unload form_sheets
    form_sheets.Show vbModeless
End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Formula11 said:
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"
VBA Code: 
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
VBA Code: 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Unload form_sheets
    form_sheets.Show vbModeless
End Sub
Click to expand...
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.
 
Upvote 0
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.
 
Upvote 0
Formula11 said:
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.
Click to expand...
pls share the file for reference and testing
 
Upvote 0
Formula11 said:
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.
Click to expand...
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
 
Upvote 0
OK thanks for comments.
With regard to Excel file, there's nothing additional to code in original post.
Just add worksheets and test.
 
Upvote 0
Found this code which remembers form position.
link to code

It uses defined names ... I don't understand what RefersTo:="3" means though.
VBA Code: 
ActiveWorkbook.Names.Add Name:="frmLeft", RefersTo:="3"
ActiveWorkbook.Names.Add Name:="frmTop", RefersTo:="3"
 
Upvote 0
Formula11 said:
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"
VBA Code: 
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
VBA Code: 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Unload form_sheets
    form_sheets.Show vbModeless
End Sub
Click to expand...

You could save the Userform Top and Left settings to the Registry.

Read this first.
VBA Registry Functions

The advantage of this method is that you can save the userform position, come out of Excel and retrieve the settings when you load the userform the next time.

To demo this, set up a new userform with two Command Buttons

cmdSave and cmdGet with captions of Save and Get respectively.

Paste this code into the Userform Code Module.

Load the Userform and move it to another position.

Click on the Save button.

Move the Userform again and click on the Get button.

The Userform will return to the position it was in when you clicked on Save.

If you think that this method will be useful let me know,

VBA Code: 
Private Sub cmdSave_Click()

' SaveSetting

' SAVESETTING(appname, section [,key] [,setting])

' Writes to the registry and saves a section or key in the registry.

' appname The name of the application or project (String).
' section The name of the section (String).
' key (Optional) The name of the key to save (String).
' setting (Optional) The value you want to save (Variant).
  
  SaveSetting appname:="MyRegistrySettings", section:=Me.Name, Key:="Top", setting:=Me.Top
  
  SaveSetting appname:="MyRegistrySettings", section:=Me.Name, Key:="Left", setting:=Me.Left
  
End Sub

Private Sub cmdGet_Click()

' GetSetting

' GETSETTING(appname, section, key [,default])

'Reads from the registry and returns the value or key from the registry (String).

' appname The name of the application or project (String).
' section The name of the section (String).
' key The name of the key to return (String).
' default (Optional) The value to return if no value has been set (String).

  Me.Top = GetSetting(appname:="MyRegistrySettings", section:=Me.Name, Key:="Top", Default:=Me.Top)
  
  Me.Left = GetSetting(appname:="MyRegistrySettings", section:=Me.Name, Key:="Left", Default:=Me.Left)
  
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Hide userform title bar, and locking the userform into a relative position on the application.
2
Replies
16
Views
721
Jaafar Tribak
Jaafar Tribak
A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
267
RoryA
RoryA
G
  • Solved
Run-Time Error 381
2
Replies
12
Views
201
Micron
M
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
115
6StringJazzer
6StringJazzer
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
234
krunal123
K

Forum statistics

Threads
1,224,813
Messages
6,181,109
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top