CODEWITHSUNDEEP
excelvba
Grandog
Grandog

Reputation: 3

Excel VBA change semicolon to colon automatically

I've made an Excel workbook, where people can type their work times. Is it possible to map the semicolon to colon? It should put a colon if the user tries to input a semicolon.

I'm alredy using Application.OnKey "^x", "" to disable the cutting function, when the workbook is active. I tried something like Application.OnKey ";", ":" but it isn't working.

Upvotes: 0

Views: 367

Answers (2)

DisplayName
DisplayName

Reputation: 13386

use Replace() method of Range object with xlPart value for its lookAt parameter:

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Replace what:=";", replacement:=":", lookat:=xlPart
End Sub

Upvotes: 1

ArcherBird
ArcherBird

Reputation: 2134

You can use the Worksheet Change event to capture user input and replace the semi-colons with colons, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range

    For Each cell In Target
        Target.Value = Replace(Target.Value, ";", ":")
    Next cell

End Sub

This will only work if you are listening for user input in a single sheet. If you want to listen to all sheets, you would just do something similar in the Workbook change event.

Upvotes: 1

Related Questions