Duplicate a control button

Rickinnocal

New Member
Joined
Dec 14, 2010
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hello, everyone.
I have a worksheet that performs four-way interpolation on values taken from paper tables.

It has to do this for 40 sets of data. After the four data points are entered for the first tank and it produces the interpolated result, I created a Command Button to push the answer to a second sheet.

This works fine. However, I need 40 buttons to do the push for each tank (Actually 20 tanks, each has a 'Before' and 'After' value) I can just make 40 buttons, but is there an easier way to duplicate them without having to manually enter each macro code?

The code for the button I already have reads :
Private Sub cmdSendData1_Click()

Worksheets("ROB (Before)").Range("i10").Value = Worksheets("Interpolator").Range("e9").Value

End Sub
The code for the second button would need to read :

Private Sub cmdSendData2_Click()

Worksheets("ROB (Before)").Range("i11").Value = Worksheets("Interpolator").Range("e15").Value

End Sub

The row of the destination cell increases by 1 for each button, but the row of the source cell increases by 6.

Is there an easy-ish way to do this?

Thanks.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
if this was a userform the answer is Yes, there is a very elegant way to do it, but i am not sure if it works on worksheet controls. it may if they are activeX controls but cant say for sure. it involves writing a class and assigning each commandbutton_click event to the class. having made this suggestion, there may be another member who is familiar with referencing classes from worksheets
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,078
You could make the 40 buttons with this code
VBA Code:
Sub Macro1()
    Dim i As Long
    For i = 0 To 39
        ActiveSheet.Buttons.Add(380 + 5 * i, 190 + 20 * i, 120, 30).Select
        With Selection
            .Name = "butRow" & Format(i, "00")
           .Characters.Text = "to row " & (10 + i)
           .OnAction = "Button_Click"
        End With
    Next i
End Sub

Which will assign them all to this macro
VBA Code:
Sub Button_Click()
    Dim index As Long
    index = Val(Right(Application.Caller, 2))

    Worksheets("ROB (Before)").range(10 + index, 9).Value = Worksheets("Interpolator").Cells(9 + 6 * index , 5).Value
End Sub

But I can't help thinking that there is an easier interface than 40 different buttons.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
i agree, that the 40 cmdbuttons is a bit inelegant. but thx for the interesting solution. i dont manipulate worksheets, i build pure userform driven applications and hide excel, none the less i will stash your code in my snippits library just in case. out of interest are the buttons created activeX.
Hope the OP appreciates the cleverness of this solution :)
 

Rickinnocal

New Member
Joined
Dec 14, 2010
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Thanks, everyone. I agree that the 40 buttons are inelegant, but I wanted to leave the destination cells able to be typed into directly without overwriting any interpolation formulae, as on many occasions the required result is a straight read out of the table.
 

Forum statistics

Threads
1,144,617
Messages
5,725,326
Members
422,615
Latest member
Mareza

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
Top