Cell Locking after data input

dave_br · Sep 16, 2011

Right guys,

is it possible to lock a cell once data has been inputted into it??
i want to make a spreadsheet which has empty cells for reference numbers that once entered cannot be changed unless it is by the administrator.
i also want the spreadsheet to be accessed on multiple pc's by multiple users.

any help is appreciated.

thanks
dave

rdw72777 · Sep 16, 2011

How will administrator access be determined?

Have you thought of the imapct of typos on making this file painful to work with? I worry your desired solution might create more trouble than its worth....

dave_br · Sep 16, 2011

i was going to lock the majority of the sheet with a password, only leaving certain cells to be filled in.

i know a typo would create an issue but this could be resolved by the 'owner' of the workbook. also there is only 6 numbers in the reference number so it shouldnt be too difficult to get wrong!!!

i want to use the sheet for a delivery schedule and when the day is full then another day can be used. currently people have to ring a central point, check availability, run the delivery through and finally ring back with the reference number. this just simplifys the process.

dave

rdw72777 · Sep 16, 2011

Ok, so I'm thinking in this direction. Maybe the entire worksheet is set to a natural state of always being protected. Upon opening, a macro could determine the windows ID and determine if the user has access to edit all cells, just your 1 column, or no access whatsoever (this list could be maintained in the code or a hidden tab). Upon closing the file, it would revert back to protecting the worksheet in question completely.

What are your thoughts? Is it over complicated by finding out the username? You'll have to know all of your team's window's usernames (but not passwords) in order for this to work.

dave_br · Sep 16, 2011

rather than have to write a code with everyones usernames (i can get this but seems a bit long winded) could i not write a code that runs each time excel closes which saves the document and locks all cells that are populated? therefore if there are blank cells they are free to be populated by another user and if they are populated then they will be locked to most users but can be unlocked by admin?
dave

rdw72777 · Sep 16, 2011

Hmm, I've never tried that, but should have the desired functionality. I assume you'll just password protect the sheet/file and only the Admin will have the password?

I'll give this a try. I've never done it this way trying to find all non-blank cells, should be intriguing.

rdw72777 · Sep 16, 2011

Hi Dave,

I gave it a shot, i think ti does what you ask. you have to paste this in the "ThisWorkbook module to get it to work. BTW, note that I set it up to work whenever the file is saved, so test it on a test workbook.

You might have to modifiy it, I have it setup to look at a worksheet called "Sheet10" and I also coount the columns assuming you have column headers in row1. Also I have the password set as "ryan" for the worksheet protection, so you'll need to change that. Beside that, it should be plug-n-play

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim RowCount As Long, ColumnCount As Long
Dim i As Long, j As Long

'Note I'm sure there is a way to track changes and only update cell locking on cells that
'have been changed, but I dont know how to do that off the top of my head.  This code unlocks
'all cells and then re-locks based on cell value.  Depending on file size it might take a
'split-second or take a few extra seconds

    With Sheets("Sheet10")
        
        'Unprotect Sheet to perform actions
        .Unprotect Password:="ryan"
        
        'Unlock all cells
        .Cells.Locked = False
        
        'Determine number of columns in this tab
        'Column count is based on the column furthest to the right with cell populated in row 1
        ColumnCount = .Cells(1, Columns.Count).End(xlToLeft).Column
        
        'Loop through the rows of data in each column and lock cells that are not blank
        For i = 1 To ColumnCount
            
            'Count rows in a given column
            RowCount = .Cells(Rows.Count, i).End(xlUp).Row
            
            For j = 1 To RowCount
            
                If Cells(j, i) <> "" Then
            
                    .Cells(j, i).Locked = True
                
                End If
            
            Next j
        
        Next i
        
        'Re-protect the worksheet tab
        .Protect Password:="ryan"
        
        'Control-Home
        .Range("a1").Select

    End With

End Sub

dave_br · Sep 25, 2011

im struggling with the code at the moment and have now included a copy of the data i am trying to protect. the majority of the sheet is locked and users currently can only fill in the white cells with a reference number, but these can be amended by any user.

<TABLE style="WIDTH: 507pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=681><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" span=17 width=35><TBODY><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=86></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-TOP: windowtext 0.5pt solid" class=xl64 width=35>17</TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 30.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=41>Monday</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 30.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=41>Tuesday</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8" class=xl67></TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 507pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=681><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" span=17 width=35><TBODY><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=86> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>17</TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 30.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=41>Monday</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 30.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=41>Tuesday</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD></TR></TBODY></TABLE>

every day has a different number of 'slots' for the refence number bu i do know the ranges so can include that in a code.

thanks
dave

rdw72777 · Sep 26, 2011

Dave,

I sent you a PM. When you send me the file, hopefully I can get a better handle on what exactly you are dealing with.

Can you tell me what happens with the gray-shaded cells (are they locked, usable, etc)? Also, is the cell color now the driving force for the protection?

rdw72777 · Sep 26, 2011

Dave

I came up with the updated code below. It assumes you don't have merged cells (that was causing the .cells(j,i) error). I couldn't get it to work in the file you sent me, but if I re-created your file it worked. it seems to not like something on that tab you sent me that is causing it not to lock the white cells once populated within the grid, not sure what the deal is there.

Give this a try, if the white cells still don't lock when you save, let me know and I will look into deeper.

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim RowCount As Long, ColumnCount As Long
Dim i As Long, j As Long

'Note I'm sure there is a way to track changes and only update cell locking on cells that have been
'changed, but I dont know how to do that off the top of my head.  This code unlocks all cells and
'then re-locks based on cell value.  Depending on file size it might take a split-second or take a
'few extra seconds

    With Sheets("Sheet1")
        
        'Unprotect Sheet to perform actions
        .Unprotect Password:="ryan"
                
        'Unlock all cells
        .Cells.Locked = False
        
        'Determine number of columns in this tab
        'Column count is based on the column furthest to the right with cell populated in row 1
        ColumnCount = 30 '.Cells(1, Columns.Count).End(xlToLeft).Column
        
        'Loop through the rows of data in each column and lock cells that are not blank
        For i = 1 To ColumnCount
            
            'Count rows in a given column
            RowCount = .Cells(Rows.Count, i).End(xlUp).Row
            
            For j = 1 To RowCount
                                   
                'Lock populated or gray cells
                If .Cells(j, i).Value <> "" Or .Cells(j, i).Interior.Color = RGB(165, 165, 165) Then
                    .Cells(j, i).Locked = True
                End If
            
            Next j
        
        Next i
        
        'Re-protect the worksheet tab
        .Protect Password:="ryan"
        .Protect contents = True
        
        'Control-Home
        .Range("a1").Select
        
    End With

End Sub

Cell Locking after data input

dave_br

Board Regular

Excel Facts

rdw72777

Well-known Member

dave_br

Board Regular

rdw72777

Well-known Member

dave_br

Board Regular

rdw72777

Well-known Member

rdw72777

Well-known Member

dave_br

Board Regular

rdw72777

Well-known Member

rdw72777

Well-known Member

Similar threads

Forum statistics

Share this page

Cell Locking after data input

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock