Using Button location as variable?

[Hombre]

Trying to use one macro for multiple Forms Buttons in multiple columns to copy and then past from the same columns in the spreadsheet..

If I cannot do above, then I need separate buttons and separate Macros for each column I am in, even though they copy from the same place but paste to the column the button is in.

I used:
dim btnrng = Range
Set btnrng = ActiveSheet.Buttons(Application.Caller).TopLeftCell

to get the button's cell address, but it returns a buttom area not defined error, Assuming this is solved (it is not), how do I use the address as a variable, perhaps with offsets?

My original code is:
If Range("H24") = "1" Then
Range("AE25:AF81").Select
Selection.Copy
Range btnrng.Address.Select
ActiveSheet.Paste
Range("H23").Select
End If
If Range("H24") = "2" Then
Range("AG25:AH81").Select
Selection.Copy
Range btnrng.Address.Select
ActiveSheet.Paste
Range("H23").Select
End If

etc to 70 results of the button, and then 70 columns to copy from. Then I paste to the same column as the button.

I would like variables to paste so I don't have 10 (eg) buttons times 70 copies for 700 if statements in 10 macros.

Halp!

 

[Akuini]

Trying to use one macro for multiple Forms Buttons

Any particular reason why you're using Form Control instead of ActiveX Control?

to get the button's cell address,

If you use ActiveX Control you can do it like this:

Sub try(obj As Object)
Dim c As Range
With obj.TopLeftCell
    Set c = Cells(.Row, .Column)
End With
MsgBox c.Address
End Sub

Private Sub CommandButton1_Click()
Call try(CommandButton1)
End Sub

Private Sub CommandButton2_Click()
Call try(CommandButton2)
End Sub

If you click CommandButton2:

 

[dmt32]

OK, thanks!
There are other buttons, for hiding/unhiding on the same worksheet, and a couple other copy paste to store the answers as a template.

ok then need to understand are only the buttons this code applies to located on row 24?

Dave

 

[Hombre]

ok then need to understand are only the buttons this code applies to located on row 24?

Dave

Yes, and desired paste row is 25 right below. All buttons Copy range is copied from one of 70 columns in the same sheet. So I hit any of the 10 buttons and they copy from the same range for the same number (1-70), and paste to THEIR column row 25

 

[RoryA]

If you use ActiveX Control you can do it like this

You don't need to use activex for that. In fact using Form controls is simpler as you can assign the same macro to all of them, and they are generally more stable.

 

[Hombre]

Any particular reason why you're using Form Control instead of ActiveX Control?

If you use ActiveX Control you can do it like this:

Sub try(obj As Object)
Dim c As Range
With obj.TopLeftCell
    Set c = Cells(.Row, .Column)
End With
MsgBox c.Address
End Sub

Private Sub CommandButton1_Click()
Call try(CommandButton1)
End Sub

Private Sub CommandButton2_Click()
Call try(CommandButton2)
End Sub

If you click CommandButton2:
View attachment 99631

Yes, this returns the button cell.
Then I need to copy a range in the sheet, the range depending on the button's value (1-70), then paste to the same column offset down one cell. The goal being to have just 1 macro for all 10 buttons (diff columns), not how I do it now with 70 similar if statements for each button because I can't make the macro "generic".

I have always used forms buttons, and I can get the address from a what called cell, but then don't know how to use the address ("$H$24" for example) to paste under it. The value of H24 is, eg., "2" but I want to use the $H$24 offset 1 for the paste address.
I am probably not clear and just about to paste the code itself if that helps. DMT32 may have the answer though, and I can stop bothering you good people!

 

[dmt32]

Yes, and desired paste row is 25 right below. All buttons Copy range is copied from one of 70 columns in the same sheet. So I hit any of the 10 buttons and they copy from the same range for the same number (1-70), and paste to THEIR column row 25

Ok then please follow these instructions carefully

1 - Make a BACKUP of your workbook

2 - DELETE any Button_Click codes you may have for said 10 buttons

3 - Copy both codes below to a STANDARD module

