How to work with an adjacent cell with an ActiveX button

[xcamarasa]

Hello guys, first time posting here (an first time posting in a forum ever).

I'm desperate.

I am programming an Excel File to act as a game interface (a very simple one, for a gamification project in my company).

The thing is that I want to create an ActiveX button (why an ActiveX and not a Form? Because I can change the background of an ActiveX button and put an image in it) that operates the cell to the left of it, so I can copy the button in different rows and the button won't operate a fixed cell, but the cell next to it. I want to button to substract 1 to the cell on the left.

I've tried to program first a FORM control button (through a macro) with this code, and it works perfect:
Sub Substract()

Dim cellAddr As String
Dim aCol As Long

'~~> Get the address of the cell
cellAddr = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address

ActiveSheet.Range(cellAddr).Offset(, -3).Value = ActiveSheet.Range(cellAddr).Offset(, -3).Value - 1

End Sub

But I don't want a form control button, I want an ActiveX control button. I've tried this (calling the macro), but it does not work:
Private Sub CommandButton1_Click()
Substract

End Sub

When I click the button, this message appears: Run-time error '-2147352571 (80020005)': The item with the specified name wasn't found. And then, when I click on "debug", the VBA window highlights in yellow the following part of my macro:
Sub Substract()


Dim cellAddr As String
Dim aCol As Long


'~~> Get the address of the cell
cellAddr = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address


ActiveSheet.Range(cellAddr).Offset(, -3).Value = ActiveSheet.Range(cellAddr).Offset(, -3).Value - 1

End Sub

I've also tried copying the macro coding directly to the Private Sub of the ActiveX button, but it doesn't work either (same error as mentioned before):
Private Sub CommandButton1_Click()
Dim cellAddr As String
Dim aCol As Long

'~~> Get the address of the cell
cellAddr = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address

ActiveSheet.Range(cellAddr).Offset(, -3).Value = ActiveSheet.Range(cellAddr).Offset(, -3).Value - 1

End Sub




I would really appreciate some help on this because I have to submit this Excel by the end of today and this is the last thing left to do...

left Hope I'll be hearing from you.

Best Regards,
Chuck.

 

[dmt32]

Hi,
welcome to forum

try this update to your code


Place code in your sheets code page:

Private Sub CommandButton1_Click()
    Dim cellAddr As String
    
'~~> Get the address of the cell
    cellAddr = Me.CommandButton1.TopLeftCell.Address
    
     With Me.Range(cellAddr).Offset(, -3)
        .Value = .Value - 1
     End With


End Sub

Dave

 

[xcamarasa]

Hi Dave,

Thanks a lot for your reply!

I've tried your code, and it "works" in the sense of: when I click the button, it substracts 1 unit to the cell 3 positions on its left. But I still cannot copy and paste the button to another row without having to modify and adapt the code for this second button. I want to achieve this because I won't be the one maintaining the Excel and another person with no idea about coding will need to add buttons in an easy way (copy-pasting them).

With form control buttons, since they have a macro associated to them, I can just copy them and paste them in another row and they will do the operation to the cell next to it without having to re-code anything. But with ActiveX command button, if I copy-paste the first button I create and code, another separate "entity" is created in the sheet's code page (CommandButton2).

In other words, if I hadn't understood wrong, the solution you suggest would be nearly the same as coding the button to operate a certain cell. I would still need to recode every time I copy and paste the button (either to select a different concrete cell or to copy-paste your solution coding).

Maybe it is not possible to achieve what I am asking for, I don't know. Maybe you can't copy-paste an ActiveX button that operates an adjacent cell without having to code each new copy.

If you have any news on this, I would really appreciate it.

Thanks a lot for your idea though, I'm very grateful.

Chuck.

 

[MickG]

The file below may do what you want, it is a Class module, the only problem with it is, that it is loaded by activating Sheet1. This is done when you open the workbook, but if you copy and paste one of the other "Commandbuttons" you will need to reselect sheet1 to run the initial code again.
You could add a Forms button to run the code, if that would help !!!!
https://app.box.com/s/c0j7iuy1uc9ljw5rxoqbbcf48tw6s4yb

 

[dmt32]

Hi,
sorry, I did not read all your post just looked at updating the code you posted without taking fully on board what you are trying to do.

MickG post may have the solution you seek.


Dave

 

[dmt32]

Hi,
re-read your post & gave it some further thought - Maybe to be able to do what you want with your original code consider using a rectangle shape - Shapes like a forms button, you can assign a macro BUT they also allow you to add a picture. Shapes have ability to add effects where you can make them look raised like a button.

I created a shape, added a picture & assigned your macro then copied on the sheet it a few times & seems I think, to do what you want.

You will need to experiment but hope suggestion helpful


Dave

 

[Rick Rothstein]

Sub Substract([B][COLOR="#FF0000"]ControlName As String[/COLOR][/B])

  Dim cellAddr As String
  Dim aCol As Long

  '~~> Get the address of the cell
  cellAddr = ActiveSheet.Shapes([B][COLOR="#FF0000"]ControlName[/COLOR][/B]).TopLeftCell.Address

  ActiveSheet.Range(cellAddr).Offset(, -3).Value = ActiveSheet.Range(cellAddr).Offset(, -3).Value - 1

End Sub

Private Sub CommandButton1_Click()

  Substract [B][COLOR="#FF0000"]"CommandButton1"[/COLOR][/B]

End Sub

Why don't you simply pass the name of the control into the Substract subroutine directly and then use it within the macro? This is untested, but I am thinking if you modify your code as I show in red (passing the correct control name from the Click event for each control), I think it should work.

 

[xcamarasa]

Hi guys,

I already submitted the Excel with the suggestion Dave made. I had to deliver it by the end of friday, so I didn't have much time. I just copy pasted Dave's code to every button I created. It's not optimal, but it does what it has to do.

Thank you everybody for your help and contributions. I will try all your suggestions on my own (now it's something personal haha).

Have a good day.

Chuck.