• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there a code to allow me block the sheet to only enter data but not "delete"

Fred Wayne

Member
Hello, I am making a User Login/Logout workbook. I want that "Sheet1" to have the capacity to enter data but not be able to delete rows or columns. Is there a code to do this? I am using a VBA Code to set the Login and Logout time. But if I protect the sheet, the Login/Logout code does not work. How can I make that the sheet records the information but block it to avoid the data being deleted?

Code:
Private Sub Workbook_Open()
Dim uFila As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    uFila = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
   
    ws.Cells(uFila, 1) = "Login"
    ws.Cells(uFila, 2) = Application.UserName
    ws.Cells(uFila, 3) = Date & " " & Time
   
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

End Sub




Thank you in advance.

I am sending the file I just started.
 

Attachments

  • Test 1.xlsm
    23.6 KB · Views: 2
Last edited by a moderator:
Code:
Private Sub Workbook_Deactivate()
Call ResetDeleteRowCols
End Sub

Private Sub Workbook_Open()

Dim uFila As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    uFila = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    ws.Cells(uFila, 1) = "Login"
    ws.Cells(uFila, 2) = Application.UserName
    ws.Cells(uFila, 3) = Date & " " & Time

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Call StopDeleteRowCols
End Sub
add below macros to your worbook
Code:
Sub StopDeleteRowCols()
' https://support.microsoft.com/en-us/help/213552/list-of-id-numbers-for-built-in-commandbar-controls-in-excel-2000

    For Each ctrl In Application.CommandBars.FindControls(ID:=293) 'delete - from row
        ctrl.Enabled = False
    Next ctrl

    For Each ctrl In Application.CommandBars.FindControls(ID:=294) 'delete - from column
        ctrl.Enabled = False
    Next ctrl

    For Each ctrl In Application.CommandBars.FindControls(ID:=3183) 'insert - from rows& columna
        ctrl.Enabled = False
    Next ctrl
    
    For Each ctrl In Application.CommandBars.FindControls(ID:=292) 'delete - from cell
        ctrl.Enabled = False
    Next ctrl
    
    For Each ctrl In Application.CommandBars.FindControls(ID:=3181) 'insert - from cell
        ctrl.Enabled = False
    Next ctrl
    
End Sub

Sub ResetDeleteRowCols()
' https://support.microsoft.com/en-us/help/213552/list-of-id-numbers-for-built-in-commandbar-controls-in-excel-2000

    For Each ctrl In Application.CommandBars.FindControls(ID:=293) 'delete - from row
        ctrl.Enabled = True
    Next ctrl

    For Each ctrl In Application.CommandBars.FindControls(ID:=294) 'delete - from column
        ctrl.Enabled = True
    Next ctrl

    For Each ctrl In Application.CommandBars.FindControls(ID:=3183) 'insert - from rows& columna
        ctrl.Enabled = True
    Next ctrl
    
    For Each ctrl In Application.CommandBars.FindControls(ID:=292) 'delete - from cell
        ctrl.Enabled = True
    Next ctrl
    
    For Each ctrl In Application.CommandBars.FindControls(ID:=3181) 'insert - from cell
        ctrl.Enabled = True
    Next ctrl
End Sub
 
Back
Top