Convert to LOOP 12 little macros

[Alex Piotto]

Well... hi!
I made a little numberpad to avoid users to enter nothing but numbers in a protected textbox,
but more specifically because i was asked to do an IN and OUT for expenses workbook that does not require the keyboard and must be dumb proof. So...

Please do not laugh at me...
EVERYTHING WORKS ANYWAY...
Now, there is some code behind the numberpad. Here it is:

Private Sub CommandButton1_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY1 As Integer
KEY1 = ActiveSheet.CommandButton1.Caption
ActiveSheet.TextBox1.Value = myVar & KEY1
End Sub

Private Sub CommandButton2_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY2 As Integer
KEY2 = ActiveSheet.CommandButton2.Caption
ActiveSheet.TextBox1.Value = myVar & KEY2
End Sub

Private Sub CommandButton3_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY3 As Integer
KEY3 = ActiveSheet.CommandButton3.Caption
ActiveSheet.TextBox1.Value = myVar & KEY3
End Sub

Private Sub CommandButton4_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY4 As Integer
KEY4 = ActiveSheet.CommandButton4.Caption
ActiveSheet.TextBox1.Value = myVar & KEY4
End Sub

Private Sub CommandButton5_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY5 As Integer
KEY5 = ActiveSheet.CommandButton5.Caption
ActiveSheet.TextBox1.Value = myVar & KEY5
End Sub

Private Sub CommandButton6_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY6 As Integer
KEY6 = ActiveSheet.CommandButton6.Caption
ActiveSheet.TextBox1.Value = myVar & KEY6
End Sub

Private Sub CommandButton7_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY7 As Integer
KEY7 = ActiveSheet.CommandButton7.Caption
ActiveSheet.TextBox1.Value = myVar & KEY7
End Sub

Private Sub CommandButton8_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY8 As Integer
KEY8 = ActiveSheet.CommandButton8.Caption
ActiveSheet.TextBox1.Value = myVar & KEY8
End Sub

Private Sub CommandButton9_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY9 As Integer
KEY9 = ActiveSheet.CommandButton9.Caption
ActiveSheet.TextBox1.Value = myVar & KEY9
End Sub

Private Sub CommandButton0_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEY0 As Integer
KEY0 = ActiveSheet.CommandButton0.Caption
ActiveSheet.TextBox1.Value = myVar & KEY0
End Sub

Private Sub CommandButtonDot_Click()
Dim myVar As String
myVar = ActiveSheet.TextBox1.Value
Dim KEYDOT As String
KEYDOT = ActiveSheet.CommandButtonDot.Caption
ActiveSheet.TextBox1.Value = myVar & KEYDOT
End Sub

Private Sub CommandButtonCancel_Click()
ActiveSheet.TextBox1.Value = ""
End Sub

Every button has its own little code.
The buttons gets whatever is in the textbox and append the new number.

I am sure that it can be done in less of ten lines...

Like with a for each loop perhaps? Still trying to get the logic...

FOR EACH COMMANDBUTTON, NUMBERED FROM 0 TO 9, MORE THE "DOT" AND THE "CANCEL" BUTTON,
GET WHAT IS INTO THE TEXTBOX as a variable,
AND PUT IT BACK FOLLOWED BY THE NEW CLICKED NUMBER.

but no loop code yet to show );

 

[JLGWhiz]

I think you are pretty much stuck with the 12 macros, since you are using 12 command buttons. I though maybe using a single macro to execute the display by calling it from the individual button_click macros might work, but it still takes almost as much code per button and does not really save a whole lot of code or time.

 

[Alex Piotto]

Shame... I was thinking like to make 1 script and call it from each button... with the name and the value of the button as variable and the text box1 value also avriable...

 

[JLGWhiz]

