Protected Worksheet can't Insert Line

JamieLee2k

New Member
Joined
May 9, 2004
Messages
38
I have this protected worksheet which is stopping people from clicking on the cells they don't need to access, hense protecting my work from being used by others in my workplace, but there is an issue I have faced and I don't know how to get around it.

What I want people to be able to do is insert a Row, but since the worksheet is protected they are unable to do this, If I tick "Select Locked Cells" & "Insert Rows" then yes people can insert rows but it means they can click on the cells I don't want them to click on, but can't edit them. I also have "Select Unlocked Cells" ticked so they can put in data that is required.

In a short I want to be able to:
  • Lock Cells so others can't click on the ones they will never use
  • Allow Users to Insert Rows
  • Protect worksheet

I really hope you all understood that, So what are my options?

Thanks
JamieLee2k
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
What about using a userform to act as the interface, the users can't use the sheet when the form is active, so you could unlock the sheet when the form is activated then lock the sheet once it has been used, and if the row is being added from the last then you would use an offset method.
 
Upvote 0
The userform is in the Visual Basic side of Excel.

Here is tutorial about userform from DataPig, all this will do is give you the basic idea. You would need more instructions to get through your project.

Are you able to upload a sample of the spreadsheet via Excel Jeanie so I can take a look at your headings and create the code and guide you.

http://www.datapigtechnologies.com/ExcelMain.htm

Look down the list until you see create your first form

If this is something you can achieve/work with then also look at the YouTube videos on Excel VBA Userforms

http://www.youtube.com/watch?v=5PN7lWJSobQ
 
Last edited:
Upvote 0
You could try this code, which I can't claim as my own! (got it from some kind soul on here before when I had a similar problem!)

Code:
Sub InsertRow()
  '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
  Call InsertRowsAndFillFormulas
End Sub
 
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
ActiveSheet.Unprotect
   Dim x As Long
   ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
   If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If
   'if you just want to add cells and not entire rows
   'then delete ".EntireRow" in the following line
      Dim sht As Worksheet, shts() As String, i As Long
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name
    x = Sheets(sht.Name).UsedRange.Rows.Count
ActiveSheet.Unprotect
    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
     Resize(rowsize:=vRows).Insert Shift:=xlDown
    Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillDefault
    On Error Resume Next
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select
   ActiveSheet.Protect
End Sub

  • In your Workbook, press Alt+F11 which opens the Project Editor
  • Click Insert>Module
  • Copy the code above and paste into the module
  • To run the code
  • Close the editor
  • To run the code, click Tools>Macro>Macros>InsertRow (or press Alt+F8 then choose "InsertRow" or set it to run from an autoshape etc)
The macro unprotects the sheet as it starts to run, then protects it as it ends

If your Sheet protection is passworded use

Code:
ActiveSheet.Unprotect Password:="yourpassword"
ActiveSheet.Protect Password:="yourpassword"
 
Upvote 0

Forum statistics

Threads
1,214,851
Messages
6,121,931
Members
449,056
Latest member
denissimo

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
Back
Top