Convert to LOOP 12 little macros

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
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...

1604066930373.png


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

VBA Code:
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 );
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

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

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
@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.

ScreenShot001.png


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:
VBA Code:
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:
VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,970
Office Version
  1. 2016
Platform
  1. Windows
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)...
VBA Code:
Private Sub CommandButton1_Click()
  AffixNumber "1"
End Sub
and this is the subroutine they are all calling...
VBA Code:
Sub AffixNumber(KeyIn As string0)
  ActiveSheet.TextBox1.Value = ActiveSheet.TextBox1.Value & KeyIn
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,012
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top