Selecting cell below command button

[dshadel]

Hi, I am working with a sheet where I will have multiple command buttons that will run the same macro in multiple areas (different rows and columns) of the same sheet. I would like to only have one macro - but I am at a loss as to how to make sure the proper cell is active before the rest of the macro runs.

Using VB code, how can I select the cell directly below - relative to the cell that the command button is located in?

 

[Andrew Poulsom]

If your CommandButton is from the Forms ToolBar, you can get the cell below it like this:

Sub Test()
    MsgBox ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(1, 0).Address
End Sub

 

[dshadel]

The command buttons will be objects in the worksheet, for instance in cell B2 will be a button that will run a macro to input data in cell B3. Another button might be in cell d3 to input data in cell d4. The problem I encounter is that if cell a1 is selected and I push the button in cell b2, the data is entered in cell a1 instead of b3. I can not figure out how to set the active cell relative to the cell the control is in.

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(1, 0).Value = "Whatever"
End Sub

 

[dshadel]

That works. Thanks!

 

[Future_MVP]

Hello,

What if your button is a command button and not from the forms menu. Is there code to perform this same function? Thanks,

If your CommandButton is from the Forms ToolBar, you can get the cell below it like this:

Sub Test()
    MsgBox ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(1, 0).Address
End Sub

 

[Smitty]

Hello,

What if your button is a command button and not from the forms menu. Is there code to perform this same function? Thanks,

It works the same way:

MsgBox CommandButton1.TopLeftCell.Address

HTH,