Excel Forum

Creates usable slicers and add rows with data validation on a protected sheet

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

June 1, 2022 - 1:03 am

I'm struggling to write elegant code that:

Creates usable slicers on a protected sheet
Allows the user to enter data using Data Validation drop-lists on a protected sheet

I've looked at several online forums and code samples, but can't seem to get this right. Please see attached.

I have the following sheets:

Enter Velocity: This sheet has a table (TABLE_VELOCITY) where the user enters info about Scrum team velocity
ReportVelocity: Visualizes the velocity data
Enter Business Value: This sheet has a table (TABLE_BV) where the user enters info about the business value they delivered
ReportPPM: Visualizes the business value data
Setup ARTs: User can create ARTs (which are like folders that contain Teams), and can indicate whether a particular ART is active or not (by using the Include in Droplists field)
Setup Teams: User can create Teams (which are like sheets in an ART folder), and can indicate whether a particular team is active or not (by using the Include in Droplists field)
Setup Timeboxes: User can create PIs (which are like folders that contain sprints) and Sprints (which are like sheets in a PI folder). They can then indicate whether a particular sprint (within a PI) is active or not (by using the Include in Droplists field)
Droplists: This sheet removes blanks, removes duplicates, and alphabetizes the ARTs, Teams, and PIs created on other sheets. The tables on this sheet are also named ranges used for Data Validation

The sheets have the following buttons:

Enter Velocity and Enter Business Value sheets:
1. Button 1: Reset UI
2. Button 2: Add new row
ReportVelocity and ReportPPM sheets
1. Button 1: Reset UI

The Reset UI buttons (on all sheets) is supposed to do the following:

Unprotect the sheet using the password found in the Password Token named value (create the password if not found)
Delete all slicers on the sheet
Create a new set of usable slicers (one of the slicers is optional and controlled by a parameter)
Protect the sheet using the same password as above

The Add New button on the data entry sheets is supposed to do the following:

Add a row to the bottom the sheet
Navigate to the new row
All the user to enter data, including with Data Validation drop-down lists

The FOUR problems are:

SLICERS: The routine that creates the slicers triggers a 1004 error. I obviously need the slicers operational (unlocked) and to allow filtering
ADD ROW: When a new row is added to a table, the drop-lists do not work.
PROTECTION: Protecting the sheet has weird results. Sometimes the Unprotect option on the sheet tab is grayed out until I click another tab and then click back. Sometimes the sheet unprotects without prompting for the password.
DATA VALIDATION: The Droplists sheet is supposed to contain the drop-list values for Data Validation. I cannot figure out the formula for column N TEAMS SORTED ACTIVE). This should show the teams that are member of active ARTs and that are themselves not marked as inactive, sorted alphabetically and excluding blanks

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

June 3, 2022 - 8:28 pm

These should not be in sheet modules, they refer to a form, but Me syntax represents the worksheet then the code is in a sheet module:

Me.cbFilterSort.Top = 5
Me.cbFilterSort.Left = 350

Depending on context, Me expression can be: a form, ThisWorkbook, a worksheet.

LockSheet:

When you need to unprotect, you just pass the password, it does not need arguments like the Protect method:

ThisWorkbook.Worksheets(iSheetIndex).Unprotect Password:=strReturnValue

When you need to protect, do it in 1 line:

Sheets(iSheetIndex).Protect Password:=strReturnValue, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowUsingPivotTables:=True

NOT in 2 lines, it makes no sense:

Sheets(iSheetIndex).Protect Password:=strReturnValue

Sheets(iSheetIndex).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowUsingPivotTables:=True

Should be:

' Protect or unprotect the sheet
If bSwitch Then
ThisWorkbook.Worksheets(iSheetIndex).Protect Password:=strReturnValue, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowUsingPivotTables:=True
Else
ThisWorkbook.Worksheets(iSheetIndex).Unprotect Password:=strReturnValue
End If

4. Data validation:

Why using formulas?
Collect the data you need in dictionary objects for use in code. Or you are not going to use those tables in code, just in sheet lists?
You might find it easier to build a small pivot table to get the sorted and filtered list of teams, you can simply refresh it in code when needed.

Answers Post

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

June 3, 2022 - 10:20 pm

