Macro para proteger hoja dependiendo de una celda

anibal3

New Member
Joined
Oct 20, 2014
Messages
5
Saludos Maestros. Quiero proteger un rango de datos si se cumple el valor de una Celda. Por ejemplo, si en la celda A1 esta "X" que proteja el rango desde la A5 hasta la A10; de igual manera si esta en la celda b1 "X" que proteja B5 hasta la B10 y asi en la columna C y D.
O el otro pedido que sería el que mejor quedaría que proteja las celdas que tengan el color rojo (puesto con formato condicional). Si se puede de esta forma sería excelente o el de mi primer planteamiento. Gracias.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Saludos

VBA Code:
Sub Protect()

Dim colnum As Long

ActiveSheet.Unprotect Password:="myPassword"

Cells.Locked = False

colnum = 1

Do Until colnum = 5
    If Cells(1, colnum) = "X" Then
    Range(Cells(5, colnum), Cells(10, colnum)).Locked = True
    End If
colnum = colnum + 1
Loop

ActiveSheet.Protect Password:="myPassword"

End Sub
 

anibal3

New Member
Joined
Oct 20, 2014
Messages
5
Saludos

VBA Code:
Sub Protect()

Dim colnum As Long

ActiveSheet.Unprotect Password:="myPassword"

Cells.Locked = False

colnum = 1

Do Until colnum = 5
    If Cells(1, colnum) = "X" Then
    Range(Cells(5, colnum), Cells(10, colnum)).Locked = True
    End If
colnum = colnum + 1
Loop

ActiveSheet.Protect Password:="myPassword"
Gracias por la ayuda pero quisiera que esta macro este permanentemente funcionando en la hoja 1
[/QUOTE]
 

anibal3

New Member
Joined
Oct 20, 2014
Messages
5
Gracias por la ayuda pero quisiera que esta macro este permanentemente activa en la Hoja 1
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951

ADVERTISEMENT

Try using the below.

VBA Code:
Sub Protect()

Dim colnum As Long
Dim ws as Worksheet

Set ws = Sheets("Sheet1")

ws .Unprotect Password:="myPassword"

ws. Cells.Locked = False

colnum = 1

Do Until colnum = 5
    If ws. Cells(1, colnum) = "X" Then
    ws. Range(ws. Cells(5, colnum), ws. Cells(10, colnum)).Locked = True
    End If
colnum = colnum + 1
Loop

ws.Protect Password:="myPassword"

End Sub

And call it from a sheet level macro.

VBA Code:
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then

Call Protect

End If
 

anibal3

New Member
Joined
Oct 20, 2014
Messages
5
Gracias por la respuesta pero no funciona al proteger protege toda la hoja y no solo la columna que tiene X. La idea es que solo las columnas que tienen marcada la X se protejan las demas queden desprotegidas.
 

anibal3

New Member
Joined
Oct 20, 2014
Messages
5

ADVERTISEMENT

Y cuando quito la X no se desprotege la columna
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Sorry, bad copy paste.

Sheet level macro is:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A5")) Is Nothing Then

Call Protect

End If
End Sub

You must protect the whole sheet, but those cells should be the only ones locked. Perhaps we have a misunderstanding
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
What exactly do you mean by protected? What do you want to be able to do in the locked and unlocked cells?
 

Forum statistics

Threads
1,147,632
Messages
5,742,233
Members
423,715
Latest member
Albert Kuni

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