worksheet deactivate not working

[Chewyhairball]

Hi

I am trying to run this code to delete constants from column I when i leave a sheet.

Private Sub Worksheet_Deactivate()

Columns("I:I").Select
Selection.SpecialCells(xlCellTypeConstants, 2).Select
Selection.ClearContents
End Sub


The code runs fine using a button but if i try to run it using worksheet deactivate i get an error

"select method of range class failed" and debugging highlights "Columns("I:I").Select"

any help would be most appreciated

thanks

Rory

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Deactivate()
'Modified 6/28/18 6:30 PM EDT
On Error GoTo M
Columns("I:I").SpecialCells(xlCellTypeConstants, 2).ClearContents
Exit Sub
M:
MsgBox "No constants found"
End Sub

 

[Osvaldo Palmeiro]

Hi.
Try this code into the ThisWorbook module. In the code, change MySheetName accordingly

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
 If Sh.Name = "MySheetName" Then
  Sh.Columns("I:I").SpecialCells(xlCellTypeConstants, 2).ClearContents
 End If
End Sub

 

[Chewyhairball]

Hi folks

Both of these work a treat.

I was hoping to be able to have a go and add to what you have done to include some more code but alas I can't.

I should really just post the whole code straight off so apologies for that.

Could the following be added into the code before the clear contents.
I tried just adding it in but it doesn't work??

Columns("I:I").Select
Selection.Replace What:="complete", Replacement:="complete", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False


It replaces the word 'complete' (generated from a formula) with 'complete'. I noticed doing it like this turns it into text and runs another code (kindly written for me the other day by My Answer is This). Once that is done it clears the contents ( the code out have just given me)

This works great just run as a macro but not as a worksheet deactivate.
thanks

Rory

 

[My Aswer Is This]

I think you are still trying to get a previous script I gave to do what you want.
The script I wrote before looked at the last column in the row where you entered Complete.
Now this column was not always column I
Or if it was you did not say that in your previous post, You said last column

Now my script required you to manually enter complete into the cell.
And when you did that the script ran automatically.
Having a sheet deactivate script run and change the formula created value of complete to a actual value of complete and thinking that would activate your other sheet change event script I do not think would work.

You would probable get a error. Two scripts trying to run at same time.

Now a script which runs from a button click could do what you want if complete is generated from a formula.

But then does the script need to look in the last column with data or always look in column I

 

[Chewyhairball]

Hi
Yes I wanted it to look in the last column. What you gave me was spot on?. Since then I have been playing about and found that find and replace turns "complete" into text and fires the other script. This would be a handy thing so wrote a macro that runs from a button to try it. It worked.

What you gave me works great because as soon as a line is finished then someone just types complete.
I then realised that often there are 20-30 results ready to complete so doing them all at once would save typing(I had not thought of this when we first spoke so even though I had mentioned doing this it wasn't really something required and manual typing was ok). Rather than ask you again I gave it a go myself...I got as far as being able to do it with a button and then read an article about deactivating and thought that was cool. Even though it all worked with a button it didn't seem to work with deactivate.

The deleting of the word complete works fine but as you have pointed out now it probably won't work with the find and replace code. I don't know why?

The only reason I put column I for this is I noticed the code I had written seemed to take longer searching the whole sheet...I thought it's maybe not that big a hardship to specify a column in the code if it runs quicker.
I apologise if I am frustrating but I don't really know what I am doing and usually think of different things that might be useful as I progress. I don't really know what I want until I have tried something only to realise something else would be good.
Cheers
Rory

Ps. To run it from a button I have that done. I have half the code running from deactivate( what you gave me in this thread) but if I can't get the find and replace to run the same I will have to resign myself to the button option ?

 

[My Aswer Is This]

Show me the formula that causes the cell value to change to complete.

And I assume this formula will be in the same column on every row.

I believe my original answer was to double click on the value in a certain column when you wanted all the formulas in that row converted to values.
This would be a easy way and now the script would only be activated when a double click happened.
As now the script is running any time you change any value on the sheet.

But you would need to specify a certain column to double click on like column I or column A

 

[Chewyhairball]

Hi

Depending on which report sheet i am using there are different formulas. The reason for this is on some sheets there is 1 authorisation columns and others there are 2 or 3.

The authorisation column is where my boss puts in todays date if she is happy that row of results are OK. What you gave me before works good as i told her once she has put the date in just type complete into the next column and all the data is converted to text and blank spaces are filled with a dash.

Trying to take that a step further I thought it could be automated using a simple formula like...

assuming the authorising column is AA and the complete column is AB i use =if(AA1=Today(),"complete","")....copied down.

Yes this formula would be the same column on every row.

So if she has just Authorised 20 results by inputing todays date then find and replace seemed the best way to replace 'formula comment' with 'text comment' thus triggering the script.

I chose find and replace as copy and replace also removed the formulas from the column which was no use when the next set of results were authorised.

I am guessing you are probably going to ask, if you haven't already, why don't i just get the script to run when todays date is entered in to the authorisation column.

I thought it might be to complicated. For the sheets with 3 Authorisations it needs all 3 columns to have data entered before it considered checked and complete.
This might be all 3 of them having todays date. It might be one of them having todays date and the others having dashes(-) and various permutations.
Basically something has to be entered into all 3 columns and one of them has to be todays date.
(The reason for this is a standard lab practice that everything, including paperwork, that could contain a value or some data must have something entered even if it is a dash or N/A or anything)
I had assumed that rather than trying to explain that for a macro it would be easier to use a formula to generate the word complete when all three cells met the criteria. I am not at work at the moment and forget the exact formula i used but it is an If/and/or formula which says something like if aa= todays date or a dash and AB=todays date or dash etc etc ...then "complete".

Rory

 

[Chewyhairball]

this is an example of the other formula i use to generate 'complete'

=IF(OR(M15=TODAY(),M15="-")*AND(OR(L15=TODAY(),L15="-")*AND(OR(K15=TODAY(),K15="-"))),"complete","")

 

[My Aswer Is This]

I think I have run out of answers.
If you want a script to be activated immediately when a change is made to a sheet normally it has to occur when a manual change is made to a cell.

Sounds like to me you need a separate change event script for each sheet and not expect the same script to work on all sheets if the change event is different on each sheet.

Like on one sheet you could have the code look for a change in column AA if todays date is entered.
On another sheet your code would look in column AA AC and BB and if a change is made to any of these columns and the change is todays date is entered and all three of these columns have todays date then the script would run.

Trying to get a formula change to run a script is beyond my knowledgebase.