PDA

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



jammer6_9
04-24-2007, 03:53 AM
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.


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

Above code was taken from :thumb xld :thumb ...

Bob Phillips
04-24-2007, 04:25 AM
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

jammer6_9
04-24-2007, 04:41 AM
:bow: :bow: :bow: once again thnks xld for that quick response however all of the toolbars came visible giving that if statement..

Bob Phillips
04-24-2007, 05:18 AM
... 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
04-24-2007, 05:29 AM
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
04-24-2007, 05:48 AM
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
04-24-2007, 06:16 AM
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
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


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
04-24-2007, 06:22 AM
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
04-24-2007, 06:42 AM
So sorry xld :banghead: pls... Let me refresh...

1. Code below -->All Toolbars is disabled :thumb
***The only problem is I can not perfrom a "Right Click"???
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


2. Code below --> Right Click is OK...
***But Toolbars are enabled...???
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



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
04-24-2007, 07:05 AM
We seem to have lost some code in transposition

If oCB.Name "Cell" Then

should be

If oCB.Name <> &quot;Cell&quot; Then

jammer6_9
04-24-2007, 07:42 AM
Still no right click on the worksheet :doh:

Bob Phillips
04-24-2007, 07:54 AM
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
04-24-2007, 08:01 AM
:beg: 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 :confused:

Bob Phillips
04-24-2007, 11:46 AM
That is not the code I gave



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

jammer6_9
04-24-2007, 10:32 PM
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...:whistle: