Worksheet Activate problem

[hrayani]

Hello Friends,

I have a workbook in which there are multiple sheets. The workbook worked fine until last week when I added some userforms.

I have noticed the below issues.........

1) Sometimes worksheet activation does not get triggered for a specific sheet if called from another sheet within the workbook.
2) If the VBA editor is open then it does not get triggered at all for that specific sheet.

here is the worksheet activate code

Private Sub Worksheet_Activate()

ActiveSheet.Unprotect Password:="merchant"

Worksheets("CURRENT PRODUCTION STATUS").EnableCalculation = True
  Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "1", , , 0
Range("A:T").Select 'set range zoom
ActiveWindow.Zoom = True
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Range("C24").ClearContents
Range("B7").Select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingRows:=True, Password:="merchant"
End Sub

called from this code

Private Sub open_cps_from_ro()
    
    Application.ScreenUpdating = False    

    ActiveWorkbook.Unprotect Password:="2270166"
   
    Sheets("CURRENT PRODUCTION STATUS").Visible = True    
    Worksheets("RUNNING ORDER STATUS").Cells(ActiveCell.Row, 1).Copy
    Worksheets("CURRENT PRODUCTION STATUS").Range("B7").PasteSpecial Paste:=xlPasteValues    
    Sheets("RUNNING ORDER STATUS").Visible = xlSheetVeryHidden    
    ActiveWorkbook.Protect Password:="2270166", Structure:=True, Windows:=True   

    Application.ScreenUpdating = False
    
End Sub

All errors are gone as soon as the userforms are removed from the file...

below is 1 of the 9 userforms I am using in the workbook

code used for this userform

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub quantity_value_list_Change()
    Range("B4").Value = Me.quantity_value_list.Value
End Sub

Private Sub quantity_value_list_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Me.quantity_value_list.Value = Range("B4").Value   
End Sub

Can someone pls shed some light on this as why this is happening ??

Regards,

Humayun

 

[GWteB]

A worksheet activate event takes place if the sheet is activated (manually or via code) or if something is pasted on it.
Your code below causes a worksheet activate event (red lines) just twice:
1) activation of worksheet CURRENT PRODUCTION STATUS;
2) activation of a "random" worksheet, ie. the one that was active before this procedure was launched.

Private Sub open_cps_from_ro()
   
    Application.ScreenUpdating = False   

    ActiveWorkbook.Unprotect Password:="2270166"
  
    Sheets("CURRENT PRODUCTION STATUS").Visible = True   
    Worksheets("RUNNING ORDER STATUS").Cells(ActiveCell.Row, 1).Copy
    Worksheets("CURRENT PRODUCTION STATUS").Range("B7").PasteSpecial Paste:=xlPasteValues    
    Sheets("RUNNING ORDER STATUS").Visible = xlSheetVeryHidden    
    ActiveWorkbook.Protect Password:="2270166", Structure:=True, Windows:=True  

    Application.ScreenUpdating = False
   
End Sub

It may be wise to include code that needs to be addressed at different times or in different scenarios in separate procedures. Such procedures do not depend solely on a specific event, but can also be directly invoked by other procedures.
As a side note, to avoid unwanted changes, it is wise to qualify worksheet ranges (especially if that code occurs in a userform) e.g. instead of ....

Private Sub quantity_value_list_Change()
    Range("B4").Value = Me.quantity_value_list.Value
End Sub

preferably something like this ....

Private Sub quantity_value_list_Change()
    ThisWorkbook.Worksheets("SheetName").Range("B4").Value = Me.quantity_value_list.Value
End Sub

 

[xenou]

Generally speaking, best not to rely on worksheet activation events for anything other than when a USER activates a worksheet.

In automated code you should not need to activate sheets at all (except sometimes at the very end when you want the code to present the user with a specific sheet activated as the last thing you have done - not for the sake of your own code but for the convenience of the user and what is visible to them when they have control or are otherwise using the workbook again).

To put this another way, you almost never need to use Activate or Select in order to get data from any worksheet or cell anywhere in the workbook.

I'm having a hard time understanding your code - why the selection and activation of sheets? why making them hidden? When is this code supposed to run? What is it doing?

But as far as why data would disappear, possibly it is tied to closely to the status of worksheets being active (which is a difficult thing to control when someone else is using the workbook). Also hidden sheets are never active and are deactivated when hidden (another sheet will generally become the active one, unless there are no active sheets left).

 

[hrayani]

Generally speaking, best not to rely on worksheet activation events for anything other than when a USER activates a worksheet.

In automated code you should not need to activate sheets at all (except sometimes at the very end when you want the code to present the user with a specific sheet activated as the last thing you have done - not for the sake of your own code but for the convenience of the user and what is visible to them when they have control or are otherwise using the workbook again).

To put this another way, you almost never need to use Activate or Select in order to get data from any worksheet or cell anywhere in the workbook.

I'm having a hard time understanding your code - why the selection and activation of sheets? why making them hidden? When is this code supposed to run? What is it doing?

But as far as why data would disappear, possibly it is tied to closely to the status of worksheets being active (which is a difficult thing to control when someone else is using the workbook). Also hidden sheets are never active and are deactivated when hidden (another sheet will generally become the active one, unless there are no active sheets left).

Hi xenou,

Thanks for the reply..

