change transition menu key with VBA

[Philip Stromme]

I am trying to create a bowling score sheet, but I'm having trouble because
"/" activates the file menu, when I want to use to indicate a spare.

I want to create some code that will automatically change the transition
menu key when I open the file, and change it back when I close the file.

I found some code on Microsoft TechNet, at
http://www.microsoft.com/technet/scriptcenter/topics/office/manage/exceltr.mspx
and I put it under Workbook_Open. I'm getting "Variable not defined" errors,
but I'm not sure how I should define them. Any thoughts?

Option Explicit

Private Sub Workbook_Open()
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.TransitionMenuKey = ""

objExcel.Quit

End Sub

 

[Ron de Bruin]

Code looks like this

Private Sub Workbook_Open()
With Application
.TransitionMenuKey = ""
.TransitionNavigKeys = False
End With
End Sub

 

[Dave Peterson]

You could also format the cells as Text or start with a leading apostophe:
'/

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.TransitionMenuKey = "/"
End Sub
Private Sub Workbook_Open()
Application.TransitionMenuKey = ""
End Sub

 

[Philip Stromme]

Thank you. That works great, and it makes more sense than the code that I had
found. I added a Workbook_BeforeClose sub to change it back, too.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.TransitionMenuKey = "/"
.TransitionNavigKeys = False
End With
End Sub

 

[Ron de Bruin]

Hi Philip

This line is not needed (forgot to delete it)

.TransitionNavigKeys = False

 

[Philip Stromme]

Thanks for the reply. I don't want to require a leading apostrophe, because I
want it to be simple for other people to use. I tried formatting the cells as
text, but I still couldn't type / without an apostrophe (and if they were
formatted as text, I wouldn't be able to use numbers to calculate the score).

Your code works slick, though. Thanks.
~ Phil

 

[Dave Peterson]

Oops. I should have checked the Text formatting suggestion first.

Sorry.