Can't 'activate'...for some reason I know not of....

[nedbarra]

Have specially self-formatted 'accounts sheets' which I email clients for return when completed to suit current tax period.
These worksheets have relied on the 'activate' command to bring them to screen with basic vba and have been in use for some 10 years +
This is a primary command alongwith 'deactivate' which sends all monthly data to the respective sheets and has served me very well for the
period they've been in use.
The 'activate' command suddenly and strangely stopped functioning on Friday last and 'recovery' and updates have done nothing for the cause.
A working on the 'worksheet_open() event has only led to difficult thoughts as I'm not exactly a heavy vba user.
A command button would be the answer I suppose, but I most times don't want to be checking if the users have been 'pressing the button'
where and when they should have.
Any suggestions on replacing the 'activate' command with something equally as useful would be very much appreciated.
Set-out as follows

Private Sub worksheet_activate()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Me.ScrollArea = "A16:y56"
Application.ScreenUpdating = True
Worksheets("aug").Range("a1").Select

'description
Worksheets("aug").Range("c4").Select
ActiveCell.Value = ThisWorkbook.Sheets("prsnldet").Range("g28")
'business
Worksheets("aug").Range("f3").Select
ActiveCell.Value = ThisWorkbook.Sheets("prsnldet").Range("g26")
'start date
Worksheets("aug").Range("w4").Select
ActiveCell.Value = ThisWorkbook.Sheets("prsnldet").Range("n16")
'end date
Worksheets("aug").Range("w5").Select
ActiveCell.Value = ThisWorkbook.Sheets("prsnldet").Range("n18")

Application.EnableEvents = False
Worksheets("aug").Range("a1").Select
Application.ScreenUpdating = True

End Sub

 

[JLGWhiz]

Try running this little snippet and see if it starts working again.

Sub t()
Application.EnableEvents = True
End Sub

If it does, then you will need to check any code yhou have recently installed to see if it has the Application.EnableEvents statement in it equalling False without resetting it to True and fix that code.

 

[nedbarra]

Hi, JLGWhiz....thanks for taking an interest in this puzzling enigma.
The 'screenupdating / enable events / display alerts - have been running in various true / false positions in the format shown...
I have had a spell on the 'enable' options 'true / false' which was advised on another question but has done nothing to help the problem this time.
I'm aware there are plenty comments on these events quoting how troublesome they can be and advising to avoid using them
But, I've been using the same code set-up for donkeys and have only had small issues when changing the 'enable events' etc to true has helped to solve whatever was wrong...
These occasions have been far between, tho'

Would I maybe be best to go into the ThisWorkbook and write code from April to April...using the workbook open event.
Meaning ALL monthly worksheets would be 'named and dated' on each 'opening ' of the workbook.
The code is same for every month so I need only change the 'month' on the individual tabs.
And that keeps the user will not need to tackle this and will be ' hands-free'

Almost all my VBA work has been done on Excel worksheets so this night be the problem all along
I will need to draft up a worksheet enter the code thro' the 'module' option
I will not be surprised if this is maybe my problem
Thanks for your interest and comments

 

[MARK858]

Is there a reason why you are setting Events to False at the end of the code in the first place? it is very unusual, you normally set it to False at the start of the code and True at the end?

 

[nedbarra]

Is there a reason why you are setting Events to False at the end of the code in the first place? it is very unusual, you normally set it to False at the start of the code and True at the end?

Thanks, Mark858...
You'll most likely note that I'm not a VBA 'heavy' altho' I'd like very much to be deeper into things
Yeh, the EnableEvents = false was an earlier 'help' question solution that 'Yongle' gave me, if I remember...
So, it's just been a matter of course now, to include in all current worksheets

All has been running fine and well for what the work type I use VBA for
I have a 'data transfer button' on every monthly sheet which the user presses to move all the data done at that sitting - to the relevant summary and P& L sheets etc...and all has been fine
Up until Friday when my sheets did not pick up the 'owner/business title and dates of the tax year.

My reading on 'activate' has not been good and it seems that many are not exactly too happy with what it does not do.
My own view is that it is a very important 'event handler' and is certainly a must have for me.
I have tried all the various options with the 'true / false but just cannot get the solution to this problem

I had an office update on Friday last which seems to have been the start of the problem.
I did a recovery back to before the last update...but nothing to give hope came out of it.
I may have to try MS chatline to see if they can resolve
It looks to me that this was the definite cause...
Thank you

 

[JLGWhiz]

Application.EnableEvents = False

@MARK858 is trying to point out that this line of code is not necessary, based on what is contained in your Worksheet_Activate code. You can delete that line, run the snippet I posted and your problem should go away.

 

[JLGWhiz]

