Get a textbox to behave like an excel cell.

[Edgar_]

I gave it another shot. The reason it writes the expression in the new record seems to be because it's an unbound control, or so I believe. For now, I switched to use the keyup and keydown events to handle this. One version looks like this and it does not write the expression in a new record, if that was a problem for someone:

Spoiler: Code for textbox calculations triggered by the equals sign

Option Explicit

Private Sub CalculationTextbox_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then
        Dim calc As Double
        If Len(Nz(Me.CalculationTextbox.Text, "")) <> 0 Then
            calc = Eval(Me.CalculationTextbox.Text)
        End If
        Me.CalculationTextbox.ControlSource = "Calculation"
        Me.CalculationTextbox.BorderStyle = 1
        Me.CalculationTextbox = calc
        Me.lblExcelMode.Visible = False
    End If
End Sub

Private Sub CalculationTextbox_KeyUp(KeyCode As Integer, Shift As Integer)
    If Me.CalculationTextbox.Text = "=" Then
        Me.CalculationTextbox.ControlSource = ""
        Me.CalculationTextbox.BorderStyle = 2
        Me.lblExcelMode.Visible = True
    End If
End Sub

And I added another version of the form that takes care of the expression in a new record, if that is a problem. This version is reactive and it detects common operators in the expression being written. That means you don't need to type the equals sign, it will simply check if there is an operator and it will try to calculate when Enter is pressed.

Spoiler: Code for textbox calculations triggered by operators like plus, minus, etc., detected

Option Explicit

Private Sub CalculationTextbox_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then
        Dim calc As Double
        If Len(Nz(Me.CalculationTextbox.Text, "")) <> 0 Then
            calc = Eval(Me.CalculationTextbox.Text)
        End If
        Me.CalculationTextbox.ControlSource = "Calculation"
        Me.CalculationTextbox.BorderStyle = 1
        Me.CalculationTextbox = calc
        Me.lblExcelMode.Visible = False
    End If
End Sub

Private Sub CalculationTextbox_KeyUp(KeyCode As Integer, Shift As Integer)
    Dim inputText As String
    inputText = Me.CalculationTextbox.Text
    If InStr(inputText, "/") > 0 Or _
        InStr(inputText, "*") > 0 Or _
        InStr(inputText, "+") > 0 Or _
        InStr(inputText, "-") > 0 Or _
        InStr(inputText, "(") > 0 Or _
        InStr(inputText, ")") > 0 Then
        Me.CalculationTextbox.ControlSource = ""
        Me.CalculationTextbox.BorderStyle = 2
        Me.CalculationTextbox = inputText
        Me.CalculationTextbox.SelStart = Len(inputText)
        Me.lblExcelMode.Visible = True
    End If
End Sub

Private Sub Detail_Paint()
    If IsNull(Me.SomethingID) Then
        Me.CalculationTextbox.ControlSource = ""
        Me.CalculationTextbox = 0
        Me.CalculationTextbox.ControlSource = "Calculation"
    End If
End Sub

'Private Sub Form_Current()
'    If IsNull(Me.SomethingID) Then
'        Me.CalculationTextbox.ControlSource = ""
'        Me.CalculationTextbox = 0
'        Me.CalculationTextbox.ControlSource = "Calculation"
'    End If
'End Sub

Both the detail paint and form current event can take care of the expression in the new record, if that is a problem. I hope it helps someone.

Just to help others see some potential, I wrapped the first one in a class to adopt it very easily. Again, this is just a prototype, add whatever else you want. Here's the little class:

Spoiler: The code of the class

Option Explicit

Private WithEvents m_txtbox As TextBox

Public Sub Init(txtbox As TextBox)
    Set m_txtbox = txtbox
    m_txtbox.OnKeyDown = "[Event Procedure]"
    m_txtbox.OnKeyUp = "[Event Procedure]"
End Sub

Private Sub m_txtbox_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then
        Dim calc As Double
        If Len(Nz(m_txtbox.Text, "")) <> 0 Then
            calc = Eval(m_txtbox.Text)
        End If
        m_txtbox.ControlSource = "Calculation"
        m_txtbox.BorderStyle = 1
        m_txtbox.Value = calc
    End If
End Sub

Private Sub m_txtbox_KeyUp(KeyCode As Integer, Shift As Integer)
    If m_txtbox.Text = "=" Then
        m_txtbox.ControlSource = ""
        m_txtbox.BorderStyle = 2
        m_txtbox.Visible = True
    End If
End Sub

With the class, using it is as simple or complicated as this:

Private xlTextbox As ExcelLikeTextbox

Private Sub Form_Load()
    Set xlTextbox = New ExcelLikeTextbox
    xlTextbox.Init Me.CalculationTextbox
End Sub

 

[arnelgp]

you can use any expression on textbox1 (or textbox2), like 1+2, or 3-2, and when you press the Equal sign (=) at the end
of the expression, the result will be shown.

and the cursor stays on that textbox without any alternation of any Option.

reference: https://www.access-programmers.co.uk/forums/threads/textbox-calculator.299922/

 

[DickyP]

Would a simple pop up calculator work? I have one you can click in a field and do the calculations, returning the value back to the control.

Whilst not convinced of the usefulness of the threads desire to treat Access like Excel, thanks for this popup - I have incorporated it into my standard library database. And I'm happily fiddling with it to see where I can get to!

First one is to define a sub-macro ( I've assigned to ^+K) so I don't have to assign any code to particular fields.

Next step is to make sure it only works on numeric text fields by:

replace
    
    If Not BoundControl Is Nothing Then
            If IsNumeric(BoundControl) Then strArgs = BoundControl.Value
    End If

with

    If Not BoundControl Is Nothing Then
      If BoundControl.ControlType <> acTextBox Then Exit Function
      If IsNumeric(BoundControl) Then
        strArgs = BoundControl.Value
      Else
        Exit Function
      End If
    End If

 

[Mike Krailo]

Lot's of creativity coming out of this idea. Way to go guys.

 

[MajP]

So when I'm entering the total say $525.00 for 20 units from a vat inclusive bill I was planning just to input "=525/1.125" in the Cost Field.

This is an interesting exercise and the proposed solutions are more flexible, but if it is simply what you say and it is in single form view why not simply have the extra controls to do the calculations. An unboudn bound quantity control and Vat control then the form has a built in calculator. I would think the Vat control could even be a combo so you can pick a common value and not have to type 1.125 each time.
Even if this is in continuous view you could make the controls only "visible" in the active record.

 

[raziel3]

I would think the Vat control could even be a combo so you can pick a common value and not have to type 1.125 each time.

My data entry form is set up to enter
PurDate, Item, Quantity, Cost (which is the Vat Exclusive Cost) and a Check box to select if the Item is Vatable or not (all fields are bounded)
Once I have that data everything else (Cost/Unit, Vat Amount etc) can be calculated with queries.

The problem arises when a Vat Inclusive Invoice comes in. In the [Cost] control I would be using the code.

See the attachment. All controls after ZR are unbounded and just for information.