Sub SetButtons()
    Dim xshape      As Shape
    Dim addr        As String
    '---------------------------------------------------------------------------
    '@dmt32 MRExcel - Oct 2023
    '---------------------------------------------------------------------------
    'sets the name of common code to run when a forms button is chosen by user
    'also adds the buttons cell address as an argument
  
    'Note - code should only need to be run when button(s) are added or
    'their address locations are changed
    '---------------------------------------------------------------------------
    For Each xshape In ActiveSheet.Shapes
        With xshape
            addr = .TopLeftCell.Address
            If .Type = msoFormControl Then
             If Range(addr).Row = 24 Then
                If .FormControlType = xlButtonControl Then
                    .OnAction = "'CopyCells """ & addr & """'"
                End If
            End If
            End If
        End With
    Next
  
End Sub

You should only need to run the above code ONCE or anytime you make changes / additions to the buttons in Row 24 of your worksheet

Code above assigns to the Buttons OnAction property the name of the common code & includes the cell address as an argument.

Sub CopyCells(ByVal ButtonAddress As String)
    Dim BtnRng      As Range, CopyRng As Range
    Dim BtnValue    As Long
    '---------------------------------------------------------------------
    '@dmt32 MRExcel - Oct 2023
    '---------------------------------------------------------------------
  
    Set BtnRng = Range(ButtonAddress)
    BtnValue = Val(BtnRng.Value)
    BtnValue = IIf(BtnValue > 0 And BtnValue < 71, (BtnValue - 1) * 2, 0)
    Set CopyRng = Cells(25, 31 + BtnValue).Resize(81, 2)
    CopyRng.Copy BtnRng.Offset(1)
End Sub

I have only lightly tested but hope I have understood your requirement correctly & solution does indeed, do what you want

Dave

 

[Hombre]

Ok then please follow these instructions carefully

1 - Make a BACKUP of your workbook

2 - DELETE any Button_Click codes you may have for said 10 buttons

3 - Copy both codes below to a STANDARD module

Sub SetButtons()
    Dim xshape      As Shape
    Dim addr        As String
    '---------------------------------------------------------------------------
    '@dmt32 MRExcel - Oct 2023
    '---------------------------------------------------------------------------
    'sets the name of common code to run when a forms button is chosen by user
    'also adds the buttons cell address as an argument
 
    'Note - code should only need to be run when button(s) are added or
    'their address locations are changed
    '---------------------------------------------------------------------------
    For Each xshape In ActiveSheet.Shapes
        With xshape
            addr = .TopLeftCell.Address
            If .Type = msoFormControl Then
             If Range(addr).Row = 24 Then
                If .FormControlType = xlButtonControl Then
                    .OnAction = "'CopyCells """ & addr & """'"
                End If
            End If
            End If
        End With
    Next
 
End Sub

You should only need to run the above code ONCE or anytime you make changes / additions to the buttons in Row 24 of your worksheet

Code above assigns to the Buttons OnAction property the name of the common code & includes the cell address as an argument.

Sub CopyCells(ByVal ButtonAddress As String)
    Dim BtnRng      As Range, CopyRng As Range
    Dim BtnValue    As Long
    '---------------------------------------------------------------------
    '@dmt32 MRExcel - Oct 2023
    '---------------------------------------------------------------------
 
    Set BtnRng = Range(ButtonAddress)
    BtnValue = Val(BtnRng.Value)
    BtnValue = IIf(BtnValue > 0 And BtnValue < 71, (BtnValue - 1) * 2, 0)
    Set CopyRng = Cells(25, 31 + BtnValue).Resize(81, 2)
    CopyRng.Copy BtnRng.Offset(1)
End Sub

I have only lightly tested but hope I have understood your requirement correctly & solution does indeed, do what you want

Dave

Wow. I will try this afternoon--in mtg now.
Thank you--this looks brilliant!
H

 

[dmt32]

Wow. I will try this afternoon--in mtg now.
Thank you--this looks brilliant!
H

thank-you for your kind words but as on school run this week, ran out of time to complete narrative

Each button should now call the common code which passes the cell address to it & taking into account the value in the buttons cell, should select the correct range to copy

Dave

 

[Hombre]

Ok then please follow these instructions carefully

1 - Make a BACKUP of your workbook

2 - DELETE any Button_Click codes you may have for said 10 buttons

3 - Copy both codes below to a STANDARD module

Sub SetButtons()
    Dim xshape      As Shape
    Dim addr        As String
    '---------------------------------------------------------------------------
    '@dmt32 MRExcel - Oct 2023
    '---------------------------------------------------------------------------
    'sets the name of common code to run when a forms button is chosen by user
    'also adds the buttons cell address as an argument
 
    'Note - code should only need to be run when button(s) are added or
    'their address locations are changed
    '---------------------------------------------------------------------------
    For Each xshape In ActiveSheet.Shapes
        With xshape
            addr = .TopLeftCell.Address
            If .Type = msoFormControl Then
             If Range(addr).Row = 24 Then
                If .FormControlType = xlButtonControl Then
                    .OnAction = "'CopyCells """ & addr & """'"
                End If
            End If
            End If
        End With
    Next
 
End Sub

You should only need to run the above code ONCE or anytime you make changes / additions to the buttons in Row 24 of your worksheet

Code above assigns to the Buttons OnAction property the name of the common code & includes the cell address as an argument.

Sub CopyCells(ByVal ButtonAddress As String)
    Dim BtnRng      As Range, CopyRng As Range
    Dim BtnValue    As Long
    '---------------------------------------------------------------------
    '@dmt32 MRExcel - Oct 2023
    '---------------------------------------------------------------------
 
    Set BtnRng = Range(ButtonAddress)
    BtnValue = Val(BtnRng.Value)
    BtnValue = IIf(BtnValue > 0 And BtnValue < 71, (BtnValue - 1) * 2, 0)
    Set CopyRng = Cells(25, 31 + BtnValue).Resize(81, 2)
    CopyRng.Copy BtnRng.Offset(1)
End Sub

I have only lightly tested but hope I have understood your requirement correctly & solution does indeed, do what you want

Dave

This newbie needs to clarify:

1 - Make a BACKUP of your workbook OK

2 - DELETE any Button_Click codes you may have for said 10 buttons Not sure of this instruction: Each button looks up from a table/list and assigns the number 1-70 to the relative selection made in the list. Then the assigned macro to that button runs and copies the column for the relative number selected, then I tell it to paste to a specific address, always row 24 and one cell under the button.

Soooo, I need to keep the button assigning the number of he selection and putting that number under the button in it's cell, right? (1-70).

And if I delete the code, it loses the copy the column instructions....

3 - Copy both codes below to a STANDARD module Standard means just a separate module with the 2 macros in it, right? Then I run it once, or have it run every time the workbook opens? Confused.

I fell so stupid but see the gist of this great idea.

 

[dmt32]

Your 10 buttons do not need any addition codes to copy the required range based on the button cell value - The SetButtons code assigns the Common code to the buttons OnAction property and includes the Cell Address to pass as an argument - (which is what you were asking for at outset of this thread.) the common code then performs the copy action.

If the Button_click events are doing more than this then, you will need to share what else the click event code is doing otherwise, no additional code should be needed.