Adding comma when typing!

[rahi_2014]

hi Is there any way when i'm typing a number in a cell (the cell is active /Cursor is blinking) after every 3 digits (1000 separator) excel add a comma ? (NOT AFTER PRESSING ENTER!) thanks

 

[Kenneth Hobson]

Welcome to the forum!

I don't know of an easy and efficient way to do that. That would not be a good thing anyway. It is better to set a number format. The commas will show after cell is updated. Press Ctrl+1 to add the thousands format.

 

[rahi_2014]

thank you...
I know this way but it doesn't solve my problem!
i've seen in some other programming languages that it is possible then it can be done by VBA but it doesn't seem a simple one at least for me !
why do you think this wouldn't a good thing?

 

[RickXL]

Hi and welcome from me as well!

I can offer this as a starting point. You might need to experiment to get it as you want it.

The font always overfills the TextBox so you will probably need to change the row spacing (or make the TextBox font smaller).

Add an ActiveX TextBox named TextBox1 to the Worksheet then paste this code into the code module for that Sheet.

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    With TextBox1
        If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0")
        ActiveCell = .Value
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge <> 1 Then Exit Sub
    With Me.TextBox1
        .Value = Target
        If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0")
        .Top = ActiveCell.Top
        .Left = ActiveCell.Left
        .Height = ActiveCell.Height
        .Width = ActiveCell.Width
        .Activate
    End With
End Sub

Every time the Selection changes, the TextBox moves into that Cell.
When you enter a number it formats it and displays it as you go along.


Regards,

 

[Kenneth Hobson]

Most want numbers so that 1,000+1=1,001. If it was an actual comma, you would get concatenation, 1,0001 or maybe an error.

If you found code to do it, post the link or the code and we will show you how to implement if possible.

 

[rahi_2014]

Thank you, RickXL for your answer you learned me some new things in VBA.
And thank you, Mr.Hobson. If i found, i would post!

 

[rahi_2014]

Hi and welcome from me as well!

I can offer this as a starting point. You might need to experiment to get it as you want it.

The font always overfills the TextBox so you will probably need to change the row spacing (or make the TextBox font smaller).

Add an ActiveX TextBox named TextBox1 to the Worksheet then paste this code into the code module for that Sheet.

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    With TextBox1
        If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0")
        ActiveCell = .Value
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge <> 1 Then Exit Sub
    With Me.TextBox1
        .Value = Target
        If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0")
        .Top = ActiveCell.Top
        .Left = ActiveCell.Left
        .Height = ActiveCell.Height
        .Width = ActiveCell.Width
        .Activate
    End With
End Sub

Every time the Selection changes, the TextBox moves into that Cell.
When you enter a number it formats it and displays it as you go along.


Regards,

A question :
Is it possible when i press enter the textbox enables ? i mean in this code we have to click on the cell to write in it. can we move and active textbox in cells with hitting enter like excel!?

 

[Kenneth Hobson]

I would recommend limiting the selections to a few cells, rows, or columns by using Intersect(). Pressing the escape key will exit the control. You can then click in the Formula box to add a formula if needed. I would recommend intersect though so you can add the formulas more easily.

 

[RickXL]

This is a step up from the previous code:

' List of KeyCodes - https://msdn.microsoft.com/en-us/library/aa243025(v=vs.60).aspx

Private Sub TextBox1_Keydown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    On Error GoTo err   ' Traps moves to illegal cells e.g. on Row/Column = -1.
    With ActiveCell
        Select Case KeyCode
            Case vbKeyTab
                If Shift Then .Offset(0, -1).Activate Else .Offset(0, 1).Activate
            Case vbKeyLeft:     .Offset(0, -1).Activate
            Case vbKeyRight:    .Offset(0, 1).Activate
            Case vbKeyUp:       .Offset(-1, 0).Activate
            Case vbKeyDown:     .Offset(1, 0).Activate
            Case vbKeyReturn
                Select Case Application.MoveAfterReturnDirection
                    Case xlToLeft:  .Offset(0, -1).Activate
                    Case xlToRight: .Offset(0, 1).Activate
                    Case xlUp:      .Offset(-1, 0).Activate
                    Case xlDown:    .Offset(1, 0).Activate
                End Select
        End Select
    End With
