Clearing status cells - Excel Macros

JacoboPolavieja
Posts: 91
Joined: Thu Sep 10, 2009 6:00 pm

It is indeed a calculation.. thank God I finally see the lght at the end of the tunnel! Well... and thanks a lot to you nigelk :D.

There's just one step left I think which is that I have several sheets. Just guessing... as the code works on a per sheet basis, is there any problem just copying the sheet I have finally working to have several ones?
Maybe there's a performance issue or another issue I haven't thought about. After all the struggle maybe I'm just too worried and looking for too many problems.

Thank you very much for all the help nigelk, you can't know how much you've helped me, but you will sure have a free beer if you ever come to Spain ;).

Thanks! Cheers!
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Press ALT F11 to be taken to the vba screen. On the left hand side you should see Sheet1, Sheet2,Sheet3 and
"This Workbook"

Double click on it and over on the right, from the drop down boxes, select "Workbook" and then "Sheet Calculate"

The macro will then work for EVERY sheet in the workbook, so be careful

You can write some code so that it only acts on the sheets you require, i.e If activesheet.name ="Not This One"
then exit sub

and after that put your code in

Nigel.
User avatar
to75ne
Posts: 2413
Joined: Wed Apr 22, 2009 5:37 pm

sorry to resurrect yet again

i use the following simple macro

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("O6").Select
Selection.ClearContents
Range("K5").Select
End Sub

all it does if o6 the global status cell states "ok",
(becuse the command in the global command field L6 as fired). i can press control and "q". and clear cell o6 (reset the sheet) and return the cursor to cell k5 for convenience.

i would like this to happen automatically every time the command in L6 is fired but, i cant get it to happen no matter how many times i alter the previous code examples on this thread i,e, the calculate event example.

i dont wish to change the status of any other cell or cells, just 06.

obviously im doing something wrong?

could somebody point me in the right direction please.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

I hardly ever bother with it now.

Can anyone help?
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

Try using these events

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub


Regards
Paul.
Post Reply

Return to “Tips and tricks”