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.

 

[My Aswer Is This]

If your happy with your buttons that's great.
I do not understand this:

It is cumbersome to have to click each cell and pull up virtual keyboard every time​

My script only requires double clicking with the mouse on a cell or right clicking on a cell.

 

[pingme89]

I meant without the buttons, I would have had to pull up a virtual keyboard. That is the reason I wanted to add buttons. So your solution is best for my use with a tablet.

 

[My Aswer Is This]

That's interesting I have never used a Tablet and do not know what a

virtual keyboard is.
So you can use Excel without using a keyboard?
And using my script you do not use the keyboard you just use the mouse.
But maybe when using a tablet you have no keyboard.

I guess I should learn more about Tablets.

How do you enter the value "Bill" into cell B1 without a keyboard?


​

 

[pingme89]

If you wanted to write text, then you need to pull up the virtual keyboard or use a blutooth keyboard. Actually, Android or IoS tablets will not run Macros or VBA code. A Microsoft Surface can because it has Windows 10 full version installed. If you want VBA usage, avoid the Microsoft RT. That can only install Apps and not full blown windows software. I am using a cheap unit made in China by a company called Chuwi. It can dual boot Windows 10 and Android OS. It is not the fastest machine but it is rare to find a full version of Windows 10 on a tablet other than a Microsoft Surface which is cost prohibitive.

 

[My Aswer Is This]

Well thanks for that bit of knowledge. I'm old school and still use PC laptops.