Pasting and deleting macro

[Closed]
Report
Posts
2
Registration date
Friday April 7, 2017
Status
Member
Last seen
April 8, 2017
-
 Blocked Profile -
Hello,
I need to create a macro that paste macro buttons from one spread sheet to another spread sheet, but when I try to do this and assign the macro to a button the macro instead paste a picture of the buttons and they cannot be used. Then I need another macro on that page to delete those buttons when needed. Is this possible?!
Thank you,
Dustin


3 replies

I found this and scraped it from another page:

Sub makeabutton()
  Dim Dynbtn As Button
  Application.ScreenUpdating = False
  ActiveSheet.Buttons.Delete
  Dim t As Range
  For i = 2 To 6 Step 2
    Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))
    Set Dynbtn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
    With Dynbtn
      .OnAction = "Pushed"
      .Caption = "DynamicBtn " & i
      .Name = "DynamicBtn" & i
    End With
  Next i
  Application.ScreenUpdating = True
End Sub

Sub Pushed()
 MsgBox(Application.Caller &" was pushed.")
End Sub


that should help in what you are doing.....

It's kind of fun to do the impossible! -Walter Elias Disney
Posts
2
Registration date
Friday April 7, 2017
Status
Member
Last seen
April 8, 2017

I'm having a little bit of a hard time understand this, is there anyway you could explain a little bit?!
Please and thank you for the reply

Sure....

For i = 2 To 6 Step 2


Set up a for loop inititializing i starting from 2 to 6 and skipping everyother digit (evens).

Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))


Now, we are setting up "t" to change with every Loop of i, by changin the cells.

    Set Dynbtn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)


Here, we initialize DynBtn, and instantiate it as a BUTTON, at the cells, set by "t".

With Dynbtn
.OnAction = "Pushed"
.Caption = "DynamicBtn " & i
.Name = "DynamicBtn" & i
End With


The above WITH statement, just starts to setup the BUTTON with the details. So the .OnAction is the SCRIPT to run when the button is pushed. You could also dynamically setup the script that each runs (using t-as in: .OnAction = "Pushed" & t. These would run the Scripts "Pushed2", "Pushed4", and "Pushed6"). It also makes the DISPLAYED name of each button with "DynamicBtn2" (as t=2 the first time it loops through). It also names each button with "DynamicBtn2" (as t=2 the first time it loops through).


Next i

The above NEXT, loops through the I until it reaches 6 (counting By 2 [Step 2] of the For i code)

If you were to change the OnAction results, then make certain you change the following code:

Sub Pushed()
MsgBox(Application.Caller &" was pushed.")
End Sub


Into.....

Sub Pushed2()
MsgBox(Application.Caller &" was pushed.")
End Sub


Now each dynamic button can perform a different script!


I hope this has helped!

Have FUN!