How to disable slicer from being clicked after it's filtered once by login password?

ger0g3n

New Member
Joined
Feb 1, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hey guys,

I've got a database of workshops and created a userform for login purpose.
Once the workshop successfully login to the workbook, they can see 2 worksheets with data necessary for them to track their KPI results.
There are multiple slicers on both worksheets, but one is crucial and it's called "workshops". There is also one hidden worksheet called "admin" with columns in respective order:
workshoplogin / workshopassword / workshops
workshoploginworkshopasswordworkshops in slicers
Dim as serwis_logDim as serwis_passDim as serwis_slicer

So:
After the workshopAAA sucessfully logins to the workbook all the data in the workbook is automatically filtered to the data related to this workshopAAA and it's perfect.
Problem is:
I don't know how to disable "workshop slicers" from being "clicked". I dont want workshopAAA to see data of the workshopBBB.
WorkshopAAA can't see other Workshops data/results/charts. But other slicers must be unlocked so WorkshopAAA can see how they perform in different product categories.

I tried with worksheet protection but after the workshopAAA logins to the workshop I get an error that pivottable can't be modified when worksheet is protected.

Much thanks for help!

VBA Code:
Private Sub login_BT_Click()


If serwisy_CB.Value = "" Then
MsgBox "Należy wybrać serwis", vbInformation, "Serwis"
Exit Sub
End If

If password_TB.Value = "" Then
MsgBox "Należy podać hasło", vbInformation, "Hasło"
Exit Sub
End If

If serwisy_CB.Value = "Admin" And password_TB.Value = "admin" Then
Unload Me

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Next Ws
Sheets("Admin").Select
Else

Dim serwis_log As String
Dim serwis_pass As Variant
Dim serwis_slicer As Variant

serwis_log = serwisy_CB.Value
serwis_pass = Application.WorksheetFunction.VLookup(serwis_log, Sheets("Admin").Range("A:C"), 2, 0)
serwis_slicer = Application.WorksheetFunction.VLookup(serwis_log, Sheets("Admin").Range("A:C"), 3, 0)
If serwis_pass <> password_TB.Value Then
MsgBox "Hasło nie pasuje!", vbInformation, "Błędne hasło"
Exit Sub
End If

If serwis_pass = password_TB.Value Then
Unload Me
Sheets("Dashboard").Visible = True
Sheets("Dashboard2").Visible = True

Sheets("Dashboard").Select
Sheets("Dashboard").Activate
        
With ActiveWorkbook.SlicerCaches("Slicer_Workshop")
    .ClearManualFilter
    For Each oSlicerItem In .SlicerItems
        If oSlicerItem.Name = serwis_slicer Then
            oSlicerItem.Selected = True
        Else
            oSlicerItem.Selected = False
        End If
    Next oSlicerItem
End With

With ActiveWorkbook.SlicerCaches("Slicer_Workshop11")
    .ClearManualFilter
    For Each oSlicerItem In .SlicerItems
        If oSlicerItem.Name = serwis_slicer Then
            oSlicerItem.Selected = True
        Else
            oSlicerItem.Selected = False
        End If
    Next oSlicerItem
End With

End If
End If

End Sub
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
I've never used vba on slicers, so there maybe a posh way of doing this.
In your code add a line that writes a character to a cell that isn't visible usually on first set up
then wrap that slicer change code with an IF statement that checks for the character, and if it exists then set a Goto, which oversteps the slicer being operated. Of course macros have to be enabled for that
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top