assign a custom character to a keyboard key in excel

rubble2

As above really, I produce a large number of spreadsheets into which test results are entered. In locations (cells) where no data is recorded this is indicated by entering a hyphen symbol multiple times.

I wonder is it possible to produce a custom symbol which consists of say 5 hyphens (essentially a dashed line) which could be input by hitting a single keystroke?

I have checked the special characters which are available as standard but none of them seem to be suitable, any help would be appreciated.

Thanks

Andy_L

If its just to indicate that the cell is "intentionally blank" rather than missed out do you need 5 dashes rather than just 1?
If its needed to draw the eye to missing data you could use conditional formatting to tune the cell red if there is just 1 dash entered

rubble2

It is to report that the cell is intentionally blank, one dash just looks a bit 'lost'. I have always entered multiple dashes and would prefer to continue doing so, just looking for a quicker way of achieving this.

System

Write a macro to put your hyphens in the current cell and assign the macro to a shortcut key

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = "'

---

"

End Sub

How you link to a shortcut key may vary depending on Excel version

fwor

Would it not be far simpler to use the Autocorrect function instead of writing macros?

System

You could end up hit by the law of unintended consequences.

indesisiv

The length of time it takes you to hit a shortcut will be the same as typing

---

Even with using auto correct you are not going to save much time and there may be issues for instance i wouldn't recommend replacing - with

---

.
If there are lots of cells in a row just fill the value down.

System

But a macro gives guaranteed consistency of content. It's easy enough to only do 4 hyphens by mistake and if there is any counting or manipulation then errors will creep in. A macro removes that area of error.

paddyrg

http://www.extendoffice.com/documents/excel/772-excel-fill-blank-cells-with-0-or-specific-value.html

The first of these methods takes a little practice, and read the four steps carefully - but it's powerful and fast

To open the Go To | Special window, do Control+G, then press the Special button :-)

woodworm001

I use a macro very similar to the above to add a tick (windings) in excel, that is assigned to a keyboard shortcut. Save it into my personal.xlsm and the macro is available in every spreadsheet I use.

EchoLocation

Personally I would just use a Macro for it, however, the drawback of a Macro is that you cannot undo the action. So if you accidentally run it on the wrong cell you could overwrite and lose whatever value was already there. So building on !!!!!!'s code it might be an idea to either publicly store the current ActiveCell value which can then be recalled using an 'undo' Macro should it be required...

Public undovalue As String

Sub NoDataValue()
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
undovalue = ActiveCell
ActiveCell.FormulaR1C1 = "'

---

"

End Sub



Sub NoDataValueUndo()
'
' Keyboard Shortcut: Ctrl+Shift+U
'
ActiveCell.FormulaR1C1 = undovalue
undovalue = Empty

End Sub

(I've also amended the shortcut-key combination above as it's a better idea to assign Macros to uppercase letters)

Or perhaps even easier you could just add an If clause to check if the ActiveCell is blank before entering your value...

Sub NotIfBlank()
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
If ActiveCell <> "" Then Exit Sub Else
ActiveCell.FormulaR1C1 = "'

---

"

End Sub

If you find your shortcut-keys are not working, press Alt-F8, then select the Macro and click Options. Then set your preferred shortcut letter (Hold Shift to enter in uppercase) and change to Ctrl+Shift+