I did a little modification to your code to eliminate unecessary recorder language. See if the code will work as you want it . Try it first in a copy of your file before applying your original, because you will need to delete the old code, so this one can run. Excel will not allow two of the same event macros in the same sheet code module.

Private Sub worksheet_activate()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Worksheets("aug")
'description
    .Range("c4") = Sheets("prsnldet").Range("g28")
'business
    .Range("f3") = Sheets("prsnldet").Range("g26")
'start date
    .Range("w4") = Sheets("prsnldet").Range("n16")
'end date
    .Range("w5") = Sheets("prsnldet").Range("n18") 
End With
Application.ScreenUpdating = True
End Sub

 

[MARK858]

the EnableEvents = false was an earlier 'help' question solution that 'Yongle' gave me, if I remember...
So, it's just been a matter of course now, to include in all current worksheets

I am sure Yongle never suggested to put Application.EnableEvents = False at the end of the code. It is used to stop the code that you are running from triggering other event code to run. Once the code has finished then it gets set back to True or else no event code will run when needed.

that many are not exactly too happy with what it does not do.

It is rather that people are not happy with what it does do. It slows down code and causes screen flicker.
It is extremely rare to need to use Activate in any code other than right at the end possibly to change the sheet/workbook, the same goes for Select.
If you want to use Events = False then you would use it something like the below

Private Sub worksheet_activate()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    Me.ScrollArea = "A16:y56"
    'Application.ScreenUpdating = True
    'Worksheets("aug").Range("a1").Select

    'description
    With Worksheets("aug")
        .Range("c4").Value = ThisWorkbook.Sheets("prsnldet").Range("g28")
        'business
        .Range("f3").Value = ThisWorkbook.Sheets("prsnldet").Range("g26")
        'start date
        .Range("w4").Value = ThisWorkbook.Sheets("prsnldet").Range("n16")
        'end date
        .Range("w5").Value = ThisWorkbook.Sheets("prsnldet").Range("n18")
    End With
 
    With Application
        .EnableEvents = True
        .Goto Worksheets("aug").Range("a1"), True
        .ScreenUpdating = True
    End With

End Sub

 

[nedbarra]

I am sure Yongle never suggested to put Application.EnableEvents = False at the end of the code. It is used to stop the code that you are running from triggering other event code to run. Once the code has finished then it gets set back to True or else no event code will run when needed.


It is rather that people are not happy with what it does do. It slows down code and causes screen flicker.
It is extremely rare to need to use Activate in any code other than right at the end possibly to change the sheet/workbook, the same goes for Select.
If you want to use Events = False then you would use it something like the below

Private Sub worksheet_activate()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    Me.ScrollArea = "A16:y56"
    'Application.ScreenUpdating = True
    'Worksheets("aug").Range("a1").Select

    'description
    With Worksheets("aug")
        .Range("c4").Value = ThisWorkbook.Sheets("prsnldet").Range("g28")
        'business
        .Range("f3").Value = ThisWorkbook.Sheets("prsnldet").Range("g26")
        'start date
        .Range("w4").Value = ThisWorkbook.Sheets("prsnldet").Range("n16")
        'end date
        .Range("w5").Value = ThisWorkbook.Sheets("prsnldet").Range("n18")
    End With

    With Application
        .EnableEvents = True
        .Goto Worksheets("aug").Range("a1"), True
        .ScreenUpdating = True
    End With

End Sub

Thanks once more, MARK858...
Apologies for the rather muddled quoting of Yongle's advice for the 'enable events'...
I had similar 'activate' probs then which may be linked again to the current one...
Workbook was opening at wrong page ( result of a Worksheet("homepage"),activate ) and Yongle
advised to insert the 'EnableEvents' before my 'sheets.activate' command ...
It could maybe be that this same problem is at the root of the current one.
When I switched on this morning all initially went to plan...the workbook opening as it should
and the 'activate' successfully opening the 'homepage' ...
Another file opening did the same and I thought we were maybe back to how it should operate...
But, 3rd file opening and we were back to head-scratching...
I, unfortunately, need the 'activate' command to transfer the data that has been input by the user
to the 'accounts section' worksheets...so, I am caught in a trap and cannot see any other way
apart from introducing an onscreen 'button' to do what is needed
I appreciate your help with this
Would you agree that a 'button' would be the best solution...

 

[MARK858]

I, unfortunately, need the 'activate' command to transfer the data that has been input by the user
to the 'accounts section' worksheets

You definitely do not need to Activate any other sheet to transfer data to another worksheet but you don't have that in your code, test the code either I or JLGWhiz posted.

The Worksheet.Activate event is fine (that is just how the macro is triggered), it is the Selects and ActiveCells we are saying there is an issue with.

I personally would use the code I posted as any interaction with a cell can trigger another event depending on how any other Event code you have is written but the Application.EnableEvents = False goes near the start as I have it and the True near the end.