Buttons:

You advised that these lines should NOT be in sheet modules, but the buttons to which they refer are on the sheet, not on a form. For now, I replaced "me" with "ActiveSheet." Please advise.

Me.cbFilterSort.Top = 5 --- > ActiveSheet.cbFilterSort.Top = 5
Me.cbFilterSort.Left = 350 --> ActiveSheet.cbFilterSort.Left = 350

LockSheet:

Thank you. I did not realize there was a difference when you used the underscore. I use that a lot for readability.

Data Validation

I am trying to use formulas because I only use this data in Data Validation. Here's what I've been trying to do:

Create a sheet (e.g., "Manage Inventory") where the user will enter values into a table (e.g., "TABLE_AVAILABLE_ITEMS").
There is no limit to the number of rows in the table
Data can be entered in any sequence
Duplicates are possible (although I may try to prevent dupes using the Worksheet_Change event and "Undo")
The user can mark any item as "UNAVAILABLE"
Create a named range (e.g., "RANGE_AVAILABLE_ITEMS") so I can refer to the table in Data Validation formulas
Create a dynamic second named range (e.g., "RANGE_DROPLIST_SOURCE") comprised of non-duplicate, non-blank, alphabetized values from the RANGE_AVAILABLE_ITEMS range where "UNAVAILABLE" items are excluded.
If no data matches this criteria, then the list is just empty
Create a second sheet (e.g., "Purchase Items") where the user can select which items they want to purchase.
The choice of items is presented as a Drop List using Data Validation based on a "List" where the "Source" is: "RANGE_DROPLIST_SOURCE" (this should show, of course, an alphabetized list of available items with no blanks or dupes).

I will try your idea of using a pivot table for this purpose, but I'm not sure how to remove duplicates in that scenario.

As always, thank you.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

June 3, 2022 - 10:48 pm

By default, the pivot table will create a unique list of items from that column, you don't have to remove duplicates..

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

June 3, 2022 - 11:01 pm

Thanks.

I created a pivot table on a sheet called "Droplists." The first row of data is in $AB$7. So it's "Droplists!$AB$7". The data in the pivot table is filtered to exclude unavailable data and it looks exactly correct.

Now I'm trying to refer to that data in a named range. So I created a range called "DL_ARTS" (this is essentially like the "Available Items" list in my example above).

The formula that defines this range is:

=IFERROR(OFFSET(Droplists!$AB$7,,,COUNTA(Droplists!$AB$7:$Y$999),1),Droplists!$AB$7)

In a blank cell on the Droplists sheet I added Data Validation so I can see what the drop list will look like. The Data Validation is based on a "List" and the "Source" of the list is the named range called "DL_ARTS."

When I try to save the Data Validation settings, Excel shows a warning that "The Source currently evaluates to an error."

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

June 3, 2022 - 11:06 pm

I'm revising this reply to correct an error. I thought the problem went away with time and magic, but it didn't. So I now have four pivot tables (ART, TEAM, PI, and SPRINT) and four named ranges that refer to those tables, as follows:

RANGES:

DL_ARTS = =IFERROR(OFFSET(Droplists!$AC$8,,,COUNTA(Droplists!$AC$8:$AC$999),1),Droplists!$AC$8)
DL_TEAMS = IFERROR(OFFSET(Droplists!$AF$8,,,COUNTA(Droplists!$AF$8:$AF$999),1),Droplists!$AF$8)
DL_INCREMENTS = =IFERROR(OFFSET(Droplists!$AI$8,,,COUNTA(Droplists!$AI$8:$AI$999),1),Droplists!$AI$8)
DL_SPRINTS =IFERROR(OFFSET(Droplists!$AL$8,,,COUNTA(Droplists!$AL$8:$AL$999),1),Droplists!$AL$8)

The data validation for AC4, AF4, AI4, and AL4 are all "List" with "Source" set as follows:

DATA VALIDATION:

For ARTs: List = DL_ARTS
For Teams: List = DL_TEAMS
For Increments: List = DL_INCREMENTS
For Sprints: List - DL_Sprints

When I create Data Validation based on any of these ranges, I get the error: "The Source currently evaluates to an error." I assume that's a #REF error, but why?

I updated my example XLS which shows the same problem.