macros

[Lois]

Hi,
I am trying to protect a workbook using a macro allowing users the following
permissions:
insert rows, format cells, use auto filter. Have tried recording a macro &
typing the code in but i must be typing it in the wrong place as it dosent
recognise it! can anyone help?

 

[Otto Moehrbach]

Lois
Exactly what are you doing to run the macro?
Do you want the macro to run automatically? If so, when?
Post the macro you are trying to run. HTH Otto

 

[Lois]

Hi,
i had the macro assigned to a shortcut code. I have it detailed below
without the permissions to insert rows ect.

Sub ProtectAll()


Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="DESDS"
Next ws



End Sub
Sub UnProtectAll()


Dim wb As Workbook
Dim ws As Worksheet
Dim blnIsProtected As Boolean
Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
ws.Unprotect Password:="DESDS"
Next ws

Set wb = Nothing
Set ws = Nothing


End Sub

 

[Otto Moehrbach]

Lois
I take it that nothing happens when you run these macros. Is that
correct? If so, try this: enter the following line of code as the first
line after the Dim statements:
MsgBox "Hello"
Run the macro. Do you see the "Hello" message box? If not, the macro is
not running. What is your macro security setting (Tools - Macro -
Security). If you are running Excel 2007, is the workbook located (on your
hard drive) in a trusted source? HTH Otto

 

[Lois]

Hi
the macro runs as it is but i want to add the ability of users to insert
rows & edit cells into the macro. i dont know the 'code' for this or where
to type it in the macro
thanks

 

[Otto Moehrbach]

From the macro recorder:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True
The above statement protects the sheet and allows the user to insert rows.
To allow the user to edit cells isn't done in the VBA Protect code. You
must setup those cells you want him to edit beforehand. Are you familiar
with the Locking/Unlocking of cells? If a cell is LOCKED and the sheet
protected, the user CANNOT edit that cell. If a cell is UNLOCKED and the
sheet is protected, the used CAN edit that cell.
All cells are LOCKED by default. What you have to do is select those cells
in which you want editing allowed, and UNLOCK those cells. First select all
the editing cells. Then click on Format - Cells - Protection tab and
uncheck the LOCKED box.
Now, the user CAN edit those cells as he wishes. Come back if you need
more. HTH Otto