err:
End Sub

Private Sub TextBox1_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    With ActiveCell
        Select Case KeyCode
            Case vbKeyTab, vbKeyLeft, vbKeyRight, vbKeyUp, vbKeyDown, vbKeyReturn
            Case Else
                With TextBox1
                    If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0")
                    ActiveCell = .Value
                End With
        End Select
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge <> 1 Then Exit Sub
    With Me.TextBox1
        If Not Intersect(Target, Range("A4:F17")) Is Nothing Then
            .Value = Target
            If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0")
            .Top = ActiveCell.Top
            .Left = ActiveCell.Left
            .Height = ActiveCell.Height
            .Width = ActiveCell.Width
            .Activate
        Else
            .Visible = False
        End If
    End With
End Sub

As Kenneth suggested, it is usually better to restrict this type of operation to a defined Range of Cells. So I have added the Intersect option. The Range considered is A4:F17 as shown in red above. Change that to whatever works for you.

If you need to process other Key Codes then you can end up re-writing quite a lot of existing functionality. The problem is knowing when to stop!

I have picked up the Excel Option that specifies the action when Enter is pressed and used that to move the TextBox. I have also detected the Tab and Arrow keys.

The TextBox becomes invisible when using a Cell outside the defined Range.

Note: I added an On Error step to ignore moves to Row -1 etc. That could be made smarter.


Regards,

 

[rahi_2014]

This is a step up from the previous code:

' List of KeyCodes - https://msdn.microsoft.com/en-us/library/aa243025(v=vs.60).aspx

Private Sub TextBox1_Keydown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    On Error GoTo err   ' Traps moves to illegal cells e.g. on Row/Column = -1.
    With ActiveCell
        Select Case KeyCode
            Case vbKeyTab
                If Shift Then .Offset(0, -1).Activate Else .Offset(0, 1).Activate
            Case vbKeyLeft:     .Offset(0, -1).Activate
            Case vbKeyRight:    .Offset(0, 1).Activate
            Case vbKeyUp:       .Offset(-1, 0).Activate
            Case vbKeyDown:     .Offset(1, 0).Activate
            Case vbKeyReturn
                Select Case Application.MoveAfterReturnDirection
                    Case xlToLeft:  .Offset(0, -1).Activate
                    Case xlToRight: .Offset(0, 1).Activate
                    Case xlUp:      .Offset(-1, 0).Activate
                    Case xlDown:    .Offset(1, 0).Activate
                End Select
        End Select
    End With
err:
End Sub

Private Sub TextBox1_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    With ActiveCell
        Select Case KeyCode
            Case vbKeyTab, vbKeyLeft, vbKeyRight, vbKeyUp, vbKeyDown, vbKeyReturn
            Case Else
                With TextBox1
                    If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0")
                    ActiveCell = .Value
                End With
        End Select
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge <> 1 Then Exit Sub
    With Me.TextBox1
        If Not Intersect(Target, Range("A4:F17")) Is Nothing Then
            .Value = Target
            If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0")
            .Top = ActiveCell.Top
            .Left = ActiveCell.Left
            .Height = ActiveCell.Height
            .Width = ActiveCell.Width
            .Activate
        Else
            .Visible = False
        End If
    End With
End Sub

As Kenneth suggested, it is usually better to restrict this type of operation to a defined Range of Cells. So I have added the Intersect option. The Range considered is A4:F17 as shown in red above. Change that to whatever works for you.

If you need to process other Key Codes then you can end up re-writing quite a lot of existing functionality. The problem is knowing when to stop!

I have picked up the Excel Option that specifies the action when Enter is pressed and used that to move the TextBox. I have also detected the Tab and Arrow keys.

The TextBox becomes invisible when using a Cell outside the defined Range.

Note: I added an On Error step to ignore moves to Row -1 etc. That could be made smarter.


Regards,

sorry i'm answering late

i receive an error in this line :
Private Sub TextBox1_Keydown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
which says: user-defined type not defined