Button visible on 'last sheet only' not working as expected

[kjg]

In the attached workbook I have 1 sheet with a command button to add another sheet to the end, this works fine.

I only want the button to be visible on the last sheet.


I “seem” to have the code in place for this to happen, (code from the web and modified) but, it doesn’t happen as expected.


If I choose the sheet before the last sheet, the button is still visible. In debug mode, using debug.print, I get the expected results in the Immediate Window.


Strangely, when on the sheet, if I 'enter and exit' Design Mode, the button disappears.


I have also noticed, when I press the Add New Sheet button, I get an extra tab on the tool bar Drawing Tools.


Anyone got a solution?

[Keebellah]

Hi. do you want the button to be visible or why not just have the button appear on the last added sheet.
What I mean, just 'move' the button to the newest sheet and while the button is there the sheet should be read-only until you add a new sheet.

[kjg]

That sounds as if it might be something that could work. I have never heard of this sort of proceedure before. How would you do that? I have no idea how to do it

[Keebellah]

I played around a little but I guess that wasn't such a bright idea.
I did however modify your code a little, the worksheet with the button visible is protected (no password) all other unprotected.
Of course the code could be more elaborated.
When you delete worksheets it could be that the button remains invisible.
Press Shift+Ctrl+N to run the macro and the button will showup again

[kjg]

Thank you for that solution, that will do what I need. I will play with it and try to learn.