title: VBA - CommandButton Click sub - when the worksheet is created new each time
body: <p>This feels like a stupid question but I can't figure it out, so here goes...</p>
  
  <p>I have a workbook which, based on what's input into a userform, creates a new worksheet each time a command button on the userform is clicked, and deletes the old one.</p>
  
  <p>That means that, although the new worksheet is always given the same name ("Results"), the sheet number increments each time; it's currently calling itself Sheet48 and next time I run it it will be Sheet49.</p>
  
  <p>What I'd like to do is add a command button to that "Results" sheet, which users can click to take them to a different worksheet.  I've got as far as adding some code into the userform which creates the command button itself on the "Results" worksheet.  I haven't yet written the sub which would tell it to take the user to a different worksheet, but I'm confident that I could do this without much trouble.</p>
  
  <p>My question is how I can assign the sub to the button, considering that the worksheet it's located on is not permanent?  I thought the sub should go into a standard module, but all the examples I see for this kind of thing begin with "CommandButton1_Click", or "CommandButton2_Click", and are stored within the sheet object itself.  It seems like these numbers 1 and 2 refer to the command button's order on a given worksheet, but presumably a sub on a standard module won't know which worksheet to look at.</p>
  
  <p>I hope that makes sense - any helpful explanations will be much appreciated!  Thanks.</p>
  
  <p>EDIT: 
  This is the code I'm using to create the command button and to (attempt to) assign the sub to it:</p>
  
  <pre><code>'Add a command button, which will allow users to jump to the full report
  
  ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
          , DisplayAsIcon:=False, Left:=1025, Top:=130.5, Width:=160, Height:= _
          400).Select
  
  ActiveSheet.CommandButton1.Caption = "Click here to see the original report"
  ActiveSheet.CommandButton1.WordWrap = True
  ActiveSheet.CommandButton1.OnAction = "Module1.ButtonForOriginal" 'the sub works in isolation, but this line isn't assigning it.
  </code></pre>
  
  <p>Can anyone see any issues there?  The ButtonForOriginal sub procedure works in isolation, so it looks like I'm just not calling it properly?</p>
  
  <p>EDIT 2 - I have now achieved the right effect with a different method - see my answer posted below if you're interested.  Thanks to those who helped and set me on the right path!</p>