Share via

Insert and delete rows on protected worksheet

Anonymous
2024-04-05T09:10:08+00:00

Hi,

i have the following VBA Code on my USER FORM Level in my Excel Dashboard. As you see from the code it filters Pivots and Tables from several sheets based on inserted credentials in the UserForm that appears everytime the file is opened. Once the tables are filtered the users can interact wtih the Data related to their credentials and cannot interact or filter unfilter data not related to the entered credentials.

In the "Data" sheet of my workbook once the table is filtered based on the entered credentials, i allow users to add or delete rows to the table althoug sheet protection as applied. And it works fine. I want to do the same for my "Table13" on my "Financials&Performance" Sheet, for which i added the command into the VBA Code, however i cannot get it to work.

Please help me. What should i do. Re-write my code so this las detail wil work. My current VBA Code is:#If VBA7 Then 

Private Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr 

Private Declare PtrSafe Function SetWindowLongA Lib "user32" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr 

Private Declare PtrSafe Function GetWindowLongA Lib "user32" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr 

Private Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hWnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal uFlags As Long) As LongPtr

#Else 

Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long 

Private Declare Function SetWindowLongA Lib "user32" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long 

Private Declare Function GetWindowLongA Lib "user32" (ByVal hWnd As Long, ByVal nIndex As Long) As Long 

Private Declare Function SetWindowPos Lib "user32" (ByVal hWnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal uFlags As Long) As Long

#End If

Private Const GWL_STYLE = -16

Private Const WS_CAPTION = &HC00000

Private Const SWP_NOMOVE = &H2

Private Const SWP_NOSIZE = &H1

Private Const HWND_TOP = 0

Private Sub RemoveTitleBar() 

Dim hWnd As LongPtr 

hWnd = FindWindowA(vbNullString, Me.Caption) 

If hWnd <> 0 Then 

    Call SetWindowLongA(hWnd, GWL\_STYLE, GetWindowLongA(hWnd, GWL\_STYLE) And (Not WS\_CAPTION)) 

    Call SetWindowPos(hWnd, HWND\_TOP, 0, 0, 0, 0, SWP\_NOMOVE Or SWP\_NOSIZE) 

End If

End Sub

Private Sub Workbook_Open() 

' Unprotect both sheets upon opening the workbook 

Dim ws As Worksheet 

Dim PivotSheet As Worksheet 

Dim FinancialSheet As Worksheet 

Set ws = ThisWorkbook.Worksheets("Data") 

Set PivotSheet = ThisWorkbook.Worksheets("Pivot") 

Set FinancialSheet = ThisWorkbook.Worksheets("Financials&Performance") 

ws.Unprotect password:="" 

PivotSheet.Unprotect password:="" 

FinancialSheet.Unprotect password:="" 

' Refresh all connections and PivotTables 

ThisWorkbook.RefreshAll

End Sub

Private Sub UserForm_Activate() 

' Remove the title bar from the UserForm 

RemoveTitleBar

End Sub

Private Sub OKButton_Click() 

Dim UserPlant As String 

Dim UserPassword As String ' Added password field 

Dim ws As Worksheet 

Dim PivotSheet As Worksheet 

Dim FinancialSheet As Worksheet 

Dim PivotTable As PivotTable 

Dim PivotField As PivotField 

Dim FilterRange As Range 

Dim FilterCriteria As String 

' Get the entered Plant and Password from the form 

UserPlant = Trim(TextBoxPlant.Value) ' Remove leading/trailing spaces 

UserPassword = Trim(TextBoxPassword.Value) ' Remove leading/trailing spaces 

' Reference the "Data" worksheet 

Set ws = ThisWorkbook.Worksheets("Data") 

' Reference the "Pivot" worksheet 

Set PivotSheet = ThisWorkbook.Worksheets("Pivot") 

' Reference the "Financials&Performance" worksheet 

Set FinancialSheet = ThisWorkbook.Worksheets("Financials&Performance") 

' Check if the entered Password is valid for the corresponding Plant 

Select Case UserPlant 

    Case "HSS" 

        If UserPassword <> "111" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "HAN" 

        If UserPassword <> "222" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "HJL" 

        If UserPassword <> "333" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "HSKK" 

        If UserPassword <> "444" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "HSKB" 

        If UserPassword <> "555" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "HSKT" 

        If UserPassword <> "101" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "PL5" 

        If UserPassword <> "667" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "PL7" 

        If UserPassword <> "777" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "HCL" 

        If UserPassword <> "123" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "MAESA" 

        If UserPassword <> "345" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "W2" 

        If UserPassword <> "999" Then 

            MsgBox "Invalid Password for Plant " & UserPlant & ".", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case "GLOBAL" ' Managerial credentials 

        If UserPassword <> "mgmt" Then ' Managerial password 

            MsgBox "Invalid Managerial Password.", vbExclamation, "Invalid Input" 

            Exit Sub 

        End If 

    Case Else 

        MsgBox "Invalid Plant. Please enter a valid Plant.", vbExclamation, "Invalid Input" 

        Exit Sub 

End Select 

' Unprotect both sheets temporarily to apply the filter and refresh PivotTable 

ws.Unprotect password:="GLGLOBAL" 

PivotSheet.Unprotect password:="GLGLOBAL" 

FinancialSheet.Unprotect password:="GLGLOBAL" 

