VBA macro-move to cell underneath the button

Solved/Closed
Alli - Feb 13, 2009 at 07:31 AM
 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

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
Wow, the second solution works.Superb.Thanks a ton.
0
This didnt work for me....
"Unable to get the Buttons property of the Worksheet class"
0
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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
0
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?
0
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
0