Add buttons with On Click code not Macros

[pingme89]

I have the following code to make buttons on a worksheet.

For example Range("B9") would have a have a button named "B9down" and another button named "B9up"
Button "B9down" would be on the left side of column "B". Button "B9up" would be on the right side of Range("B9).
The Column "B" would be spaced wide enough so that the centered text would show at least 2 digits.

I want to add the code to that button with on action click. I don't want it to call a macro.

The code should be as follows for each button:

Private Sub B9down_Click()
Dim wbname as string

wbname = thisworkbook.name
CellValue = Workbooks(wbname).Worksheets("Walkthrough").Range("B9").value
CellValue = CellValue - 1
Workbooks(wbname).Worksheets("Walkthrough").Range("B9").value = CellValue
End Sub


I will literally have hundreds of buttons and I really don't want to go through each one adding the code manually. The code I have to add the buttons is as follows:

Dim wbname As String
Dim button As OLEObject
Dim t As Range


wbname = ThisWorkbook.Name
Workbooks(wbname).Worksheets("Walkthrough").Activate


For i = 9 To 41
For j = 2 To 23
ColumnLetter = Split(Cells(1, j).Address, "$")(1)
Set t = ActiveSheet.Range(ColumnLetter & i)
ActiveSheet.Cells(i, j).Activate
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.Caption = "q"
.Name = ColumnLetter & i & "down"
.Height = 15
.Width = 15
.Font.Name = "Wingdings 3"
.Font.Size = 8
End With
With btn
.Caption = "p"
.Name = ColumnLetter & i & "up"
.Height = 15
.Width = 15
.Font.Name = "Wingdings 3"
.Font.Size = 8
End With
Next j
Next i

The only problem is I can't edit the code after creating the button. In Design mode, normally I could double click on the button to access the code. But after these buttons are created, nothing happens and I can't add code. Any ideas on how to accomplish what I want to do? Thanks in advance.

 

[Fluff]

Firstly your code is only adding the "Up" buttons, not the "down" buttons.
Secondly you can assign a macro to all the buttons like

Sub SetBox()
    Dim Btn As button
    For Each Btn In ThisWorkbook.Sheets("Walkthrough").Buttons
        Btn.OnAction = "[COLOR=#ff0000]Fluff[/COLOR]"
    Next
End Sub

Where the value in red is the name of the macro

 

[pingme89]

I literally have hundreds of buttons. I want to avoid macros as would have to have hundreds of macros. I want to run B9down_Click(). Any way to do that?

 

[Fluff]

I want to avoid macros

B9down_Click() is a macro.

Do you mean each & every single button will change the value of cell B9 by 1 ?

 

[pingme89]

No. There are buttons that I will create Named B9down, B10down, B11down... etc to B41down. B9down will increment Cell B9 value by -1. B10down button will increment Cell B10 value by -1 etc. So each button will increment the value of the cell name it has down by 1. Also I will have buttons named B9up, B10up, B11up... B41up accordingly and those buttons will increment the respective cells up by 1.

 

[Fluff]

As mentioned in post#2, your code is only creating the down buttons.
Where do you want the UP buttons located?

 

[Fluff]

Use this to add your buttons

Sub chk()
Dim Btn As button
Dim t As Range
Dim i As Long, j As Long
Dim ColumnLetter As String

ThisWorkbook.Worksheets("Walkthrough").Activate

For i = 9 To 41
   For j = 2 To 23
      ColumnLetter = Split(Cells(1, j).Address, "$")(1)
      Set t = ActiveSheet.Range(ColumnLetter & i)
      ActiveSheet.Cells(i, j).Activate
      With ActiveSheet.Buttons.Add(t.Left + 1, t.Top + (t.Height / 2), 15, (t.Height - 1) / 2)
      .Caption = "q"
      .Name = ColumnLetter & i & "down"
      .Font.Name = "Wingdings 3"
      .Font.size = 8
      End With
      With ActiveSheet.Buttons.Add(t.Left + 1, t.Top + 1, 15, (t.Height - 1) / 2)
      .Caption = "p"
      .Name = ColumnLetter & i & "up"
      .Font.Name = "Wingdings 3"
      .Font.size = 8
      End With
   Next j
Next i
End Sub

Then run this

Sub SetBox()
    Dim Btn As button
    For Each Btn In ThisWorkbook.Sheets("new").Buttons
        Btn.OnAction = "BtnClick"
    Next
End Sub

And this will work when you click the buttons

Sub BtnClick()

   Dim Btn As button
   
   Set Btn = ActiveSheet.Buttons(Application.Caller)
   If Right(Btn.Name, 2) = "up" Then
      Range(Btn.TopLeftCell.Address).Value = Range(Btn.TopLeftCell.Address).Value + 1
   Else
      Range(Btn.TopLeftCell.Address).Value = Range(Btn.TopLeftCell.Address).Value - 1
   End If
End Sub

 

[My Aswer Is This]

You said:

I literally have hundreds of buttons. ​

I want to avoid macros​

as would have to have hundreds of macros. I want to run B9down_Click(). Any way to do that?

If your button will not run a Macro what will it be doing?
​

I never believe any one should need hundreds of buttons on a worksheet.

This script will do exactly what you want with no need for any buttons.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you double click on any cell in Column B the value in column B will increase by 1

When you right click on any cell in Column B the value in column B will decrease by 1

​

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Cancel = True
Target.Value = Target.Value + 1
End If
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Cancel = True
Target.Value = Target.Value - 1
End If
End Sub

 

[pingme89]

Fluff! That worked like a charm! Originally, when you make and ActiveX command button, and in Design Mode and double click the button, you go into the Worksheet Module.

Private Sub Buttonname_Click()
code....
end sub

I was thinking making that code for each button. But I think your solution is much more elegant. Your solution would provide only Macro and not tons of Module button code.

Thank you very much for the help and insight. It is much appreciated.

 

[pingme89]

Actually the reason for all the buttons is I want to use this Workbook on a tablet. It is cumbersome to have to click each cell and pull up virtual keyboard every time. It would be easier to use if I had buttons I could Touch (click on) to change values on each cell. Hence the reason for the hundreds of buttons.

You said:

I literally have hundreds of buttons. ​

I want to avoid macros​

as would have to have hundreds of macros. I want to run B9down_Click(). Any way to do that?

If your button will not run a Macro what will it be doing?
​

I never believe any one should need hundreds of buttons on a worksheet.

This script will do exactly what you want with no need for any buttons.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you double click on any cell in Column B the value in column B will increase by 1

When you right click on any cell in Column B the value in column B will decrease by 1

​

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Cancel = True
Target.Value = Target.Value + 1
End If
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Cancel = True
Target.Value = Target.Value - 1
End If
End Sub