Home

Using Edit Ranges in a Table

%3CLINGO-SUB%20id%3D%22lingo-sub-754908%22%20slang%3D%22en-US%22%3EUsing%20Edit%20Ranges%20in%20a%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754908%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20that%20is%20going%20to%20be%20storing%20the%20entries%20made%20by%20users%20on%20a%20separate%20tab%20in%20the%20same%20workbook.%20The%20user%20will%20enter%20their%20data%20and%20then%20click%20a%20button%20which%20runs%20a%20macro%20to%20insert%20a%20row%20at%20the%20top%20of%20the%20table%20and%20copy%20their%20entries%20into%20the%20table.%20I%20tried%20using%20%22Allow%20Edit%20Ranges%22%20so%20that%20they%20would%20only%20have%20access%20to%20Row%202%20in%20the%20table%20and%20it%20worked%2C%20however%20every%20time%20a%20row%20is%20inserted%20the%20Allow%20Edit%20Range%20expands.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20set%20it%20so%20that%20only%20Row%202%20is%20editable%20without%20a%20password%3F%20Specific%20users%20will%20be%20provided%20a%20password%20to%20allow%20editing%20of%20the%20entire%20tab.%20Ultimately%2C%20I%20believe%20this%20will%20also%20end%20up%20being%20a%20Shared%20workbook%20as%20there%20could%20be%20a%20time%20when%20more%20than%20one%20user%20at%20a%20time%20needs%20to%20make%20entries.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EDonelle%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-754908%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-755976%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Edit%20Ranges%20in%20a%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755976%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376510%22%20target%3D%22_blank%22%3E%40JazzHands%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWould%20an%20option%20be%20to%20have%20the%20macro%20unprotect%2C%20then%20the%20data%20gets%20added%20and%20the%20macro%20re-protects%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20have%20a%20particular%20reason%20you%20want%20Row%202%20to%20be%20editable%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%20when%20considering%20shared%20workbooks%20note%20that%20you%20can't%20use%20the%20traditional%20shared%20workbook%20functionality%20when%20Tables%20are%20present.%26nbsp%3B%20%26nbsp%3BHowever%20if%20you're%20using%20OneDrive%20Co-Authoring%20then%20you%20can%20but%20I'm%20not%20convinced%20that%20changes%20made%20via%20macro%20will%20work%20in%20that%20scenario%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
JazzHands
Occasional Visitor

I have a table that is going to be storing the entries made by users on a separate tab in the same workbook. The user will enter their data and then click a button which runs a macro to insert a row at the top of the table and copy their entries into the table. I tried using "Allow Edit Ranges" so that they would only have access to Row 2 in the table and it worked, however every time a row is inserted the Allow Edit Range expands. 

Is there a way to set it so that only Row 2 is editable without a password? Specific users will be provided a password to allow editing of the entire tab. Ultimately, I believe this will also end up being a Shared workbook as there could be a time when more than one user at a time needs to make entries.

 

Thank you,

Donelle

1 Reply
Highlighted

Hi @JazzHands 

 

Would an option be to have the macro unprotect, then the data gets added and the macro re-protects?

 

Do you have a particular reason you want Row 2 to be editable?

 

Also when considering shared workbooks note that you can't use the traditional shared workbook functionality when Tables are present.   However if you're using OneDrive Co-Authoring then you can but I'm not convinced that changes made via macro will work in that scenario