Control Toolbox button

[Guest]

I hope I can explain this well. What I'm wanting to do is put a button from
the Control Toolbox into multiple fields on a spreadsheet. If I click the
button, then I want it to highlight the four fields next to it. Also, if
possible I would like to have it then copy that selection to a different
worksheet within the workbook. I hope I explained it well enough. If not,
please post a reply and I will try to explain it better. Thanks in advance.

 

[Dave Peterson]

You may be better off using a button from the Forms Toolbar.

That way you can assign the same macro to all those buttons.

This may give you a few ideas to play with:

Option Explicit
Sub testme01()

Dim myBTN As Button
Dim DestCell As Range
Dim RngToCopy As Range

Set myBTN = ActiveSheet.Buttons(Application.Caller)

'what does next to mean?
Set RngToCopy = myBTN.TopLeftCell.Offset(0, 5).Resize(1, 4)

If RngToCopy.Cells.Count = Application.CountA(RngToCopy) Then
With ActiveSheet.Parent.Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp)
End With

RngToCopy.Copy _
Destination:=DestCell

RngToCopy.Select
Else
Beep
MsgBox "Please fill in those 4 cells"
End If

End Sub