Activesheet.Calculate

  • Thread starter Thread starter Jim May
  • Start date Start date
J

Jim May

Can I add the above function to my mouse right-click list of commands?
If so, how?
Jim May
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
I want to do a repetition calculation :
ie.

ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate


Question : Can i write ActiveSheet.x20Calculate ?? hope someone can he me, thank you
 
Back
Top