Results 1 to 9 of 9

Thread: Table on protect sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular stefankemp's Avatar
    Join Date
    Mar 2010
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Table on protect sheet

    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?

  2. #2
    New Member
    Join Date
    Aug 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table on protect sheet

    I have the same issue, any suggestion?

  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    388
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table on protect sheet

    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?

  4. #4
    New Member
    Join Date
    Aug 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table on protect sheet

    Quote Originally Posted by EdNerd View Post
    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

  5. #5
    Board Regular
    Join Date
    May 2011
    Posts
    388
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table on protect sheet

    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:
    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

  6. #6
    New Member
    Join Date
    Nov 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table on protect sheet

    Quote Originally Posted by stefankemp View Post
    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:
    Code:
    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:

    Code:
    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:
    Code:
    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?
    Last edited by RoryA; Sep 9th, 2019 at 11:30 AM.

  7. #7
    Board Regular stefankemp's Avatar
    Join Date
    Mar 2010
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table on protect sheet

    Quote Originally Posted by tferkel View Post
    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.
    Stefan Kemp
    Excel Developers

  8. #8
    New Member
    Join Date
    Oct 2013
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table on protect sheet

    Quote Originally Posted by stefankemp View Post
    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. . .

  9. #9
    New Member
    Join Date
    Aug 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table on protect sheet

    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?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •