keyboard Shortcut for the ½ character

[RincewindWIZZ]

Hi

I have some tedious data entry to do into a spreadsheet. Mostly text.
Every once in a while I have to use the 1/2 character in a string

So I can copy and paste the character from the clipboard (very quick)
but copy/paste gets used a lot anyway so then I have
- to stop typing. go find the 1/2 character and copy/paste (very slow); or
- insert symbol (which is a slow process)
I dont seem to be able to create a keyboard shortcut
I cant execute a macro in the middle of typing a row of text

I tried using autocorrect to change 1/2 into the half character and that's not bad but it only seems to work if there is a space in front of the 1
so
21/2 does not change but
2 1/2 becomes 2½ which is great

Any better suggestions?

TFAI

 

[RincewindWIZZ]

Actually your latest version does exactly this.
Select a cell (B5). Format->Protection lock = untick
Review->Protect sheet (both select locked and unlocked ticked)
goto B5
Ctrl-space selects the entire column
But I can enter characters from the keyboard just fine

And now Review->Unprotect sheet is greyed out!
And then after fiddling around trying to select some other menus it is no longer greyed out!!
So then Review->Unprotect sheet and ctrl-space produces 1/2 just fine!

 

[Jaafar Tribak]

Actually your latest version does exactly this.
Select a cell (B5). Format->Protection lock = untick
Review->Protect sheet (both select locked and unlocked ticked)
goto B5
Ctrl-space selects the entire column
But I can enter characters from the keyboard just fine

I followed the steps you described above: I unlocked the cell , then protected the worksheet but couldn't reproduce the issue you are experiencing.

ctrl-space produces 1/2 just fine for me on unlocked cells in a protected sheet.

 

[RincewindWIZZ]

Re: keyboard Shortcut for the ½ character

I followed the steps you described above: I unlocked the cell , then protected the worksheet but couldn't reproduce the issue you are experiencing.

ctrl-space produces 1/2 just fine for me on unlocked cells in a protected sheet.

Hhhmmm - may be some interaction with one of my badly coded VBA routines.
I'm busy for rest of today but I'll put the spreadsheet up on a file sharing site this evening

Thanks for your continued support

 

[RincewindWIZZ]

Re: keyboard Shortcut for the ½ character

I'm busy for rest of today but I'll put the spreadsheet up on a file sharing site this evening

So there were some issues (menu greyed out, spreadsheet locked up etc) most of which I am unable to reproduce at will. The one reproduceable issues is
double click to open OnKeyEx causes Excel to lock up on the splash screen (small green square) while loading

I'll now patch the code into the demo spreadsheet and see if that fixes things

 

[RincewindWIZZ]

Re: keyboard Shortcut for the ½ character

I'll now patch the code into the demo spreadsheet and see if that fixes things

Works fine in the small demo work book that I set up to send to you. opens OK on double click as well

Works fine in the full workbook. Again this opens happily with double click


So thanks again for all your help.

KR
R

 

[Jaafar Tribak]

Re: keyboard Shortcut for the ½ character

Works fine in the small demo work book that I set up to send to you. opens OK on double click as well

Works fine in the full workbook. Again this opens happily with double click


So thanks again for all your help.

KR
R

Happy it worked for you in the end and thank you for the feedback.

 

[RincewindWIZZ]

Re: keyboard Shortcut for the ½ character

Spoke to soon!
it seems th context menu for rows has disappeared
Click on a Row or Column and then right click brings up a context menu. Works well for me on a blank workbook the original of the workbook I have been amending

On all of the workbooks with your code, there is no context menu
OnKeyEx
My demo
and the Final Workbook

Conrext menu is OK outside the range defined in your code
Cell and Worksheet context menus are OK

 

[Jaafar Tribak]

Re: keyboard Shortcut for the ½ character

Spoke to soon!
it seems th context menu for rows has disappeared
Click on a Row or Column and then right click brings up a context menu. Works well for me on a blank workbook the original of the workbook I have been amending

On all of the workbooks with your code, there is no context menu
OnKeyEx
My demo
and the Final Workbook

The code runs a loop in the background while the selection lies within the ApplyToRange Range so that it can watch for the user key strokes... The loop stops only when getting out of that range.

Unfortunatly, this running background loop is also the reason why the context menu (and other UI functionalities) disappear.

The only two remaining alternatives that I can think of to runing a loop is to place the monitoring code inside a timer callback or to install a keyboard hook both of which need carefull error-handling and will need some considerable setup.

And so, in the light of the above issues and depending on the user specifics, one may consider to simply enter the ½ character manually instead of using complicated vba code.

However, for the sake of completeness, I'll try to write the alternative code (either using a Timer or a keyborad hook) and post back if something wothty comes up.

Regards.

 

[RincewindWIZZ]

Re: keyboard Shortcut for the ½ character

No need to go to all that trouble for me.

On the sheets that the 1/2 character is needed it is actually a bonus not having the context menu. But there are other sheets in the workbook where the context menu is important

So for my purposes, it would be sufficient to have the loop active only when 1 of a list of sheets is the active sheet

Is that an easier solution?

 

[Jaafar Tribak]

Re: keyboard Shortcut for the ½ character

So for my purposes, it would be sufficient to have the loop active only when 1 of a list of sheets is the active sheet

Is that an easier solution?

Yes.

When you set the ApplyToRange to say : "Sheet1!A1:G20", the loop should only be running when the active selection lies within that specific range.

Once you select a range outside Sheet1!A1:G20 or move to a different sheet or a different workbook, the loop should stop and therefore you should have the context menu available as normal.

However, for your particular requirement regarding the context menu for rows (and for columns), you could easily edit the HookKey Sub located in the ThisWorkbook Module as follows :

Private Sub HookKey(ByVal OnKeyMacroName As String)

    On Error GoTo errHandler

[COLOR=#FF0000]    If Selection.Rows.Count <> Rows.Count And Selection.Columns.Count <> Columns.Count Then[/COLOR]
        Call OnKeyEx(VBA.vbKeySpace, MOD_CONTROL, OnKeyMacroName, Range(APPLY_TO_RANGE))
[COLOR=#FF0000]    End If[/COLOR]

   Exit Sub
errHandler:
    MsgBox "Runtime Error :" & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

The above change should allow you to display the row & column context menus even when inside the ApplyToRange range.