I think that is the same thing I was describing, but like I said, by the time you put in code to pass the key caption values to the single called macro, you have about the same amount of code as the current macros. And that value has to be passed for the called macro to know which button was clicked and what value to display.. You can't get away from the Button_Click procedures because of the way you have constructed the calculator. I don't visualize a loop of any kind working to reduce the code any. But once the values are entered into the text box, you definitely want to call a mutual procedure to work the data. You might also think about using an equal (=) key which would have code to transfer the accumulated characters to a storage cell for use in calculations, so those characters can be cleared and new characters then put into the text box. I think it might be a good idea to take pencil and paper and draw a logic diagram (flow chart) of just how the device needs to work. That makes it a lot easier to write the code for it because you don't have to hold all the variables in your head while you are creating the procedures.

 

[cooper645]

Although I haven't tested it, it is shorter to write it like so:

Due to being on the Mac I don't have active X command buttons, but did use shapes and cells to test this

Private Sub CommandButton1_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "1"
End Sub
Private Sub CommandButton2_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "2"
End Sub
Private Sub CommandButton3_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "3"
End Sub
Private Sub CommandButton4_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "4"
End Sub
Private Sub CommandButton5_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "5"
End Sub
Private Sub CommandButton6_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "6"
End Sub
Private Sub CommandButton7_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "7"
End Sub
Private Sub CommandButton8_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "8"
End Sub
Private Sub CommandButton9_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "9"
End Sub
Private Sub CommandButton0_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "0"
End Sub
Private Sub CommandButtonDot_Click()
ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & "."
End Sub
Private Sub CommandButtonCancel_Click()
ActiveSheet.TextBox1.Value = ""
End Sub

 

[GWteB]

@Alex Piotto,
Failed to convert your 12 macros to a loop. In contrast, I may have an acceptable alternative.
The 12 macros in the worksheet module can make room for just one macro in an event class module. For each individual command button an instance of that class is being established. In fact, only one event procedure is used for no matter how many command buttons. If desired, other events of the command buttons can also be captured within this class.

My code assumes that the textbox present on your worksheet, of which its content is changed using your custom on screen number pad, is also an Active-X control.
I would also like to point out that an instance of the event class is also stored for any other command buttons on other worksheets in the same workbook. This has no further effect on pre-existing event procedures related to command buttons on any worksheet (unless other worksheets also contain an Active-X textbox with the name TextBox1; if so that textbox should be renamed).

This goes in the ThisWorkbook module:

Option Explicit

Private coll As Collection

Private Sub Workbook_Open()
    Call ExposeGroupedCbtnEvents
End Sub

Private Sub ExposeGroupedCbtnEvents()

    Dim clsBtn  As Class_CbtnGroup
    Dim oWs     As Worksheet
    Dim oCtl    As Object
    
    Set coll = New Collection
    For Each oWs In ThisWorkbook.Sheets
        For Each oCtl In oWs.OLEObjects
            If oCtl.progID = "Forms.CommandButton.1" Then
                Set clsBtn = New Class_CbtnGroup
                Call clsBtn.Init(argCbtn:=oCtl.Object, _
                                 argSheet:=oWs, _
                                 argTbxName:="TextBox1")
                coll.Add clsBtn
            End If
        Next oCtl
    Next oWs
End Sub

This goes in a class module, to be renamed as Class_CbtnGroup:

Option Explicit

Private WithEvents CbtnGroup  As CommandButton

Private oWsHost As Worksheet
Private sTbxName  As String

Friend Sub Init(ByVal argCbtn As CommandButton, ByVal argSheet As Worksheet, ByVal argTbxName As String)
    Set CbtnGroup = argCbtn
    Set oWsHost = argSheet
    sTbxName = argTbxName
End Sub

Private Sub CbtnGroup_Click()
    On Error Resume Next
    With oWsHost.OLEObjects(sTbxName).Object
        If StrComp(Right(CbtnGroup.Name, 6), "CANCEL", vbTextCompare) = 0 Then
            .Value = ""
        Else
            .Value = .Value & CbtnGroup.Caption
        End If
    End With
End Sub

 

[Rick Rothstein]

Another way...

Do this for each one of your keys (change the text being passed to match the key's value) except the "clear all" key (leave that separate the way you have it now)...

Private Sub CommandButton1_Click()
  AffixNumber "1"
End Sub

and this is the subroutine they are all calling...

Sub AffixNumber(KeyIn As string0)
  ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & KeyIn
End Sub