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]

@nedbarra - I have been interpreting your reference to the Activate command as meaning the Sheet_Activate event since I could not see the Activate command used in the posted code. If you are in fact referring to the Sheet_Activate event, then it would be a rare occasion that the EnableEvents statement would be needed. It is more frequently used with the Worksheet_Change event to prevent perpetual loops when the code initiates change on the same worksheet. But it is set at False in the first part of a macro and bact to True in the latter part of the macro. When it is set to False, none of your event code will be triggered to run when they occur, incuding the Sheet_Activate event code. You need to be cleat about whether you are referring to the Activate command or the Sheet_Activate event.

 

[nedbarra]

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...

Just a few more words to say that the code you sent over is not taking to the task at all...
I took out a few 'older files' before the problem arose and placed it into a few of the 'monthly worksheets' there...
But, unfortunately it's not doing what was expected
Also received a similar code trial from JLGWhiz...and that's playing no part either
Something seriously amiss...but I cannot find what it is...
It's not not picking up the opening code from 'thisworkbook' and opens with one of the monthly worksheets
Should I contact MS and ask for them to have a look at some of their suggestions
Thanks again

 

[MARK858]

There is nothing in the either of the codes posted that does anything differently to your original code posted other than remove the unnecessary Selects and activates.

It's not not picking up the opening code from 'thisworkbook' and opens with one of the monthly worksheets

There is nothing either in your original code or the codes we posted that affects what worksheet is opened.

Please upload your workbook to a free file hosting site like www.box.com or www.dropbox.com. Mark the file for sharing and paste the link it provides in the the thread.
Then we will probably ask exactly what you expect the workbook to do.

Make sure that you alter any sensitive data before uploading the file.

 

[JLGWhiz]

I think you need to find someone locally who can look at your code modules to see what you actually have set up. The problem sounds to me like you have conflicting event codes, but not being able to see them all, it is difficult to make a good analysis and recommend a fix. If there is someone in your office or a friend who is proficient with vba that could take a look at your file, they could probably spot the problem. Or, you could copy and paste the ThisWorkbook and Sheet codes here and maybe we can figure it out. But i don't believe we will solve it from the one code that was posted.

 

[nedbarra]

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

Thanks for the code you sent, JLGWhiz...
Unfortunately it is taking no part at all in the search for a solution

Also received a similar set of code lines from MARK858, which took the same
stance and refused to help...

I'm stumped...to say the least...I'm no more than a VBA 'junior' who had a smple
code that ran impecably for years, until just a few months back
And now this
Do you think an approach to the MS team would be a worthwhile venture...
Thanks again

 

[MARK858]

Do you think an approach to the MS team would be a worthwhile venture...

Not if your code is causing an issue, they would only get involved if you could show a bug in the Excel program itself.

Also see post number 13

 

[nedbarra]

There is nothing in the either of the codes posted that does anything differently to your original code posted other than remove the unnecessary Selects and activates.


There is nothing either in your original code or the codes we posted that affects what worksheet is opened.

Please upload your workbook to a free file hosting site like www.box.com or www.dropbox.com. Mark the file for sharing and paste the link it provides in the the thread.
Then we will probably ask exactly what you expect the workbook to do.

Make sure that you alter any sensitive data before uploading the file.

MARK858
Thanks for your latest...
No, I was quite aware of the fact that the code instruction you sent was simply your version of events
All further macros were of similar content to the original posting of last week
They again held the 'activate' event which catered for the movement of all related figures onto the
accounts sheets which arte compiled in keeping with the HMRC Tax Form categories...
So, there may have been only half-a-dozen 'activate' worksheets.
The main concern is 'why now' after so many years with no call for changes.

I'm concerned as to why there continues to be this 'irregular' error on opening any of the current files.
First 2 files opened today were fault free and completed the movement to the desiignated worksheet first attempt.
And then it suddenly came to a hault...no worksheet action...and no reason seen for the stoppage.

I will attempt to contact the MS help.chatline tpday/tomorrow and see just what they suggest

 

[MARK858]

And then it suddenly came to a hault...no worksheet action...and no reason seen for the stoppage.

That is because you set the Events to False at the end of the Sub. You need to set it to True again before they will run!
Application.EnableEvents = False stops all Event code running until you reset it to True.

 

[nedbarra]

That is because you set the Events to False at the end of the Sub. You need to set it to True again before they will run!
Application.EnableEvents = False stops all Event code running until you reset it to True.

Hi again, MARK858...
I acknowledge your advice on the 'EnableEvents' settings and find unfortunately it is doing nothing towards a solution...and your very own code sent me has done nothing, either...the 'activate' command seems to have a mind of it's own
I have a 'data transfer' button on each of the monthly worksheets which the user presses when they have completed their input at that time...sending the data to the summary sheets...so, I have slotted the code into that module and all seems well

Thank you for your time and trouble...I trust it will be ok to for myself to use the code sent in future writings

 

[nedbarra]

@nedbarra - I have been interpreting your reference to the Activate command as meaning the Sheet_Activate event since I could not see the Activate command used in the posted code. If you are in fact referring to the Sheet_Activate event, then it would be a rare occasion that the EnableEvents statement would be needed. It is more frequently used with the Worksheet_Change event to prevent perpetual loops when the code initiates change on the same worksheet. But it is set at False in the first part of a macro and bact to True in the latter part of the macro. When it is set to False, none of your event code will be triggered to run when they occur, incuding the Sheet_Activate event code. You need to be cleat about whether you are referring to the Activate command or the Sheet_Activate event.

Thanks again, JLGWhiz...
Yeh, all instances are 'Sheet_activate'...it was set up to give the monthly worksheet standard detail of 'company/owner...trade description and period of the accounts...
This info is held on a 'personal detail' sheet which accordingly sends what is needed to individual worksheets
The problem only comes into play when used in the 'sheet_activate' event use...
All other times, when used in a module, it works as required...
So, I have now managed to include the code in a 'data transfer' event which has a button on each monthly worksheet for the user to press when they have completed the data input for that particular sitting...
It appears to be working well and has changed my views on 'sheet_activate events'

Thank you for your time and trouble...and the more appropriate code setting you sent me...
I trust you will be fine and well if I were to use your better example when the occasion arises