Assign macro to a format control button issue

[mysticmario]

Hi,
can someone tell me how to bypass this?
I have a template sheet for my projects summary. So everytime i create a new summary this template is copied as a new summary sheet. On this sheet I have few navigation and function buttons. The macros behind those buttons are stored on the template sheet itself, however when I copy the sheet the assigned macro tries to reffer to that template sheet instead of current sheet.
So let says I have a Arkusz22(Sheet22 in english) and template has a number 9. see below screenshot, where I have to manually change Arkusz9 to Arkusz22 in order to be able to execute the macro.
I know I can store the macro in a module, but the issue with that is, if i decide to copy this summary to a new workbook, I lose all the buttons functionality. Because whenever I click the button in new workbook it opens previous workbook to use this module with the macro which is not optimal to say the least. I also cannot use ActiveX buttons because this breaks co-authoring. Any ideas?

 

[RoryA]

That just means that whatever sheet name you passed doesn't actually exist.

 

[RoryA]

Ah - this line:

ans = ans = Me.OpenProjects.Value

should be this:

ans = Me.OpenProjects.Value

 

[mysticmario]

Oh ****! Well spotted, I missed that spelling error, and I even doubled down on that in create_button2 routine. I kept thinking it has something to do with cellAdresse, that's what happens when u don't fully understand the code you have.
Thank you!

 

[mysticmario]

I jumped the gun thinking that the work is done. The vba created buttons do not work. Thye have proper macro names assigned to them, but they act like the macro does not exist on the sheet

 

[mysticmario]

Looks like the macro name has to be preceded by current sheet number. In this case sheet182.WyszukajProjektG_Click

 

[mysticmario]

VBA is killing me... I thought of an idea implemented it and it looks like this:

ActiveSheet.Name = sheetName

sheetnumber = "Arkusz" & ActiveSheet.Index & "."
i = 0
i = i + 1
Call Create_Button(sheetName, "D1:E1", "Zapisz i zakończ", sheetnumber + "ZapiszWchmurze_Click", i)
i = i + 1
Call Create_Button(sheetName, "G1:H1", "Sprawdź magazyn", sheetnumber + "magazyn_Click", i)
i = i + 1
Call Create_Button(sheetName, "J1:L1", "Utwórz plik precyzyjnej wyceny", sheetnumber + "fullprice_Click", i)
i = i + 1
Call Create_Button(sheetName, "M1:N1", "Utwórz kartę realizacji", sheetnumber + "kartarealizacji_Click", i)

Unfortunately instead of taking actual sheet number which in this case is 4 it gives me well... Actual index of the sheet in the workbook in this particular workbook it is 8. In my main workbook it migth eb aswell 200+.
Is there a way to retrieve actual sheet number? I cant seem to find anything on the internet, atelast for now.

 

[RoryA]

You need the code name of the sheet, not its index. So use Sheets(sheetname).codename

 

[mysticmario]

@RoryA yup iIfigured that i was just typing a response while you responded

.CodeName - that's the method, so for future readers.

ActiveSheet.Name = sheetName

sheetnumber = ActiveSheet.Index & "."
i = 0
i = i + 1
Call Create_Button(sheetName, "D1:E1", "Zapisz i zakończ", sheetnumber + "ZapiszWchmurze_Click", i)
i = i + 1
Call Create_Button(sheetName, "G1:H1", "Sprawdź magazyn", sheetnumber + "magazyn_Click", i)
i = i + 1
Call Create_Button(sheetName, "J1:L1", "Utwórz plik precyzyjnej wyceny", sheetnumber + "fullprice_Click", i)
i = i + 1
Call Create_Button(sheetName, "M1:N1", "Utwórz kartę realizacji", sheetnumber + "kartarealizacji_Click", i)

Thank you so much for your assistance. Without this forum i would so lost with my ambitious project.