Hi everyone...
I need help with this macro i found here...
This macro in my case is working only 50% because only protects cells with values...how can i do to protect empty cells in the same row?
i tried
`If wcell.Value = "" Then wcell.Locked = True`
But protects all my range
Macro:
Option Explicit
Public wr As Range, wcell As Range, fr As Range, ws As Worksheet
' this code goes at ThisWorkbook module <--------
Private Sub Workbook_Activate()
Initial
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set wr = ws.Range("a1:w" & LastRow(Me.Name, "Enter Data Here"))
For Each wcell In wr
Select Case wcell.Interior.ColorIndex
Case xlNone
' no action
Case Else
wcell.Interior.ColorIndex = xlNone
If wcell.Value <> "" Then wcell.Locked = True ' no more changes
End Select
Next
End Sub
Private Sub Workbook_Open()
Initial
End Sub
Sub Initial()
Set ws = ThisWorkbook.Worksheets("Enter Data Here")
ws.Protect Password:="pw", userinterfaceonly:=True
Set wr = ws.Range("a1:w" & LastRow(Me.Name, "Enter Data Here"))
wr.Interior.ColorIndex = xlNone
For Each wcell In wr
wcell.Locked = False
If wcell.Value <> "" Then wcell.Locked = True
Next
End Sub
Public Function LastRow(wname$, which$) As Long
Workbooks(wname).Sheets(which).Activate
If WorksheetFunction.CountA(Cells) = 0 Then
LastRow = 0
Exit Function
End If
LastRow = Cells.Find(What:="*", After:=[a1], SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function
Thank you all
I need help with this macro i found here...
This macro in my case is working only 50% because only protects cells with values...how can i do to protect empty cells in the same row?
i tried
`If wcell.Value = "" Then wcell.Locked = True`
But protects all my range
Macro:
Option Explicit
Public wr As Range, wcell As Range, fr As Range, ws As Worksheet
' this code goes at ThisWorkbook module <--------
Private Sub Workbook_Activate()
Initial
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set wr = ws.Range("a1:w" & LastRow(Me.Name, "Enter Data Here"))
For Each wcell In wr
Select Case wcell.Interior.ColorIndex
Case xlNone
' no action
Case Else
wcell.Interior.ColorIndex = xlNone
If wcell.Value <> "" Then wcell.Locked = True ' no more changes
End Select
Next
End Sub
Private Sub Workbook_Open()
Initial
End Sub
Sub Initial()
Set ws = ThisWorkbook.Worksheets("Enter Data Here")
ws.Protect Password:="pw", userinterfaceonly:=True
Set wr = ws.Range("a1:w" & LastRow(Me.Name, "Enter Data Here"))
wr.Interior.ColorIndex = xlNone
For Each wcell In wr
wcell.Locked = False
If wcell.Value <> "" Then wcell.Locked = True
Next
End Sub
Public Function LastRow(wname$, which$) As Long
Workbooks(wname).Sheets(which).Activate
If WorksheetFunction.CountA(Cells) = 0 Then
LastRow = 0
Exit Function
End If
LastRow = Cells.Find(What:="*", After:=[a1], SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function
Thank you all