Cell protection with Macro

[austcipher]

Hi I am trying to protect a range of cells that has data pushed into it from a macro I have set up. Once I protect the cell the macro will no longer allow me to push that data in.. .Is there a way to inhibit a user from inputting a value into a cell manually and just lett the macro do the work?

Thanks in adv J

 

[VoG]

Hi. Try like this

ActiveSheet.Unprotect password:="pw"
'
'your code here
'
ActiveSheet.Protect password:="pw"

 

[Misca]

Try:

ActiveSheet.Protect userinterfaceonly:=True

You can also include the .unprotect / .protect lines in your macro.

 

[austcipher]

Hi there, thanks for the quick reply.. Its actually not the macro I would like to protect but the cells the macro pushes information into. Every time I try to protect a cell and use the macro to push data into it, it come back with a line error. Can I protect the cell without limiting the macro pushing data in?
Thank in adv

 

[Misca]

It's not the macro the code protects but the worksheet. The "UserInterfaceOnly:=True" means it only protects the sheet from changes made from the User Interface (= manually). Macros can still work on the sheet as if it was unprotected.

Without the "UserInterfaceOnly:=True" the same line protects the worksheet so that even macros can't make any changes to it without unprotecting it first. The "Activesheet.Unprotect" -line in VoG's code does just that and ".protect" -line turns the protection back on. His code is also using password to protect it.

The password-attribute can be applied to the "UserInterfaceOnly:=True" macro as well.

 

[austcipher]

Thanks again!

So this is my current code:

Sub InsertData_Switch()
Dim arrRows As Variant
Dim EmptyCell As Range
Dim Rng As Range

arrRows = Array(10, 18, 24)

For Each T In arrRows
Set Rng = Worksheets("Switch").Range("I" & T).Resize(1, 12)

On Error Resume Next
Set EmptyCell = Rng.SpecialCells(xlCellTypeBlanks).Cells(1, 1)
If Err <> 0 Then MsgBox "There are no empty cells in row ." & T: Exit Sub
On Error GoTo 0

EmptyCell = Rng.Cells(1, 1).Offset(0, -2).Value
Next T

End Sub
Sub InsertData_HFC()
Dim arrRows As Variant
Dim EmptyCell As Range
Dim Rng As Range

arrRows = Array(15, 22, 29, 43)

For Each T In arrRows
Set Rng = Worksheets("HFC").Range("I" & T).Resize(1, 12)

On Error Resume Next
Set EmptyCell = Rng.SpecialCells(xlCellTypeBlanks).Cells(1, 1)
If Err <> 0 Then MsgBox "There are no empty cells in row ." & T: Exit Sub
On Error GoTo 0

EmptyCell = Rng.Cells(1, 1).Offset(0, -2).Value
Next T

End Sub
Sub InsertData_Advanced_Technology()
Dim arrRows As Variant
Dim EmptyCell As Range
Dim Rng As Range

arrRows = Array(27)

For Each T In arrRows
Set Rng = Worksheets("Advanced Technology").Range("I" & T).Resize(1, 12)

On Error Resume Next
Set EmptyCell = Rng.SpecialCells(xlCellTypeBlanks).Cells(1, 1)
If Err <> 0 Then MsgBox "There are no empty cells in row ." & T: Exit Sub
On Error GoTo 0

EmptyCell = Rng.Cells(1, 1).Offset(0, -2).Value
Next T

End Sub
Sub InsertData_OSS_Tools()
Dim arrRows As Variant
Dim EmptyCell As Range
Dim Rng As Range

arrRows = Array(35)

For Each T In arrRows
Set Rng = Worksheets("OSS & TOOLS").Range("I" & T).Resize(1, 12)

On Error Resume Next
Set EmptyCell = Rng.SpecialCells(xlCellTypeBlanks).Cells(1, 1)
If Err <> 0 Then MsgBox "There are no empty cells in row ." & T: Exit Sub
On Error GoTo 0

EmptyCell = Rng.Cells(1, 1).Offset(0, -2).Value
Next T

End Sub
Sub InsertData_Remedy()
Dim arrRows As Variant
Dim EmptyCell As Range
Dim Rng As Range

arrRows = Array(28)

For Each T In arrRows
Set Rng = Worksheets("Remedy").Range("I" & T).Resize(1, 12)

On Error Resume Next
Set EmptyCell = Rng.SpecialCells(xlCellTypeBlanks).Cells(1, 1)
If Err <> 0 Then MsgBox "There are no empty cells in row ." & T: Exit Sub
On Error GoTo 0

EmptyCell = Rng.Cells(1, 1).Offset(0, -2).Value
Next T

End Sub


Where would I seat this in? and I still need parts of the work sheet upprotected so field can be entered.. Thanks in adv again

 

[Misca]

Place the .unprotect -line to the beginning of each of your subs and end them with the .protect -line.

If you're going to use the Activesheet-sheet reference in your code you'd better make sure the sheet you want to unprotect is activated when the code runs. Might be safer to refer to the sheets with their names.

If you're using the "userinterfaceonly:=True" part in your sheet protection code you don't have to protect / unprotect your sheet. Just run the protect-macro once on an unprotected sheet and you're done.

 

[austcipher]

Ok.. I wonder if I am being a little confusing.. I have a spreadsheet with multiple tabs.. on tab 1 I want to protect cells I27 to T27 & I28 to T28 I have a macro on this page which pushes data into the above cells from cell block (C6 to F26).

How do I enable the macro to override the protection to allow data to be enbtered without allowing the user to manually entering anything in those cells?

Thanks in adv