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

 

[Chewyhairball]

Thanks

Think you are correct and i thank you for your time and patience with me.

I have modified the current script you gave me to change when the date is input. Thats actually perfect for 1 authorisation without the need for any formulas.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified 6/27/18 5:40 AM EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastcolumn As Long
Dim ans As Long
Dim c As Range
ans = Target.Row
Lastcolumn = Cells(ans, Columns.Count).End(xlToLeft).Column
If Target.Value = date Then
Rows(ans).Value = Rows(ans).Value
For Each c In Range(Cells(ans, 1), Cells(ans, Lastcolumn))
If c.Value = "" Then c.Value = "-"
Next
End If
End Sub

if i can figure out how to add the extra criteria for 3 columns I would just change the columns as needed for each script (rather than last column). I think i could manage it if it were simply looking for 3 dates (todays date) but there is no way i will manage it looking for dates or dashes..lol

Thank you very much

Rory

 

[My Aswer Is This]

You said:

if i can figure out how to add the extra criteria for 3 columns I would just change the columns as needed for each script (rather than last column). I think i could manage it if it were simply looking for 3 dates (todays date) but there is no way i will manage it looking for dates or dashes..lol


What are the column

Say like:

Columns A C and D

Each one needing todays date entered.

Would it be possible todays date may be entered in column A Today but columns C date may not be entered till tomorrow. Meaning all three columns would not have the same date.
​

 

[Chewyhairball]

Hi
No. All three columns are cool mpletef on the same day. One or more may have today's date and any that don't would have a dash.

 

[My Aswer Is This]

Hi
No. All three columns are cool mpletef on the same day. One or more may have today's date and any that don't would have a dash.

I asked you this question which you did not answer:

What are the column

Say like:

Columns A C and D​

 

[Chewyhairball]

Hi

They are Y Z and AA

Thanks

Riry

 

[My Aswer Is This]

I need to refresh my memory:
If you put todays date in column Y Z and AA all same row
Do you want all formulas in the entire row changed to value?
And then any cell in the row that is empty you want a - entered?

Is that correct?

Well if that is true then we need to define the range.
If we do not define the range the script will put a - in all 16 thousand cells in that row which are empty.
And that could be a big task
Can you say any cell in active row from column A to column ZZ or something like that.

 

[Chewyhairball]

I need to refresh my memory:
If you put todays date in column Y Z and AA all same row
Do you want all formulas in the entire row changed to value?
And then any cell in the row that is empty you want a - entered?

Is that correct?

This is correct apart from having todays date in y,z and aa. It might be todays date is in Y and Z and AA have a -. It might be all three have todays date. It might be Z and AA have todays date and Y has a -. The best way I can put it is that one of the cells must have todays date and the others then have to have either todays date or a dash.

The rest of what you said is correct...
Do you want all formulas in the entire row changed to value?.......Yes
And then any cell in the row that is empty you want a - entered?..Yes

Can you say any cell in active row from column A to column ZZ or something like that.

Yes. That would work

thanks again

Rory

 

[My Aswer Is This]

Rory:
This is getting to complicated for me now.
There are two many if and statements to deal with for me.
And then if you have 10 different sheets with 10 different columns to deal with it gets more and more complicated.

I think someone else here at Mr. Excel may be able to help you.
I'm sure you know what you want.

I still think the easier way would be to tell users if the job is totally complete to double click on column A.
The cell in column A would turn green showing this job is complete.

But then I'm sure you know what is best with your situation.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Chewyhairball]

Hi

I was just about to reply actually. I think I am making this more complicated than it needs to be. You have actually given me at least 3 bits of code that will make my life significantly easier so i am extremely grateful for that.
I was about to suggest I just drop this idea and not waste anyones (your) time with it.

Thank you very much for all of your help and as I said before the codes you have given me which I can use in multiple sheets and not just for the original application.

have a great day

Rory

 

[My Aswer Is This]

Hi

I was just about to reply actually. I think I am making this more complicated than it needs to be. You have actually given me at least 3 bits of code that will make my life significantly easier so i am extremely grateful for that.
I was about to suggest I just drop this idea and not waste anyones (your) time with it.

Thank you very much for all of your help and as I said before the codes you have given me which I can use in multiple sheets and not just for the original application.

have a great day

Rory

Well I'm glad yo can read the script or at least part of it and understand what its doing. It's always best in the long run to try and understand the scripts others may supply so at some time you can write your own code to fit your needs.

Like some people say. It's best to teach a Man to fish then to fish for him.

Take care and have a nice holiday weekend if you live in the U.S