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

Alli - Feb 13, 2009 at 07:31 AM - Latest reply:  Liam
- Aug 4, 2010 at 12:40 AM
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
See more 

9 replies

Best answer
14
Thank you
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

Thank you, Ian 14

Something to say? Add comment

CCM has helped 1871 users 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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 2, 2010 at 08:54 AM
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?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 3, 2010 at 07:34 AM
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 http://www.speedyshare.com/ , 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!
0
Thank you
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