I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson


--

Dave Peterson

Thanks for that code not seen 'With Me' before, and just using .Columns is
quicker than selecting the ranges, which is what I would have done.

thanks

"Dave Peterson" wrote:

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson


--

Dave Peterson

Me is the object that owns the code.

If you use Me under the ThisWorkbook module, then Me is ThisWorkbook.

If you use Me under a Userform module, then Me is the userform.

In this case, Me is the sheet that owns the code.


Graham Y wrote:

Thanks for that code not seen 'With Me' before, and just using .Columns is
quicker than selecting the ranges, which is what I would have done.

thanks

"Dave Peterson" wrote:

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

I saw this post that you helped Graham Y on deactivated sheet. I have a
similar situation. I tried to do it by following your foot steps and I
couldn't do it.

Please help. Basically, I have an Excel file with multiple worksheets. I
want to have the macro executes the code after leaving/deactivating a
specific sheet "abc". So I place the following code in my abc sheet. The
code executes, but it would not stop executing. Please help.

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
End With
End Sub

"Dave Peterson" wrote:

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson


--

Dave Peterson

The .pastespecial causes the _deactivate event to fire once again.

So you could sandwich application.enableevents = false and
application.enableevents = true around each of those lines or just go whole hog:

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
Application.EnableEvents = True
End With
End Sub



Please Help wrote:

Hi Dave,

I saw this post that you helped Graham Y on deactivated sheet. I have a
similar situation. I tried to do it by following your foot steps and I
couldn't do it.

Please help. Basically, I have an Excel file with multiple worksheets. I
want to have the macro executes the code after leaving/deactivating a
specific sheet "abc". So I place the following code in my abc sheet. The
code executes, but it would not stop executing. Please help.

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
End With
End Sub

"Dave Peterson" wrote:

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

Thank you very much for your prompt response.

The code works and executes, but it does not execute every time I deactivate
from the sheet. How can I make it to execute every time when I deactivate
from the sheet?

Thanks.

"Dave Peterson" wrote:

The .pastespecial causes the _deactivate event to fire once again.

So you could sandwich application.enableevents = false and
application.enableevents = true around each of those lines or just go whole hog:

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
Application.EnableEvents = True
End With
End Sub



Please Help wrote:

Hi Dave,

I saw this post that you helped Graham Y on deactivated sheet. I have a
similar situation. I tried to do it by following your foot steps and I
couldn't do it.

Please help. Basically, I have an Excel file with multiple worksheets. I
want to have the macro executes the code after leaving/deactivating a
specific sheet "abc". So I place the following code in my abc sheet. The
code executes, but it would not stop executing. Please help.

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
End With
End Sub

"Dave Peterson" wrote:

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Check to make sure you didn't turn off enableevents and leave it off.

If that's not the problem, describe the circumstances where it doesn't fire.

ps.

Add an irritating test message to your code to make sure you know if it's
firing:

Option Explicit
Private Sub Worksheet_Deactivate()
msgbox me.name & " deactivate event"
With Me


Please Help wrote:

Dave,

Thank you very much for your prompt response.

The code works and executes, but it does not execute every time I deactivate
from the sheet. How can I make it to execute every time when I deactivate
from the sheet?

Thanks.

"Dave Peterson" wrote:

The .pastespecial causes the _deactivate event to fire once again.

So you could sandwich application.enableevents = false and
application.enableevents = true around each of those lines or just go whole hog:

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
Application.EnableEvents = True
End With
End Sub



Please Help wrote:

Hi Dave,

I saw this post that you helped Graham Y on deactivated sheet. I have a
similar situation. I tried to do it by following your foot steps and I
couldn't do it.

Please help. Basically, I have an Excel file with multiple worksheets. I
want to have the macro executes the code after leaving/deactivating a
specific sheet "abc". So I place the following code in my abc sheet. The
code executes, but it would not stop executing. Please help.

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
End With
End Sub

"Dave Peterson" wrote:

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

It seems to work now. When I sent you the message below, it didn't work.
It worked for one time after the file is opened; then it stopped working. In
order to make it work, I had to click on the Run Sub button in VBAProject to
fire. After I ran the Run Sub, it started to fire after each deactivation.

