New Sheet with automatic naming & easy file navigation

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
121
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.
 
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?
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
In your Vba Project Window where you see your Vba modules

You should see something like this:

Vba Personal

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

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
 
Last edited:
Upvote 0
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.
 
Upvote 0
If you want to position your Userform in the same place every time you open it put this script in your initialize code:

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:
Code:
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.
 
Upvote 0
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:
Code:
Me.StartUpPosition = 0
Me.Left = 45
Me.Top = 45
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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