multiple entries in the same column in a spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to input the entries in one column - ex: RF , RWW, RC, CTUM-NV,
It depends on the disposition on what code goes into that cell. But, they
are the same ones all the time. Is there a way to set-up a shortcut keys for
these.

I do not want to use drop down boxes because this is a huge spreadsheet.

We do not have Access, which that would of been my choice.

Any help would be greatly appreciated.
 
One possibility is to setup some AutoCorrect settings that will convert to
the desired results. The only hard part about it is finding unique key
combinations that are easily remembered to represent the desired codes.

For example:

Type the tilde character (~) and it automatically changes to CTUM-NV.

This can be very useful when you want to autocorrect long repetitive
strings. I don't think you really gain anything setting an autocorrect for a
2 character string like RF or RC.
 
What kind of a shortcut key are you looking for?

If you're looking for a single-keystroke solution, you could use a
worksheet event to assign these values to a key. Name the column
where the codes go CodeColumn, and paste the following code into its
VBA module:
-------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (Intersect(Target, Me.Range("CodeColumn")) Is Nothing) _
And Target.Columns.Count = 1 And _
Target.Address <> Target.EntireColumn.Address Then
Application.OnKey "1", "RF"
Application.OnKey "2", "RWW"
Application.OnKey "3", "RC_"
Application.OnKey "4", "CTUM_NV"
Else
Application.OnKey "1"
Application.OnKey "2"
Application.OnKey "3"
Application.OnKey "4"
End If
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "1"
Application.OnKey "2"
Application.OnKey "3"
Application.OnKey "4"
End Sub


---------
For your example codes, in the order you have them listed, the keys
1-4 are bound to the codes. Now, paste this code in a standard
module:
--------


Public Sub RF()
Selection.Value = "RF"
End Sub

Public Sub RWW()
Selection.Value = "RWW"
End Sub

Public Sub RC_()
Selection.Value = "RC"
End Sub

Public Sub CTUM_NV()
Selection.Value = "CTUM-NV"
End Sub


--------
Notice there is an underscore after the RC procedure, this is because
Excel will not allow RC as a name of a procedure. Also note that CTUM-
NV the dash is replaced by an underscore, also for naming reasons.

Now, whenever you have cells selected in the codes column
(CodeColumn), the keys 1 through 4 will be assigned to put those codes
in all cells selected. This functionality is disabled on three
conditions: selection consists of more than one column, selection
includes the entire codes column, or sheet is deactivated.

This is tricky to work with because it might cause your keys to be
bound when you don't want them to. The easier way to do this is to
record a macro and assign a Ctrl+whatever key combination.

Hope that helps, post back with questions.
 
Back
Top