Table on protect sheet

[stefankemp]

I have a table on a sheet with all the cells in the databodyrange unlocked and the cells in the row immediately below the table also unlocked. All other cells are locked and the sheet is protected. I need the table to expand to the next row when a user enters data in the row immediately below the table. However it appears that the sheet protection prevents this from happening. Any suggestions?

 

[201208Steph]

I have the same issue, any suggestion?

 

[EdNerd]

I don't think you can have a table on a protected sheet. The only possibility might be (and I have not tried this) to use a Sheet_Change event to redefine your table range.

Ed

PS - Just musing here - again, I haven't tried this. But I wonder if you could keep your sheet protected so users can't overwrite formulas and such, but have you table on another sheet hidden and unprotected that draws in the info from the main sheet?

 

[201208Steph]

I don't think you can have a table on a protected sheet. The only possibility might be (and I have not tried this) to use a Sheet_Change event to redefine your table range.

Ed

PS - Just musing here - again, I haven't tried this. But I wonder if you could keep your sheet protected so users can't overwrite formulas and such, but have you table on another sheet hidden and unprotected that draws in the info from the main sheet?

Hi Ed,

The table is mainly text data with a lot of dependent lists to other spreadsheet. I have define a name for the table but how do i link it into the Sheet_change according to your suggestion? i am happy to try!

Steph

 

[EdNerd]

You would have two worksheets:
-- the Active sheet, in which your data is entered and contains your dependant lists
-- the Shadow sheet, which has a formatted values-only (no formulas) shadow of all your data in the Active sheet

If you compared the two sheets, you would see exactly the same thing. But the Shadow sheet does not contain any of the formulas, dependant list drop-downs, etc that the Active sheet does.

Select your range on the Shadow sheet and create the table.

In the code module for the Active sheet, insert this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wksAct As Worksheet   'Active sheet for data entry
Dim wksShd As Worksheet   'Shadow sheet, containing a copy of the data
Dim rwAct As Long         'row being used on Active sheet
Dim rwShd As Long         'row to write in on Shadow sheet
Dim x As Long             'generic counter
Dim varXLcalc             'variable to hold XL calculation state
'Set calculation to Manual to save lots of time
Set varXLcalc = Application.Calculation
Application.Calculation = xlCalculationManual
'Set sheet objects
Set wksAct = ActiveSheet
Set wksShd = ActiveWorkbook.Worksheets("Sheet2")
'Get row numbers
rwAct = Target.Row
rwShd = wksShd.Range("A100000").End(xlUp).Row + 1
'Write data
For x = 1 To 7  'I just went to 7 columns
  wksShd.Cells(rwShd, x).Value = wksAct.Cells(rwAct, x).Value
Next x
'Reset XL calculation state
Application.Calculation = varXLcalc
End Sub

Now, as you write into the Active sheet, your data values are written into your Shadow sheet, and your table in the Shadow sheet will automatically expand. Because of that, you can use that table and the data in it for charts, calculations, etc. It's the same data, but you get to have it in a table.

If you hide the Shadow sheet, you don't (usually) have to worry about protecting it. But you can still protect your Active sheet against fat-fingered data entry overwriting your formulas and such.

That may not work for you, but it's one possible solution.

Ed

 

[tferkel]

I have a table on a sheet with all the cells in the databodyrange unlocked and the cells in the row immediately below the table also unlocked. All other cells are locked and the sheet is protected. I need the table to expand to the next row when a user enters data in the row immediately below the table. However it appears that the sheet protection prevents this from happening. Any suggestions?

All, I realize this thread is a little old but thought I would reply to it since it comes up on top in Google. I think I have solved this issue with a very simple albeit brute force solution leveraging the sheet events. Here are the code snippets:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' This routine unprotects the worksheet if the user selects any cell in the row immediately below the table
'
' Declare variables
Dim rng As Range
Dim rngMonitor As Range
Dim ilastrow As Integer

' Determine the first open row below the table
ilastrow = LastRowInColumn("A")
ilastrow = ilastrow + 1

' Check to see if the user selected the row immediately below the table
Set rngMonitor = Intersect(Target, Rows(ilastrow))
If Not rngMonitor Is Nothing Then
For Each rng In rngMonitor.Cells

' Unprotect the sheet
Application.Run "Unprotectsheet", "Resources"

Next

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
' This routine protects the sheet after data has been added to the row immediately following the table 

' Declare variables
Dim rng As Range
Dim rngMonitor As Range
Dim ilastrow As Integer

' Determine the first open row below the table
ilastrow = LastRowInColumn("A")
ilastrow = ilastrow + 1

