New Sheet with automatic naming & easy file navigation

[TimvMechelen]

Hi all,

I would like to have an excel file where I can easily navigate through the sheets.
I have sheets for each week, but when I need an extra week, I would like to add a next sheet with one click. A macro is required for that.
When that macro adds a new sheet, I would like the macro to rename that sheet a specific way: with year and weeknumber.

Other specific information is described in the example below.
https://www.dropbox.com/s/hseeolq720z88u3/Example Sheet navigation.xlsm?dl=0


Thanks for your help.

 

[TimvMechelen]

Do you know you can put this UserForm in your Excel Personal Folder.
And put a script like this in your Personal Folder: Userfrom1.Show Modeless
And then assign a shortcut key to the script and you can have this Userform Popup in all of your Workbooks just by pressing your shortcut key.

Just another thing you may want to know about.

What do you mean with my excel personal folder?

 

[TimvMechelen]

And yes, I really like this kind of thinks that make the workflow more efficient

 

[My Aswer Is This]

In your Vba Project Window where you see your Vba modules

You should see something like this:

Vba Personal

Do you see it?

 

[My Aswer Is This]

If you want to position your Userform in the same place every time you open it put this script in your initialize code:

UserForm1.StartUpPosition = 0
UserForm1.Left = 75
UserForm1.Top = 45

Play around with it till you have it positioned where you like

You will need to change 45 and 75 to what you like

 

[My Aswer Is This]

As far as the Personal Folder:

If in the Vba Project Window you see Vba Personal

Do this:

Create your Userform in a Normal Workbook

Then when you have it the way you want in the Vba Project window click on your UserForm
Then Drag the Userform into the Personal Folder
This makes a copy of the UserForm

Then you need a Module script in the Personal Folder to activate the script

So in your Normal workbook create a Module script like this:

Userform1.Show Modeless

Now Drag that module script into your Personal folder

Then go into the Ribbon where you see Macros and find your Macro in the Personal Folder select the Macro and choose Option and assign the script a Shortcut Key

Sounds hard but it's easy after you see how it works.

Then when your in any workbook just press the shortcut key you assigned to the Module script and presto you can open the same Userform in any workbook and it will load all the sheet names into the listbox on the active Workbook.

 

[TimvMechelen]

In your Vba Project Window where you see your Vba modules

You should see something like this:

Vba Personal

Do you see it?

I don't see the VBA personal in VBAProject window

 

[TimvMechelen]

If you want to position your Userform in the same place every time you open it put this script in your initialize code:

UserForm1.StartUpPosition = 0
UserForm1.Left = 75
UserForm1.Top = 45

Play around with it till you have it positioned where you like

You will need to change 45 and 75 to what you like

I changed the initialize code to this:

Private Sub UserForm_Initialize()'Modified  5/13/2019  1:25:05 PM  EDT


UserForm1.StartUpPosition = 0
UserForm1.Left = 75
UserForm1.Top = 45


Dim i As Long
ListBox1.Clear
For i = 1 To Sheets.Count
    ListBox1.AddItem Sheets(i).Name
Next


End Sub

But I get an error now.

 

[My Aswer Is This]

Is that the name of your UserForm?

Or might your UserForm be named Mary or Paul

If that were the case it should be:

Mary.Left=47
Mary.Top=76

Or to be sure try:

Me.StartUpPosition = 0
Me.Left = 45
Me.Top = 45

 

[TimvMechelen]

Is that the name of your UserForm?

Or might your UserForm be named Mary or Paul

If that were the case it should be:

Mary.Left=47
Mary.Top=76

Or to be sure try:

Me.StartUpPosition = 0
Me.Left = 45
Me.Top = 45

It works now, thanks

 

[My Aswer Is This]

Glad I was able to help you.

Did you try putting this Userform in your personal workbook where it can be used on any workbook like I explained in post 35?