Solved: How can I enable a right click on this code?

[jammer6_9]

How can I write a code to enable user to perform a right click prior of copy and pasting data to sheet cells... Correct me if I'm wrong due to below code I guess disabled the right click option in the workbook upon opening.

[vba]
Private Sub Workbook_Activate()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB
mFormulaBar= Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False

Private Sub Workbook_Deactivate()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Application.DisplayFormulaBar = mFormulaBar
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True

End Sub
[/vba]
Above code was taken from xld ...

[Bob Phillips]

[vba] Private mFormulaBar

Private Sub Workbook_Activate()
Dim oCB As CommandBar

For Each oCB In Application.CommandBars
If oCB.Name "Cell" Then
oCB.Enabled = False
End If
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
End Sub

Private Sub Workbook_Deactivate()
Dim oCB As CommandBar

For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
End Sub [/vba]

[jammer6_9]

once again thnks xld for that quick response however all of the toolbars came visible giving that if statement..

[Bob Phillips]

... however all of the toolbars came visible giving that if statement..

I am not sure if you are saying it worked or not.

[jammer6_9]

right click works on the worksheet but all of the toolbars came visible which i have hidden. On my worksheet I have made "My Toolbar"...

I am not sure if you are saying it worked or not.

[Bob Phillips]

Do you mean that you hide toolbars in a workbook, activate this workbook and all bars get hidden, but when you reactivate the first worksheet, your hidden bars suddenly become visible?

[jammer6_9]

Yes from the code that I have taken from you, I was able to hide all the Toolbars as I wanted to happen when I activate a workbook... The only problem that I wanted to fixed is "Enable a Right Click" on the worksheet... Through the code that you recently gave
[vba]Private Sub Workbook_Activate()
Dim oCB As CommandBar

For Each oCB In Application.CommandBars
If oCB.Name "Cell" Then
oCB.Enabled = False
End If
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
End Sub
[/vba]

Right click on the worksheet is successful but the Toolbars was enabled.

Do you mean that you hide toolbars in a workbook, activate this workbook and all bars get hidden, but when you reactivate the first worksheet, your hidden bars suddenly become visible?

[Bob Phillips]

I am sorry, but you are totally losing me.

When I run this code, all toolbars except right-click are disabled.

One sentence you say ... I was able to hide all the Toolbars as I wanted

Another you say ... but the Toolbars was enabled

[jammer6_9]

So sorry xld pls... Let me refresh...

1. Code below -->All Toolbars is disabled
***The only problem is I can not perfrom a "Right Click"???
[vba]Private Sub Workbook_Activate()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB
mFormulaBar= Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False

End Sub
[/vba]

2. Code below --> Right Click is OK...
***But Toolbars are enabled...???
[vba]Private Sub Workbook_Activate()
Dim oCB As CommandBar

For Each oCB In Application.CommandBars
If oCB.Name "Cell" Then
oCB.Enabled = False
End If
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
End Sub


[/vba]
What I want to happen is
1. Toolbars will be hidden in the Workbook or Worksheet
2. Right Click is enabled in the worksheet

Thanks for your patience xld...

I am sorry, but you are totally losing me.

When I run this code, all toolbars except right-click are disabled.

One sentence you say ... I was able to hide all the Toolbars as I wanted

Another you say ... but the Toolbars was enabled

[Bob Phillips]

We seem to have lost some code in transposition

[vba] If oCB.Name "Cell" Then
[/vba]
should be

[vba] If oCB.Name <> &quot;Cell&quot; Then [/vba]

[jammer6_9]

Still no right click on the worksheet

[Bob Phillips]

Now you are changing your tune. You now say still, before you said you had the right-click, but all toolbars were still enabled.

[jammer6_9]

Please have a look on my workbook code... All I want is
1. Hide all Toolbars &
2. a Right Click enabled on the Worksheet...

Thanks

[Bob Phillips]

That is not the code I gave

[vba]

Private mFormulaBar
Private Sub Workbook_Activate()
Dim oCB As CommandBar

For Each oCB In Application.CommandBars
If oCB.Name <> "Cell" Then
oCB.Enabled = False
End If
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
AddNewToolBar


End Sub

Private Sub Workbook_Deactivate()
Dim oCB As CommandBar

For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
DeleteToolbar

End Sub
[/vba]

[jammer6_9]

Thanks xld. My workbook now work as I wanted. I was able to perfrom a right click on the cells as well as hide all the tool bars.

tnx tnx tnx...