' Check to see if the user selected the row immediately below the table
Set rngMonitor = Intersect(Target, Rows(ilastrow))
If Not rngMonitor Is Nothing Then
For Each rng In rngMonitor.Cells

' Unprotect the sheet
Application.Run "Protectsheet", "Resources"

Next

End If 
End Sub

The lastrowincolumn routine simply finds the bottom of the table as below:

Function LastRowInColumn(icol As String) As Long
'
' This function returns the number of rows on a sheet by counting cells in a particular column
'
' Initialize function
LastRowInColumn = 0

' Calculate the number of rows
With ActiveSheet
LastRowInColumn = .Cells(.Rows.Count, icol).End(xlUp).Row
End With

End Function

The unprotectsheet routine simply unprotects a specific sheet name as below:

Private Sub UnProtectSheet(isheet As String)
'
' This macro unprotects the worksheet that was passed to it
With Worksheets(isheet)

.Unprotect Password:="PASSWORD"

End With
End Sub

With this logic the assumption is the user doesn't start entering data in the table in column A. Of course you could change this depending on your specific situation.

I have tested this with my own application and it seems to work beautifully. The table expands fine and the protected cells remain in tack. Can anyone see a flaw in what I am doing?

 

[stefankemp]

All, I realize this thread is a little old but thought I would reply to it since it comes up on top in Google. I think I have solved this issue with a very simple albeit brute force solution leveraging the sheet events. Here are the code snippets:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' This routine unprotects the worksheet if the user selects any cell in the row immediately below the table
'
' Declare variables
Dim rng As Range
Dim rngMonitor As Range
Dim ilastrow As Integer

' Determine the first open row below the table
ilastrow = LastRowInColumn("A")
ilastrow = ilastrow + 1

' Check to see if the user selected the row immediately below the table
Set rngMonitor = Intersect(Target, Rows(ilastrow))
If Not rngMonitor Is Nothing Then
For Each rng In rngMonitor.Cells

' Unprotect the sheet
Application.Run "Unprotectsheet", "Resources"

Next

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
' This routine protects the sheet after data has been added to the row immediately following the table

' Declare variables
Dim rng As Range
Dim rngMonitor As Range
Dim ilastrow As Integer

' Determine the first open row below the table
ilastrow = LastRowInColumn("A")
ilastrow = ilastrow + 1

' Check to see if the user selected the row immediately below the table
Set rngMonitor = Intersect(Target, Rows(ilastrow))
If Not rngMonitor Is Nothing Then
For Each rng In rngMonitor.Cells

' Unprotect the sheet
Application.Run "Protectsheet", "Resources"

Next

End If
End Sub

The lastrowincolumn routine simply finds the bottom of the table as below:

Function LastRowInColumn(icol As String) As Long
'
' This function returns the number of rows on a sheet by counting cells in a particular column
'
' Initialize function
LastRowInColumn = 0

' Calculate the number of rows
With ActiveSheet
LastRowInColumn = .Cells(.Rows.Count, icol).End(xlUp).Row
End With

End Function

The unprotectsheet routine simply unprotects a specific sheet name as below:
Private Sub UnProtectSheet(isheet As String)
'
' This macro unprotects the worksheet that was passed to it
With Worksheets(isheet)

.Unprotect Password:="PASSWORD"

End With
End Sub

With this logic the assumption is the user doesn't start entering data in the table in column A. Of course you could change this depending on your specific situation.

I have tested this with my own application and it seems to work beautifully. The table expands fine and the protected cells remain in tack. Can anyone see a flaw in what I am doing?

The sheet will become unprotected whenever a cell in the row below the table is selected. There is no guarantee that the sheet will be re-protected after that.

 

[cool0masty2]

The sheet will become unprotected whenever a cell in the row below the table is selected. There is no guarantee that the sheet will be re-protected after that.

So, did U find any solution ?
I prefer sheets without VBA...
Still If VBA is My last hope I can give it a try . . .
but VBA should not Unprotect My sheet in any case. . .

 

[Jon Mulkey]

Hi guys, I am a real dummy with VBA but apparently I need some code to accomplish what I need to do. It seems much more simple than what you guys are talking about here and i am only getting more confused. I have a spreadsheet that I want the user to be able to add new rows to a table while it appears to be constantly protected. I need to hide the formulas in the cells so they can't accidentally edit them. If I understand this correctly a macro can be coded so when the user is ready to add a new "entry" (line in the table) there is a way to program a button with a macro. However everything I have tried has come up with errors. I really need help with this. I think I need code that stops protecting the sheet long enough to insert a new row and the protect it again. The user would "click" the button each time they want to make a new entry. But I am at a total loss on how to write that code. Can anyone help me out with this?