vba - replace # key with bullet point

[RobbieC]

Hi there, I have a spreadsheet which I need to replace a users keystroke with a bullet point when they type.

I thought that the # key would be suitable as it doesn't have much use in this document...

I was hoping to use some code such as:

Application.OnKey "#", "addBulletPoint"

so that when the # key is pressed, it would run the sub 'addBulletPoint' which would add the bullet...

Can anyone point me in the right direction as to do this?

Thanks

 

[Scott Huish]

How about using the Change event to change the character after they've entered the value in the cell?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
    c = Replace(c, "#", Chr(149))
Next
Application.EnableEvents = True
End Sub

 

[RobbieC]

Awesome! Thanks Scott, this does just what I'm after!