Creating a Table
 

I have a multi sheet workbook that I set up so the majority of cells are
protected. I have some cells that I unlocked forvariable data entry. I would
like to build a table that shows on a navigation page all the cells that are
unlocked. Is there some way I can create this table?

Mark

You coukd try this code

Sub test()
Dim ws As Worksheet, ws1 As Worksheet
Dim r As Range, c As Range
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.Sheets("Summary").Delete
Set ws1 = ThisWorkbook.Sheets.Add
ws1.Name = "Summary"
For Each ws In Worksheets
If Not ws.Name = "Summary" Then
ws1.Columns("A:A").Insert Shift:=xlToRight
ws1.Range("A1") = ws.Name
With ws
Set r = .UsedRange
For Each c In r
If c.Locked = False Then _
ws1.Range("A65000").End(xlUp).Offset(1, 0) = c.Address
Next c
End With
End If
Next ws
ws1.Cells.Replace What:="$", Replacement:="", LookAt:=xlPart
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

--
XL2002
Regards

William



"Mark F" wrote in message
...
| I have a multi sheet workbook that I set up so the majority of cells are
| protected. I have some cells that I unlocked forvariable data entry. I
would
| like to build a table that shows on a navigation page all the cells that
are
| unlocked. Is there some way I can create this table?

Mark

You could try this code

Sub test()
Dim ws As Worksheet, ws1 As Worksheet
Dim r As Range, c As Range
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.Sheets("Summary").Delete
Set ws1 = ThisWorkbook.Sheets.Add
ws1.Name = "Summary"
For Each ws In Worksheets
If Not ws.Name = "Summary" Then
ws1.Columns("A:A").Insert Shift:=xlToRight
ws1.Range("A1") = ws.Name
With ws
Set r = .UsedRange
For Each c In r
If c.Locked = False Then _
ws1.Range("A65000").End(xlUp).Offset(1, 0) = c.Address
Next c
End With
End If
Next ws
ws1.Cells.Replace What:="$", Replacement:="", LookAt:=xlPart
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub


--
XL2002
Regards

William



"Mark F" wrote in message
...
| I have a multi sheet workbook that I set up so the majority of cells are
| protected. I have some cells that I unlocked forvariable data entry. I
would
| like to build a table that shows on a navigation page all the cells that
are
| unlocked. Is there some way I can create this table?