1

I have seen a lot of information on getting several command buttons to do similar things, but everything I've seen is about user forms.

I want to have a group of command buttons move data from a cell to another cell. I just don't want to copy and paste the same code into each command button.

Here is part of what I want to do for each command button:

Range(CommandButton1.TopLeftCell.Address).Select
Selection.Copy
If Application.WorksheetFunction.IsText(Range("R5")) And Range(CommandButton1.TopLeftCell.Address).Value <> Range("R5") Then
    Range("R6").Select
    ActiveSheet.Paste
    Range(CommandButton1.TopLeftCell.Address).Select
Else
    Range("R5").Select
    ActiveSheet.Paste
    Range(CommandButton1.TopLeftCell.Address).Select
End If
Application.CutCopyMode = False

I've tried to use class modules but can't seem to get it to function at all. I want to be able to throw that code into a class or something to run on all command buttons in a certain group. I would then want a different group of command buttons on the same sheet to execute different code.

What route should I take?

ekad
  • 14,436
  • 26
  • 44
  • 46
Link
  • 396
  • 1
  • 4
  • 15
  • check the application.caller option, that way, you can find out which button called the macro – nutsch Aug 15 '13 at 16:30

1 Answers1

1

When you create your first button and place it into an excel sheet you are presented with the macro screen and when clicking new it will open up the macro screen and create the Sub Button1_Click() event or whatever you named your macro. All other buttons created will also have their events created in the module.

In this module you can create a new sub that contains your shared code:

Sub SharedCode()
End Sub

You can then call your shared code in each of the events created for your buttons with:

Sub Button1_Click()
    SharedCode
End Sub
Zaider
  • 1,950
  • 1
  • 21
  • 31
  • Thanks @Zaider for the information. That works pretty well for what I want to do, but I was hoping to not have to put any code into the ButtonX_Click() subs. It's just as easy to paste "SharedCode" as it is to paste the actual shared code. So far I have it working by passing the command button from each ButtonXClick() sub to the SharedCode sub like this: Call SharedCode(Me.CommandButton1) to the sub that contains the shared code: Sub SharedCode(target as CommandButton) – Link Aug 19 '13 at 13:19
  • @Link Well you could also assign all of your buttons to the same sub, it won't show up in the list of macros until you have typed it in once, which may require it to have more checks for which button you are clicking. – Zaider Aug 19 '13 at 14:40