Activesheet.Calculate

J

Jim May

Can I add the above function to my mouse right-click list of commands?
If so, how?
Jim May
 
F

Frank Kabel

Hi Jim
you may use the following code (add this entry to the right-click cell
menu):
Sub Add_Item()
Dim New_Entry As Object
Set New_Entry = CommandBars("cell").Controls.Add(Temporary:=True)
On Error Resume Next
New_Entry.Controls("Calculate").Delete
On Error GoTo 0

With New_Entry
.Caption = "Calculate"
.OnAction = "Calculate_sheet"
End With
End Sub


Sub calculate_sheet()
ActiveSheet.calculate
End Sub

Sub Delete_Item()
Dim myControl As CommandBarButton
For Each myControl In CommandBars("Cell").Controls
If myControl.Caption = "Calculate" Then
myControl.Delete
End If
Next
End Sub
 
J

Jim May

Frank, thanks for the reply,, have missed your being around. Welcome back!!
1) How do I get to the "right-click cell menu"?
2) Once there do I copy and paste all 3 of the Subs you provided?
Sorry,
not playing with a full deck here..
Jim
 
F

Frank Kabel

Hi
o.k. a step-by-step list :)
- first copy all 3 procedures in a module of your workbook. To get to
this module do the following:
-> open your workbook
-> Press ALT+F11
-> Insert a new module in your workbook and paste the code in this
new module
-> close the VBA editor, save the workbook
- After this run the 'Add_Item' macro (e.g. via 'Tools- Macro -
Macros')
- Now if you right-click with your mouse in any of your cells in a
worksheet you should see the entry 'Calculate' in this context menu 8at
the bottom of this context menu)

Note: To remove this entry simply start the 'Delete_Item' macro
 
J

Jim May

Got it !!
appreciate the walk-through :)
One more Q..
Now that I have this in my workbook, could I clear all sheets and data
and rename it book.xlt and save it into my XLStart folder (so as to make
this a more
permanent feature)?
Thanks,
Jim
 
F

Frank Kabel

Hi
yes this should work. And you can also add a call to the add_item
method in the workbook_open event. But you may also consider using an
add-in for this
 
G

Gord Dibben

Jim

Just a note here....

You could place the add_item code into the Workbook_Open and the delete_item
code into into BeforeClose code of ThisWorkbook module so's it would run when
the workbook is opened and closed.

Gord Dibben Excel MVP
 
J

JE McGimpsey

To make it permanent and accessible from any workbook, put the code in
your Personal.xls workbook, or another workbook or add-in in your
XLSTART directory, along with this code in the workbook/add-in's
ThisWorkbook code module:

Private Sub Workbook_Open()
Add_Item
End Sub
 
Top