Changes meant for one sheet are applied to another after vba hides sheets

[kinney911]

Hi all, this is my first post here, and I'm fairly new to VBA, so bear with me. (sorry for the cryptic title)</SPAN abp="824">

I'm writing a macro that contains a sort of library that contains process steps. To add a step to the library, the user presses a button that calls a macro, which sends them through a few userForms in order to gather relevant information. When the user has input all of the info and the userforms are done, it plops them into a template worksheet that is pre-formatted to fit the library format. The template worksheets, Sheet9 and Sheet10, are in the main workbook, but are hidden (.visible = xlSheetVeryHidden) by default when the user is doing normal things in the worksheet. When the macro activates one or both of the template sheets, it hides the other sheets, Sheet1 through Sheet8, so that the user is locked into the template worksheet until they're done. To unhide the main sheets and re-hide the template, the user presses one of two buttons on the template sheet, which calls a macro that either adds the completed template to the library, or discards and acts like nothing happened.</SPAN abp="827">

The problem I'm encountering presents itself when the template is activated. On the surface, everything seems to have functioned as intended, but when you go to edit a cell, the cell Name Box will show cell names from Sheet1. If you actually enter values into these cells, upon pressing Enter, the cell that is displayed reverts back to the value it had prior, as if you had hit Escape. </SPAN abp="830">

This image shows that cell "B3" of Sheet1 is named "companyAbbrev". After leaving the userforms, sheet9 is displayed, and this image shows what happens if you go to cell "B3" of Sheet9. The cell name is the same whether you're actively editing it or not. Also notice that the formula bar displays the value of Sheet9!B3, not Sheet1!B3, even though the Cell Name box would indicate otherwise. This is what is displayed prior to pressing enter when changing data in a cell. After pressing enter, this happens. The formula bar still has the correct input, but the cell that the changes were supposed to be made to has reverted back to its previous value. After the return-macro-button is pressed, and sheet1 is visible, this is the value of Sheet1!B3.

If I manually switch sheets away from the template, then switch back into the template, everything is fine. If I do the same with VBA, nothing changes. The same is true if I create a blank worksheet and switch to the template.

Sorry for the long-winded question, but I'm dumbfounded. I have no idea why this is happening, and unfortunately, I've made many changes to this macro since the last time I tested this specific function. In the spoiler below is the code that hides/shows the various sheets depending on what's happening, but I'm not even sure if that is the culprit.</SPAN abp="841">

Thanks in advance for any help, because I'm stumped. </SPAN abp="845">

</SPAN abp="848">
Sub FlagCheck()</SPAN abp="850">
   
    Application.ScreenUpdating = False</SPAN abp="853">
    If Sheet4.Range("controlFlag") Or Sheet4.Range("pfmeaFlag") Then 'these flags determine which templates are shown</SPAN abp="855">
        If Sheet4.Range("controlFlag") Then</SPAN abp="857">
            Sheet9.Visible = xlSheetVisible</SPAN abp="859">
            Sheet9.Unprotect</SPAN abp="861">
        Else</SPAN abp="863">
            Sheet9.Visible = xlSheetVeryHidden</SPAN abp="865">
            Sheet9.Protect</SPAN abp="867">
        End If</SPAN abp="869">
        If Sheet4.Range("pfmeaFlag") Then</SPAN abp="871">
            Sheet10.Visible = xlSheetVisible</SPAN abp="873">
            Sheet10.Unprotect</SPAN abp="875">
        Else</SPAN abp="877">
            Sheet10.Visible = xlSheetVeryHidden</SPAN abp="879">
            Sheet10.Protect</SPAN abp="881">
        End If</SPAN abp="883">
        Sheet1.Visible = xlSheetVeryHidden</SPAN abp="885">
        Sheet1.Protect</SPAN abp="887">
        Sheet2.Visible = xlSheetVeryHidden</SPAN abp="889">
        Sheet2.Unprotect</SPAN abp="891">
        Sheet3.Visible = xlSheetVeryHidden</SPAN abp="893">
        Sheet3.Unprotect</SPAN abp="895">
        Sheet4.Visible = xlSheetVeryHidden</SPAN abp="897">
        Sheet4.Unprotect</SPAN abp="899">
        Sheet5.Visible = xlSheetVeryHidden</SPAN abp="901">
        Sheet5.Unprotect</SPAN abp="903">
        Sheet6.Visible = xlSheetVeryHidden</SPAN abp="905">
        Sheet6.Unprotect</SPAN abp="907">
        Sheet7.Visible = xlSheetVeryHidden</SPAN abp="909">
        Sheet7.Unprotect</SPAN abp="911">
        Sheet8.Visible = xlSheetVeryHidden</SPAN abp="913">
        Sheet8.Unprotect</SPAN abp="915">
   
    End If</SPAN abp="918">
 
