-2

I need upon opening a spreadsheet to produce a button and automatically assign a macro I previously created. Let's call this macro "edit_colour".

I know how to execute a MsgBox with "Hello World" using the following,

Private Sub Workbook_Open()
MsgBox "Hello World"
End Sub
Community
  • 1
  • 1
jrodden
  • 39
  • 1
  • 6
  • You want a button to show up on a sheet, ribbon, or quick ribbon? Honestly, an add-in loading with XLSTART, and assigning the macro to a quick/ribbon button might be simpler. – Cyril Jan 10 '17 at 16:55
  • @Cyril yes indeed, to show up on the sheet as opposed to the Ribbon or Quick Access Toolbar (I should've been more specific). Unfortunately, I cannot just add it to the Ribbon or Quick Access Toolbar since it needs to be uploaded somewhere and once it's downloaded it removes those buttons. The macros are specific to each spreadsheet too so I can't just have a ribbon filled with various different macros, hence why I've had to resort to VBA – jrodden Jan 10 '17 at 16:57
  • Have you tried using a batch file to download? Not saying VBA can't work, but there is probably an easier way. Another option is to, rather than have a button, make a file specific to that macro to execute on initialize. – Cyril Jan 10 '17 at 17:00
  • What happens when you dismiss the MsgBox? – Comintern Jan 10 '17 at 17:02
  • 2
    "Include what you've tried" would include something at least *remotely* relevant to what you're trying to do. That "hello world" snippet is borderline trolling - your question is *too broad*, and lacks basic research. – Mathieu Guindon Jan 10 '17 at 17:03
  • @Comintern The above VBA is just an example of AutoOpening a MsgBox, what I'm trying to do is Something along the lines of; AutoOpen ==> Create Button ==> Assign Macro to Button ==> End Sub I'll post a snippet of what I have and some errors I have encountered. – jrodden Jan 10 '17 at 17:04
  • Google -> "VBA programmatically assign macro to button" – Mathieu Guindon Jan 10 '17 at 17:07
  • 1
    ...or just check under "Related" on the right-side of your question – Tim Williams Jan 10 '17 at 17:11

1 Answers1

2

This is how you can do it in the code:

Dim w as Worksheet
Dim b As Button

Set w = ActiveSheet
Set b = w.Buttons.Add(5, 5, 80, 18.75)       ' left, top, width, height
b.OnAction = "recalculateSomething"          ' a sub name in module
b.Characters.Text = "Recalculate Something"  ' text on the button

More here: How to add a button programmatically in VBA next to some sheet cell data?

Community
  • 1
  • 1
Logan Reed
  • 882
  • 7
  • 13