Allow Ungrouping VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-1624989%22%20slang%3D%22en-US%22%3EAllow%20Ungrouping%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1624989%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20allow%20ungrouping%20in%20a%20protected%20worksheet%20however%20when%20saving%20and%20reopening%20the%20workbook%20and%20click%20to%20ungroup%20it%20is%20saying%20that%20i%20cannot%20do%20this%20on%20a%20locked%20worksheet.%20All%20the%20codes%20i%20have%20tried%20don't%20work.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1624989%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1625178%22%20slang%3D%22en-US%22%3ERe%3A%20Allow%20Ungrouping%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1625178%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F777996%22%20target%3D%22_blank%22%3E%40matt1770%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EUnprotect%20the%20sheet%20at%20the%20beginning%20of%20the%20macro%2C%20then%20protect%20it%20again%20at%20the%20end.%20For%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20MyUngroup()%0A%20%20%20%20ActiveSheet.Unprotect%20Password%3A%3D%22secret%22%0A%20%20%20%20'%20Your%20code%20to%20ungroup%20data%0A%20%20%20%20...%0A%20%20%20%20ActiveSheet.Protect%20Password%3A%3D%22secret%22%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EReplace%20%3CEM%3Esecret%3C%2FEM%3E%20with%20the%20password%20that%20you%20used%20to%20protect%20the%20worksheet.%20If%20you%20didn't%20specify%20a%20password%2C%20use%20%22%22%20or%20omit%20the%20Password%3A%3D...%20part.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi

 

I am trying to allow ungrouping in a protected worksheet however when saving and reopening the workbook and click to ungroup it is saying that i cannot do this on a locked worksheet. All the codes i have tried don't work.  

1 Reply

@matt1770

Unprotect the sheet at the beginning of the macro, then protect it again at the end. For example:

 

Sub MyUngroup()
    ActiveSheet.Unprotect Password:="secret"
    ' Your code to ungroup data
    ...
    ActiveSheet.Protect Password:="secret"
End Sub

 

Replace secret with the password that you used to protect the worksheet. If you didn't specify a password, use "" or omit the Password:=... part.