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.

 

[My Aswer Is This]

Re: New Sheet with automatic naming & easy file navigation | Example added

Did you use script in post 19?

You should get a message box not a error

 

[TimvMechelen]

Re: New Sheet with automatic naming & easy file navigation | Example added

Yess you're right. But then the macro still creates a new sheet?

 

[My Aswer Is This]

Re: New Sheet with automatic naming & easy file navigation | Example added

Well that is true.

What do you want to happen.

Do you want that sheet deleted?
Or do you want a warning asking if you want it delete?

This thing of what to do if this or that happens gets complicated.

 

[TimvMechelen]

Re: New Sheet with automatic naming & easy file navigation | Example added

What do you want to happen.
- sheet deleted?
- warning asking if you want it delete?

I quess I want it deleted, but I don't want another sheet to be deleted as an accident.. So maybe if it's too complicated the macro is fine the way it is now. Or a warning if i want "sheetname" to be deleted

 

[My Aswer Is This]

Try this: It will warn you before deleting:

Sub Add_New_Sheet()
'Modified  5/15/2019  2:46:27 PM  EDT
On Error GoTo M
Dim ans As String
Dim Ln As String
Ln = Sheets(Sheets.Count).Name
ans = InputBox("Enter new sheet name", "Last sheet name shown below", Ln)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
Exit Sub
M:
MsgBox "That sheet name is already used or is a improper name"
ActiveSheet.Delete
End Sub

 

[My Aswer Is This]

Try this:
It will delete sheet without warning:

Sub Add_New_Sheet()
'Modified  5/15/2019  2:53:231 PM  EDT
On Error GoTo M
Dim ans As String
Dim Ln As String
Ln = Sheets(Sheets.Count).Name
ans = InputBox("Enter new sheet name", "Last sheet name shown below", Ln)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
Exit Sub
M:
MsgBox "That sheet name is already used or is a improper name"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

 

[TimvMechelen]

Thanks a lot!! they both work

 

[My Aswer Is This]

You welcome. So did you get a sheet navigation Userform listbox script to work? Or have you come up with another plan?
Or did you not like that plan and want another approach.

Thanks a lot!! they both work

 

[TimvMechelen]

I really liked the Userform listbox approach! It's better than I hoped

 

[My Aswer Is This]

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.