'if both flags are false then make everything but the template pages visible, and re-protect the sheets that require protection</SPAN abp="921">
    If (Sheet4.Range("controlFlag") = False) And (Sheet4.Range("pfmeaFlag") = False) Then</SPAN abp="923">
       
        Sheet1.Visible = xlSheetVisible</SPAN abp="926">
        Sheet1.Protect</SPAN abp="928">
        Sheet2.Visible = xlSheetVisible</SPAN abp="930">
        Sheet2.Protect</SPAN abp="932">
        Sheet3.Visible = xlSheetVisible</SPAN abp="934">
        Sheet3.Unprotect</SPAN abp="936">
        Sheet4.Visible = xlSheetVisible</SPAN abp="938">
        Sheet4.Protect</SPAN abp="940">
        Sheet5.Visible = xlSheetVisible</SPAN abp="942">
        Sheet5.Protect</SPAN abp="944">
        Sheet6.Visible = xlSheetVisible</SPAN abp="946">
        Sheet6.Unprotect</SPAN abp="948">
        Sheet7.Visible = xlSheetVisible</SPAN abp="950">
        Sheet7.Protect</SPAN abp="952">
        Sheet8.Visible = xlSheetVisible</SPAN abp="954">
        Sheet8.Protect</SPAN abp="956">
        'now hide the template sheets</SPAN abp="958">
        Sheet9.Visible = xlSheetVeryHidden</SPAN abp="960">
        Sheet9.Protect</SPAN abp="962">
        Sheet10.Visible = xlSheetVeryHidden</SPAN abp="964">
        Sheet10.Protect</SPAN abp="966">
        'bep the user back to the home sheet</SPAN abp="968">
        Sheet1.Activate</SPAN abp="970">
    End If</SPAN abp="972">
    Application.ScreenUpdating = True</SPAN abp="974">
End Sub

</SPAN abp="976">

 

[kinney911]

I would edit the first post, but the time limit has expired. After doing some more tweaking with the code, I found that the problem is somehow related to the process of unhiding the template. As such, the problem doesn't seem to be dependant on the other sheets being forced to a "xlHidden" or "xlVeryHidden" state.

EDIT: Could it be a result of the macro being triggered by a shape instead of a button or ActiveX control? The shape is on the offending Sheet1.

 

[kinney911]

Ok, after a few good hours of troubleshooting, I found the cause. Over time, I had written multiple " 'worksheet'.Activate " calls into my macro, which must have been competing with eachother for precedence, the result of which was a very confused Excel interface. The thing that fixed it was moving all of my " 'worksheet'.Activate " lines to the last subroutine in the chain. In my case, that was the subroutine that called the initial userform, because all of the other operations branched out from, and returned to, that form. For now, it seems that un-hiding sheets and un-protecting them is alright; only the activation breaks my workbook.

Moral of the story: only activate your sheets once, and even then, only do that when your macro is done, and you're ready for them to be muddled with by the user.