Assign macro to a format control button issue

mysticmario

Active Member
Joined
Nov 10, 2021
Messages
323
Office Version
  1. 365
Platform
  1. Windows
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?
1663653646982.png
 
That just means that whatever sheet name you passed doesn't actually exist.
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Ah - this line:

VBA Code:
ans = ans = Me.OpenProjects.Value

should be this:

VBA Code:
ans = Me.OpenProjects.Value
 
Upvote 0
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!
 
Last edited:
Upvote 0
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
1665139431140.png

1665139499668.png
 
Upvote 0
Looks like the macro name has to be preceded by current sheet number. In this case sheet182.WyszukajProjektG_Click
 
Upvote 0
VBA is killing me... I thought of an idea implemented it and it looks like this:
VBA Code:
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.
 
Upvote 0
You need the code name of the sheet, not its index. So use Sheets(sheetname).codename
 
Upvote 0
@RoryA yup iIfigured that i was just typing a response while you responded

.CodeName - that's the method, so for future readers.
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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