VBA macro-move to cell underneath the button [Solved/Closed]

Report
-
 Liam -
Hello,
I want my VBA macro to move to the cell 'underneath' the particular button that is being pressed. (I have a series of buttons down the page, each will run the same macro / do the same thing, but starting from a different location each time - i.e. starting from where the button is). Any ideas?
Alison

2 replies

put this code in a macro called by the button

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select
14
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2905 users have said thank you to us this month

What I need to do is increment the cell directly to the left of the cell where the button is located by 1. So I have a button in say cell H5, and I need the macro to increment G5 by 1 when the button is pressed. There are several buttons down the page in the H column. Each button calls the exact same Macro but has to increment the previous column by one depending on which row I click the button.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Liam, then just decrease the colum by one (offset(

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(0, -1).Select
Hi there

The same run-time error occurs
"Unable to get the Buttons property of the Worksheet class"

This is my Macro:


Sub Knockout()
'
' Knockout Macro
' Macro recorded 02/08/2010 by Admin
'
Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(0, -1).Select

ActiveCell.Value = ActiveCell.Value + 1
'
End Sub

Is this wrong?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Liam : Application.Caller is used to get information on how the code was called. So could it be that you are calling it incorrectly ?

Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
The problem is now sorted... I was given a solution that works. I had to change all my buttons to buttons made from the Forms Toolbar.

THanks thought!
Don't know if this answers your question.
If your button occupies the cells F4:G5, then code your macro to select the cell underneath.

Range("F6").select