If UserPlant <> "GLOBAL" Then ' Check if the entered Plant is not "GLOBAL" 

    ' Valid Plant, define the filter range for the Pivot table 

    On Error Resume Next 

    Set PivotTable = PivotSheet.PivotTables("PivotTable1") 

    On Error GoTo 0 

    If PivotTable Is Nothing Then 

        MsgBox "Pivot table not found on the 'Pivot' sheet.", vbExclamation, "Error" 

        ' Protect both the "Data" and "Pivot" sheets 

        ws.Protect password:="GLGLOBAL" 

        PivotSheet.Protect password:="GLGLOBAL" 

        FinancialSheet.Protect password:="GLGLOBAL" 

        Exit Sub 

    End If 

    ' Refresh the PivotTable 

    PivotTable.RefreshTable 

    ' Reference the PivotField to filter (assuming the field name is "GL Plant") 

    Set PivotField = PivotTable.PivotFields("[Table1].[GL Plant].[GL Plant]") 

    ' Clear any previous filters in the Pivot field 

    PivotField.ClearAllFilters 

    ' Apply a filter to the Pivot field for an exact match 

    FilterCriteria = UserPlant 

    PivotField.PivotFilters.Add Type:=xlCaptionEquals, Value1:=FilterCriteria 

    ' Reference the filter range in the "Data" worksheet 

    On Error Resume Next 

    Set FilterRange = ws.ListObjects("Table1").ListColumns("GL Plant").DataBodyRange 

    On Error GoTo 0 

    If FilterRange Is Nothing Then 

        MsgBox "Table or column not found.", vbExclamation, "Error" 

        ' Protect both the "Data" and "Pivot" sheets 

        ws.Protect password:="GLGLOBAL" 

        PivotSheet.Protect password:="GLGLOBAL" 

        FinancialSheet.Protect password:="GLGLOBAL" 

        Exit Sub 

    End If 

    ' Clear any previous filters if FilterRange is not Nothing 

    FilterRange.AutoFilter Field:=1 

    ' Filter the "GL Plant" column for an exact match 

    FilterCriteria = "=" & UserPlant 

    FilterRange.AutoFilter Field:=1, Criteria1:=FilterCriteria 

    ' Hide the "Password" column (assuming it's the second column in "Table1") 

    ws.ListObjects("Table1").ListColumns("Password").DataBodyRange.EntireColumn.Hidden = True 

    ' Disable the AutoFilter functionality to prevent further filtering 

    ws.EnableAutoFilter = False 

    **' Allow users to insert and delete rows in the "Data" sheet** 

    **ws.Protect password:="GLGLOBAL", UserInterfaceOnly:=True, AllowInsertingRows:=True, AllowDeletingRows:=True** 

     ****         

    ' Protect both the "Pivot" sheet with the same password to prevent further modifications and allow manual removal with password "GLGLOBAL" 

    PivotSheet.Protect password:="GLGLOBAL", UserInterfaceOnly:=True 

    ' Define the filter range for "Table13" on the "Financials&Performance" sheet 

    On Error Resume Next 

    Set FilterRange = FinancialSheet.ListObjects("Table13").DataBodyRange 

    On Error GoTo 0 

    If FilterRange Is Nothing Then 

        MsgBox "Table not found on 'Financials&Performance' sheet.", vbExclamation, "Error" 

        ' Protect the "Financials&Performance" sheet 

        FinancialSheet.Protect password:="GLGLOBAL" 

        Exit Sub 

    End If 

    ' **Allow users to insert and delete rows in the "Financials&Performance" sheet** 

    **FinancialSheet.Unprotect password:="GLGLOBAL"** 

    **FinancialSheet.Protect password:="GLGLOBAL", UserInterfaceOnly:=True, AllowInsertingRows:=True, AllowDeletingRows:=True** 

    ' Filter the "GL Plant" column for an exact match in "Table13" 

    FilterCriteria = "=" & UserPlant 

    FilterRange.AutoFilter Field:=1, Criteria1:=FilterCriteria 

    ' Protect the "Financials&Performance" sheet 

    FinancialSheet.Protect password:="GLGLOBAL", UserInterfaceOnly:=True 

End If 

' Unload the UserForm 

Unload Me

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 

' Disable the close button (X) on the UserForm until valid credentials are entered 

If CloseMode = vbFormControlMenu Then 

    Cancel = True 

End If

End Sub

Microsoft 365 and Office | Excel | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-04-08T16:22:49+00:00

    I have tried, but your code is beyond my ability. You may create a new thread on Stack Overflow. Hope engineers there can help you on it.

    Thank you for your understanding.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-04-08T06:16:43+00:00

    Hi,

    here is the link to the file: https://docs.google.com/spreadsheets/d/1Y\_YalhHt91pDzWo1VnY7zc2jZT5jA37R/edit?usp=sharing&ouid=108215393630061386340&rtpof=true&sd=true

    Please help. This is the last step towards completing this Dashboard, which is much much needed.

    Kindly confirm you can access the file through the link.

    Thanks.

    Br,

    Daniel

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-04-05T15:52:46+00:00

    You can refer to this article: Office VBA support and feedback | Microsoft Docs to create a new thread on Stack Overflow which is special channel to handle VBA questions.

    Thank you for your understanding.

    Could you share us a test file to debug your code? Then I will have a try.

    For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

    Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

    Was this answer helpful?

    0 comments