change transition menu key with VBA
 

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/scr...e/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

change transition menu key with VBA
 

Code looks like this

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Philip Stromme" wrote in message
...
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/scr...e/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

change transition menu key with VBA
 

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 wrote:

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/scr...e/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

--

Dave Peterson

change transition menu key with VBA
 

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" wrote:

Code looks like this

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Philip Stromme" wrote in message
...
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/scr...e/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

change transition menu key with VBA
 

Hi Philip

This line is not needed (forgot to delete it)

.TransitionNavigKeys = False


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Philip Stromme" wrote in message
...
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" wrote:

Code looks like this

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Philip Stromme" wrote in message
...
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/scr...e/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

change transition menu key with VBA
 

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" wrote:

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 wrote:

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/scr...e/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

--

Dave Peterson

change transition menu key with VBA
 

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

Sorry.

Philip Stromme wrote:

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" wrote:

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 wrote:

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/scr...e/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

--

Dave Peterson


--

Dave Peterson