On Keyboard Event

[Akshayhk]

Hello all,

I am trying to write a macro where-in I want to assign a number to a particular letter and whenever I type that letter, it should be replaced by the number anywhere in the workbook. For example, I want to assign b=1 and s=2. Whenever I type b in any cell it should be replaced 1 and similarly with s should be replaced by 2. How can I do it?

Thanks in advance.

Cheers. :-)

[Kamboj]

This code help you
double click on sheet1 under code editor(Alt+F11)

Please Login or Register  to view this content.

[Akshayhk]

Thanks a lot buddy!! It worked like a charm. So, if I want to apply this to the entire workbook how do I do it? I tried to save this code under "workbook" but it doesn't work.

[Kamboj]

paste this on every sheet editor

[Akshayhk]

Yeah, thought as much. Thanks anyway. Is it possible to have this enabled for a particular row or a range of cells in a worksheet?

[Kamboj]

define that range in target
suppose you want it to row no.2
then

Please Login or Register  to view this content.

[Kamboj]

AND FOR A RANGE HERE B1:B5

Please Login or Register  to view this content.

[Akshayhk]

Thanks. I improvised on your code as below (this is what I want to achieve). It ran well the first time and started acting up when I tried it subsequent times. Same with your original code as well for the target range.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address >= "$F$2" And Target.Address <= "$F$100" Then
Target.Select
Selection.Replace What:="s", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
If Target.Address >= "$F$2" And Target.Address <= "$F$100" Then
Target.Select
Selection.Replace What:="b", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
End Sub