Naming a range for specific users to edit

daycartes

New Member
Joined
Feb 18, 2016
Messages
22
Hi folks, daycartes here. My, this is a busy forum.
I am engaging on a rather large spreadsheet that is going to have 10-15 users. I want to use the "Allow Users to Edit Ranges" function.
Now, I have already studied the named range advice, however the range that I want to allow is not in a complete block but spans several non contiguous rows eg row 6, row 12, row 194 and will include about 30 rows from within up to about 400 rows. The range is not random but depends on another column so as in the example above row 6,12 and 194 will be chosen because they correspond to DS in column 4 rows 6,12 and 194.
I could probably separate the ranges in the "Allow Users to Edit Ranges" however the rows could change over time so it needs to be accomplished dynamically.I think that I need to use VBA.
I can select the sheet using VBA and also filter out the column containing DS so that I am left with the non contiguous rows but I need the VBA code to select the appropriate range.
Does anyone have the VBA code for "Allow Users to Edit Ranges"?
Many thanks in advance.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

MandeepBaluja

Board Regular
Joined
Jan 28, 2014
Messages
120
Sub Allow_Edit_Range_Rows_Dynamically()

'Note:-Title should always be unique but not contain a Dollar sign that's I put this
' Title:=Replace(Cells(i, 1).Address, "$", "")

Dim wksOne As Worksheet
strPwd1 = InputBox("Enter Password")

Set wksOne = Application.ActiveSheet
Dim temparray As Variant
' Protect the worksheet
'wksOne.Unprotect

temparray = Range("I1:I5") 'Range which contains Row number to allow

For i = 1 To 10
For j = LBound(temparray) To UBound(temparray)
If Cells(i, 1).Row = temparray(j, 1) Then
' MsgBox Replace(Cells(i, 1).Address, "$", "")
wksOne.Protection.AllowEditRanges.Add _
Title:=Replace(Cells(i, 1).Address, "$", ""), _
Range:=Rows(i), _
Password:=strPwd1

End If
Next
Next
wksOne.Protect

End Sub
 

daycartes

New Member
Joined
Feb 18, 2016
Messages
22
Thanks for your speedy response Mandeep. I opened up a new workbook and copied that into a new procedure which i then ran. The code worked in that it protected the sheet, however, if I understood what you sent me then cells i1:i5 should be editable but that does not seem to be the case. I think that we are nearly there though. Would you mind checking it out to see if the same happens for you. I'm new to VBA but have quite a bit of experience in Delphi. Thanks again
 

MandeepBaluja

Board Regular
Joined
Jan 28, 2014
Messages
120
You have asked for rows to allowedit and others to be locked without editing, In I1:I5 or I1:I200 you can put your row numbers in this range, this macro will allow those rows to edit dynamically selecting one by one and allowing it.Hope now you got that.

I1:I5 put 5,8,9,10,12 than these rows will be Allowed to Edit only
 

daycartes

New Member
Joined
Feb 18, 2016
Messages
22
Thanks for replying. Unfortunately despite putting various data into I1:I5 as you suggest the whole worksheet remains locked.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top