Solved Command to lock a row and save sheet

December 4, 2015 at 01:58:56
Specs: Windows 7
I wish to incorporate a command button onto worksheet which will lock a complete row if there is data in it and save the sheet. I am sure this will be easy for experienced VBA coders but I just cant get my head round it

See More: Command to lock a row and save sheet

Report •


#1
December 4, 2015 at 04:11:05
You can use something like this, you will need to select all cells first, right click, format cells, Protection tab and unlock all cells.

Then when you click on a cell, say A7 or whatever, the code will determine which row you are on, check if there are any empty cells, and lock and password protect the sheet. You must protect the sheet for the locled cells to be uneditable

Private Sub CommandButton21_Click()
    
    
    If Application.CountA(ActiveCell.EntireRow) <> 0 Then
        
        ActiveCell.EntireRow.Locked = True
        ActiveSheet.Protect Password:="test"
        
    End If

    
End Sub


Report •

#2
December 4, 2015 at 06:16:46
Hi thanks for reply
Still struggling Im afraid. Perhaps if I could hide the row instead of locking it that would be easier? All I want to do is ensure that the data input into spreadsheet cannot be changed once a button (or checkbox /whatever) has been pressed.
Hope this is clearer
Cheers

Report •

#3
December 4, 2015 at 07:09:35
✔ Best Answer
Hi,

No problem, what exactly happens, or doesnt happen rather, with the current solution? I think it will be better to lock and protect the sheet, rather than to hide a row.

if you want to just hide the row you can use the following code:

Private Sub CommandButton21_Click()
    
    
    If Application.CountA(ActiveCell.EntireRow) <> 0 Then
        
        ActiveCell.EntireRow.Hidden = True
        
    End If

    
End Sub

Just for clarification, before you run my first code, you must unlock of cells, and the way to do that follow the following instructions:

1) select all cells by either clicking on the square to the left of 'A' and above the '1' or hold Ctrl and press A

2) right click any of the cells on the sheet and select 'Format cell'

3) from the window that opens select the 'Protection' tab

4) ensure you untick the checkbox called 'Locked'

5) select for example cell 'B10' (only only cell is required) then press the command button or run the code, here is an updated version.. You can run this by doing the following

Paste the code into VBE

Click tools
Click Macros
double click on 'Sheet1.ProtectRow'


Public ProtectRow()
    
    
    If Application.CountA(ActiveCell.EntireRow) <> 0 Then
        
        ActiveCell.EntireRow.Locked = True
        ActiveSheet.Protect Password:="test"
        
    End If

    
End Sub

Just make sure that you only select ONE cell rather than the entire row when you run either of the codes.....

If all else fails, PM me your email address and i will send you the workbook with the working code.

AWTL


Report •

Related Solutions

#4
December 4, 2015 at 07:39:19
Brilliant
The hide thing works really well. Thanks for that!!

Report •

Ask Question