That was in a test file. After I have copied the code to the actual file,
it seems to work since then.

I am not sure why. Is it possible that it would stop working, and I have to
do the Run Sub in VBAProject to activate the code?

Do you mind sharing briefly the rationale behind having the code between two
Application.EnableEvents? By having that code, what is it turning off?

Thank you very much for your helps. Below is my code.

Private Sub Worksheet_Deactivate()
Application.ScreenUpdating = False
MsgBox Me.Name & " deactivate event"
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
.Range("A1").Copy
.Range("A1").PasteSpecial xlPasteAll
Application.EnableEvents = True
End With
Application.ScreenUpdating = True
End Sub

"Dave Peterson" wrote:

Check to make sure you didn't turn off enableevents and leave it off.

If that's not the problem, describe the circumstances where it doesn't fire.

ps.

Add an irritating test message to your code to make sure you know if it's
firing:

Option Explicit
Private Sub Worksheet_Deactivate()
msgbox me.name & " deactivate event"
With Me


Please Help wrote:

Dave,

Thank you very much for your prompt response.

The code works and executes, but it does not execute every time I deactivate
from the sheet. How can I make it to execute every time when I deactivate
from the sheet?

Thanks.

"Dave Peterson" wrote:

The .pastespecial causes the _deactivate event to fire once again.

So you could sandwich application.enableevents = false and
application.enableevents = true around each of those lines or just go whole hog:

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
Application.EnableEvents = True
End With
End Sub



Please Help wrote:

Hi Dave,

I saw this post that you helped Graham Y on deactivated sheet. I have a
similar situation. I tried to do it by following your foot steps and I
couldn't do it.

Please help. Basically, I have an Excel file with multiple worksheets. I
want to have the macro executes the code after leaving/deactivating a
specific sheet "abc". So I place the following code in my abc sheet. The
code executes, but it would not stop executing. Please help.

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
End With
End Sub

"Dave Peterson" wrote:

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Excel is always monitoring stuff that's happening. You can tie into that
monitoring system by using an event macro.

But there are lots of times you don't want your code to trigger another event.

For instance, say you want to add 1 to the value in A1 whenever a cell changes
on your worksheet (ignore the fact that this is a nutty idea).

You would think that something like this code placed in the worksheet module
would be enough:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Range("A1")
If IsNumeric(.Value) Then
.Value = .Value + 1
End If
End With
End Sub

But there's (probably) a bug in this code. You change B1 and A1 gets changed
(by your code). But A1 getting changed causes A1 to get changed (again) and
then again and again and again... until excel gets bored and gives up.

(Probably a bug because you may want to do something like this on purpose--not
in this case, I would guess.)

The better code would look like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Range("A1")
If IsNumeric(.Value) Then
'tell excel to stop monitoring
Application.EnableEvents = False
.Value = .Value + 1
'ok to for excel to start monitoring again
Application.EnableEvents = True
End If
End With
End Sub

or maybe better...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

With Me.Range("A1")
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value + 1
End If
End With

ErrHandler:
Application.EnableEvents = True
End Sub

Then if something bad happens, events are still enabled when the code finishes.

==============

I think what happened is that you were testing the code and you broke out of it
(just once) in the middle of the code. (You hit the End key on the VBE
toolbar.)

But the code had turned off events and didn't get a chance to turn them back on.

When you started the code manually, it finished ok--and that included the line
of code that turned events back on.

==============

And that .pastespecial is a funny thing. It caused the worksheet_activate to
fire again (like the change to A1 in my earlier example caused the _change event
to fire).

You have to tell excel to stop looking for things that would cause events to
fire.

Try this with your old code (comment the .enableevents lines):