The worksheet named CURRENT PRODUCTION STATUS is showing daily production for a specific purchase order (user enter the desired order # in cell B7)
The worksheet named RUNNING ORDER STATUS is showing all the running purchase orders

Now if the user directly opens the worksheet CURRENT PRODUCTION STATUS then he/she inputs the desired order # in cell B7 and the sheet get the results.
Pls note that the worksheet CURRENT PRODUCTION STATUS contains worksheet change event in order to hide / show some rows based on the order # entered in cell B7

Now the reason for applying the worksheet activate event is this........

If the user is on RUNNING ORDER STATUS and if wants to look at the production of any specific order in the list then he/she places the cursor on that row and click a button & that copies the left most cell in that row & paste that order # in the CURRENT PRODUCTION STATUS in cell B7. And the paste method does not trigger the worksheet change event. That is why I applied the worksheet activate event.

Let me know if you have a better solution for this.

Having said that.... few hours ago I found what is causing trouble for the worksheet activate to get triggered. There are some userforms (having list box) which I added few days back in my workbook and in the properties of the list box there are some cells mentioned in the control source box. As soon as I clear the control source property from the list box then everything works fine....

Although I don't know why it is happening (that a control source property in the list box creating trouble for some other sheet's activate event) but I have figured out what is happening.

Your thoughts/feedback on this is more than welcome.

Regards,

Humayun

 

[hrayani]

Hi Gwteb,

Sorry I overlooked your post and saw it just now. Well thanks for the reply.

Well the activate event does not get triggered at all. If it would have been triggered then then it has to hide/unhide some rows based on what is copied in cell B7

Secondly, It is hard to understand that if the editor (password protected) is closed then the activate event triggers fine but it does not trigger when the VB editor is open.

It may be wise to include code that needs to be addressed at different times or in different scenarios in separate procedures. Such procedures do not depend solely on a specific event, but can also be directly invoked by other procedures.
As a side note, to avoid unwanted changes, it is wise to qualify worksheet ranges (especially if that code occurs in a userform) e.g. instead of ....

Private Sub quantity_value_list_Change()
    ThisWorkbook.Worksheets("SheetName").Range("B4").Value = Me.quantity_value_list.Value
End Sub

I can't use a sheetname in the code as the userform is being used on different sheets. Let me know if you have a solution for this.

As of now I have just cleared the control source property and everything is working fine now. I am not sure if it will create some problem in future or not but at least it is working fine as of now.

Thanks once again for your time and kindly do let me know if there is a better way of handling it.

Regards,

Humayun

 

[xenou]

If I go back to Post number 1, the only thing I see that "calls" (or might call) worksheet activate is copying a value to the worksheet (that should not trigger a worksheet activate event, I don't think), and otherwise just the fact that you hide a worksheet. Because if you hide a worksheet, you generally activate another worksheet - some other non-hidden worksheet will become active.

As you can see just by reading the above sentence, its not super exact how this is being done ... "...some other non-hidden worksheet will become active....". Well, maybe you have only two worksheets in the workbook! That would be okay then, but doesn't lend itself well to changes in the future (a third worksheet being added...).

I would generally prefer something more exact in a case like this ... so if you want to invoke the worksheet activate event either (directly) activate the worksheet with a statement that literally activates the worksheet you want to activate, or (my preference) call the code that is run on worksheet activate:

PSEUDOCODE

worksheet_activate() {
    call DoStuff
}

DoStuff() {
    //Do A, B, C things
}

Now just call DoStuff() from another worksheet when you want to. In fact you could just call worksheet_activate() if we only had one procedure above, not two. The point is, you can run the code that runs when worksheets are activated without activating the worksheets.

I'm not a big fan of doing a lot of hiding and unhiding of rows. I find such code complicated and difficult to maintain. I understand that a lot of excel vba applications undertake this effort to try to control the user experience (hopefully to make it a good one). But the results are often fragile and I can't help but think there has to be a better way....

As far as the vba editor changing the results when open vs. when closed, I'll have to stay silent on that one - not clear to me.

 

[hrayani]

If I go back to Post number 1, the only thing I see that "calls" (or might call) worksheet activate is copying a value to the worksheet (that should not trigger a worksheet activate event, I don't think), and otherwise just the fact that you hide a worksheet. Because if you hide a worksheet, you generally activate another worksheet - some other non-hidden worksheet will become active.

As you can see just by reading the above sentence, its not super exact how this is being done ... "...some other non-hidden worksheet will become active....". Well, maybe you have only two worksheets in the workbook! That would be okay then, but doesn't lend itself well to changes in the future (a third worksheet being added...).

Thanks again for your thoughts on the matter

I have many worksheets in the workbook but only one worksheet is unhidden at a time. So immediately after I copy paste a purchase order from running order worksheet to the current production worksheet - the running order worksheet is hidden and the only one unhidden is the current production worksheet and that is why the worksheet activate event gets triggered for the current production worksheet (this is what I believe)

I would generally prefer something more exact in a case like this ... so if you want to invoke the worksheet activate event either (directly) activate the worksheet with a statement that literally activates the worksheet you want to activate, or (my preference) call the code that is run on worksheet activate:

I guess one option would be to add this line of code to make it more precise/exact as you said so - Although it is working fine now when I removed the control source cell reference in the userform list box properties (mentioned in post # 5

Private Sub open_cps_from_ro()
    
    Application.ScreenUpdating = False
    ActiveWorkbook.Unprotect Password:="2270166"
   
    Sheets("CURRENT PRODUCTION STATUS").Visible = True    
    Worksheets("RUNNING ORDER STATUS").Cells(ActiveCell.Row, 1).Copy
    Worksheets("CURRENT PRODUCTION STATUS").Range("B7").PasteSpecial Paste:=xlPasteValues
[B][COLOR=rgb(235, 107, 86)]    Worksheets("CURRENT PRODUCTION STATUS").Activate[/COLOR][/B]
    Sheets("RUNNING ORDER STATUS").Visible = xlSheetVeryHidden    
    ActiveWorkbook.Protect Password:="2270166", Structure:=True, Windows:=True   

    Application.ScreenUpdating = False    
End Sub

Regards,

Humayun