Step through the code (just F8, f8, f8 until it's done). No problems, huh.

Now put a break point on the "with Me" line. (select that line and hit F9)

Now go back to excel and activate your worksheet, then activate a different
worksheet in the same workbook. You'll see that each time that the
..pastespecial lines try to run, the even fires again.




Please Help wrote:

Dave,

It seems to work now. When I sent you the message below, it didn't work.
It worked for one time after the file is opened; then it stopped working. In
order to make it work, I had to click on the Run Sub button in VBAProject to
fire. After I ran the Run Sub, it started to fire after each deactivation.

That was in a test file. After I have copied the code to the actual file,
it seems to work since then.

I am not sure why. Is it possible that it would stop working, and I have to
do the Run Sub in VBAProject to activate the code?

Do you mind sharing briefly the rationale behind having the code between two
Application.EnableEvents? By having that code, what is it turning off?

Thank you very much for your helps. Below is my code.

Private Sub Worksheet_Deactivate()
Application.ScreenUpdating = False
MsgBox Me.Name & " deactivate event"
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
.Range("A1").Copy
.Range("A1").PasteSpecial xlPasteAll
Application.EnableEvents = True
End With
Application.ScreenUpdating = True
End Sub

"Dave Peterson" wrote:

Check to make sure you didn't turn off enableevents and leave it off.

If that's not the problem, describe the circumstances where it doesn't fire.

ps.

Add an irritating test message to your code to make sure you know if it's
firing:

Option Explicit
Private Sub Worksheet_Deactivate()
msgbox me.name & " deactivate event"
With Me


Please Help wrote:

Dave,

Thank you very much for your prompt response.

The code works and executes, but it does not execute every time I deactivate
from the sheet. How can I make it to execute every time when I deactivate
from the sheet?

Thanks.

"Dave Peterson" wrote:

The .pastespecial causes the _deactivate event to fire once again.

So you could sandwich application.enableevents = false and
application.enableevents = true around each of those lines or just go whole hog:

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
Application.EnableEvents = True
End With
End Sub



Please Help wrote:

Hi Dave,

I saw this post that you helped Graham Y on deactivated sheet. I have a
similar situation. I tried to do it by following your foot steps and I
couldn't do it.

Please help. Basically, I have an Excel file with multiple worksheets. I
want to have the macro executes the code after leaving/deactivating a
specific sheet "abc". So I place the following code in my abc sheet. The
code executes, but it would not stop executing. Please help.

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
End With
End Sub

"Dave Peterson" wrote:

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.



Graham Y wrote:

I've set up two custom views, 'TheLot' & 'Dave' I want to make sure the
'TheLot' is elected when the user moves to another sheet. So I put following
into the shee deactivate event:
ActiveWorkbook.CustomViews("TheLot").Show
Which works, but it also pulls me back to that sheet!

I may be tackling this all wrong, but what I wanted to do was to make sure
all the collumns and the autofilter was removed when the user left the sheet,
if they had used the 'Dave' custom view.

Help would be much appreciated

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

Thank you very much for taking the time to explain to me.

"Dave Peterson" wrote:

Excel is always monitoring stuff that's happening. You can tie into that
monitoring system by using an event macro.

But there are lots of times you don't want your code to trigger another event.

For instance, say you want to add 1 to the value in A1 whenever a cell changes
on your worksheet (ignore the fact that this is a nutty idea).

You would think that something like this code placed in the worksheet module
would be enough:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Range("A1")
If IsNumeric(.Value) Then
.Value = .Value + 1
End If
End With
End Sub

But there's (probably) a bug in this code. You change B1 and A1 gets changed
(by your code). But A1 getting changed causes A1 to get changed (again) and
then again and again and again... until excel gets bored and gives up.

(Probably a bug because you may want to do something like this on purpose--not
in this case, I would guess.)

The better code would look like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Range("A1")
If IsNumeric(.Value) Then
'tell excel to stop monitoring
Application.EnableEvents = False
.Value = .Value + 1
'ok to for excel to start monitoring again
Application.EnableEvents = True
End If
End With
End Sub

or maybe better...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

With Me.Range("A1")
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value + 1
End If
End With

ErrHandler:
Application.EnableEvents = True
End Sub

Then if something bad happens, events are still enabled when the code finishes.

==============

I think what happened is that you were testing the code and you broke out of it
(just once) in the middle of the code. (You hit the End key on the VBE
toolbar.)

But the code had turned off events and didn't get a chance to turn them back on.

When you started the code manually, it finished ok--and that included the line
of code that turned events back on.

==============

And that .pastespecial is a funny thing. It caused the worksheet_activate to
fire again (like the change to A1 in my earlier example caused the _change event
to fire).

You have to tell excel to stop looking for things that would cause events to
fire.

Try this with your old code (comment the .enableevents lines):

Step through the code (just F8, f8, f8 until it's done). No problems, huh.

Now put a break point on the "with Me" line. (select that line and hit F9)

Now go back to excel and activate your worksheet, then activate a different
worksheet in the same workbook. You'll see that each time that the
..pastespecial lines try to run, the even fires again.




Please Help wrote:

Dave,

It seems to work now. When I sent you the message below, it didn't work.
It worked for one time after the file is opened; then it stopped working. In
order to make it work, I had to click on the Run Sub button in VBAProject to
fire. After I ran the Run Sub, it started to fire after each deactivation.

That was in a test file. After I have copied the code to the actual file,
it seems to work since then.

I am not sure why. Is it possible that it would stop working, and I have to
do the Run Sub in VBAProject to activate the code?

Do you mind sharing briefly the rationale behind having the code between two
Application.EnableEvents? By having that code, what is it turning off?

Thank you very much for your helps. Below is my code.

Private Sub Worksheet_Deactivate()
Application.ScreenUpdating = False
MsgBox Me.Name & " deactivate event"
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
.Range("A1").Copy
.Range("A1").PasteSpecial xlPasteAll
Application.EnableEvents = True
End With
Application.ScreenUpdating = True
End Sub

"Dave Peterson" wrote:

Check to make sure you didn't turn off enableevents and leave it off.

If that's not the problem, describe the circumstances where it doesn't fire.

ps.

Add an irritating test message to your code to make sure you know if it's
firing:

Option Explicit
Private Sub Worksheet_Deactivate()
msgbox me.name & " deactivate event"
With Me


Please Help wrote:

Dave,

Thank you very much for your prompt response.

The code works and executes, but it does not execute every time I deactivate
from the sheet. How can I make it to execute every time when I deactivate
from the sheet?

Thanks.

"Dave Peterson" wrote:

The .pastespecial causes the _deactivate event to fire once again.

So you could sandwich application.enableevents = false and
application.enableevents = true around each of those lines or just go whole hog:

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
Application.EnableEvents = False
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
Application.EnableEvents = True
End With
End Sub



Please Help wrote:

Hi Dave,

I saw this post that you helped Graham Y on deactivated sheet. I have a
similar situation. I tried to do it by following your foot steps and I
couldn't do it.

Please help. Basically, I have an Excel file with multiple worksheets. I
want to have the macro executes the code after leaving/deactivating a
specific sheet "abc". So I place the following code in my abc sheet. The
code executes, but it would not stop executing. Please help.

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
.Range("a10:a64").Copy
.Range("o10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("N10").FormulaR1C1 = _
"=IF(LEN(RC[-13])=3,CONCATENATE(""a"",RC[-13]),RC[-13])"
.Range("N10:N64").FillDown

.Range("n10:n64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("A10:O64").Sort _
Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Range("o10:o64").Copy
.Range("a10").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

.Range("n10:o64").Clear
End With
End Sub

"Dave Peterson" wrote:

I'm not sure what breaks if you have hidden rows or columns, but how about just
showing all the columns and rows when you leave that sheet. (Drop the custom
view completely.)

Option Explicit
Private Sub Worksheet_Deactivate()
With Me
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
.Columns.Hidden = False
End With
End Sub

Graham Y wrote:

The problem is that I need to ensure that all the data is visible when they
leave the sheet, because the data here is used mainly to supply look up data
for some forms.
I could manually remove the view by unhiding the columns and removing the
autofilter, but I thought there would be a way, of just activating the view,
I can't even find out which view is current, although I could be looking for
the autofilter.

Thank you for making me think about another route, but if anyone knows how
to do waht I was hoping to, I'd still like to know.

"Dave Peterson" wrote:

I bet it would be much easier to change to the custom view you want when you
activate the sheet.