Compile Data and Produce a Dashboard

[Luke M]

Wrap Text enabled, additional columns, unlocked. DefectProtect macro, which is called whenever sheet is activated, detects whether user is currently logged in and changes protection as appropriate. You didn't say, but I assumed that all users should still be able to use dropdowns/dashboard.

Let me know what you discover about the crashing issue.

 

[Mahendra S]

Thanks Luke, yes the assumption is correct for all users should still be able to use dropdowns/dashboard, any way users can not edit anything on dashboard which still remains same.

I will verify these scenario's later today and will let u know if any issues.

Regards,
M

 

[Mahendra S]

Hi Luke, I could chase the crashing problem and nailed down to some extent, but want to discuss is this the same err you are getting or

Crashing Issue Root Cause with today's template.
(please use yesterday template to re-pro crashing issue, as I have changed few things in new template attached)
Repro steps:
1. Run the Publish DefectSet as usual
2. Change the status of DefectId: RS-P02 from Open to Ready To Test and similarly for few other rows for RS, ETR, CRM sources status in DefectsPriority tab, and save the tab after changes as we are highlighting with yellow as per our req.(This is one example to repro)
3. Then run the DP/SourceSync to have the updates reflected back to source tabs.
4. Then run the Publish DefectSet again to have the latest data from source tabs.

Now this is crashing with following 1004 err with out error trace.



Source of the problem: I think I have included additional rows earlier after the table in RP Prod Defects tab
to make sure to copy rows working or not
These outside table rows are creating problem If I understnad, pl see the screenshot here



If you take out these rows 27 to 30 in RS Prod Defects tab you don't get above crashing.
I suspect this is the issue, let me know what you think.

Some times it is possible accidentally rows will be inserted after a table in a tab and above is one of the example.
However if u think we can not solve this problem, we can live with work around by not having any rows outside table or we can have rows only with out table in any source tabs.

Please let me know on this resolution, or if you have encountered the crashing in a different way.

Apart from above crashing problem, I want to verify with you few enhancements as below:

Enhancement1: I have added additional column in Super Admin tab WS Source in Publish table.
The purpose of this table is bring the Ws Source values accordingly in Master Data and Defects Priority tabs for Source Column Data values.

Also the Defects Priority Master Filer Select By Source values will get changed I believe with this enhancement
and Dashboard Source filter values as well.

This will give more granularity as we will now have unique WS Source, so that we know from where the data is exactly flowing from.

in the past we have same code for two types of record sets, now we can distinguish easily with this enhancement.

Please let me know if we can change this.

Requirement1: Now we are dependent on Ticket / Defect Id # unique value in our resync proc.
Need to make sure we have unique value check for all Ticket / Defect Id at some point and I think this would be before Copy rows.
In any tab if we have duplicate Ticket / Defect Id# we need to convert the duplicate value to the defect Id to be appended with Defect Id-02 or any other suggested way(this should happen if we have for example Defect Id-1 occurs two times) Please let me know on this.

Requirement2: Similar to above what if we have blank value for Ticket / Defect Id #, this will create an issue as we are dependent on unique Ticket / Defect Id. I think we dont have this check but remind me if we have this.

So in case if we have blank Ticket / Defect Id # in any tab then we need to run a macro to add in sequence with format "WS Source-NTN-01" (For example if we dont have ticket value in CRM Prod Defects for row4 then macro should add CRM-Prod-NTN-01)

Also a note I had moved the DP/Source Sync button to left hand side on Super Admin for your ref.

Please let me know your understanding on these enhancement and requirements to be on same page with implementation.
Appreciate your great support on building these feature set.

Regards,
M

 

[Mahendra S]

Just FYI, in CRM-Prod-NTN-01 (NTN stands for "No Ticket Number) and the sequence continues if we have next row dont have ticket number it would be CRM-Prod-NTN-02 and so on...

 

[Luke M]

Correct, that is the same error that I am seeing. The annoying thing is, that particular error, is saying that the SpecialCells method is failing. BUT, just two lines before that in the code, I wrote the On Error Resume Next. It is not doing that!! So, while we might find a work-around, it's frustrating to me that I don't know why it's doing this exactly.

 

[Mahendra S]

Thanks Luke, we could catch the crashing err, now will see for resolution, but pl try with out those outside table rows did it work for you.

How ever I want take one step back regarding the enhancement

Enhancement1: I have added additional column in Super Admin tab WS Source in Publish table.
The purpose of this table is bring the Ws Source values accordingly in Master Data and Defects Priority tabs for Source Column Data values.

Let us not change anything with current filters in DP and Dashboard and below statement I don't think we need to implement.

(Also the Defects Priority Master Filer Select By Source values will get changed I believe with this enhancement and Dashboard Source filter values as well.)

Let us just add new column(WS Source) after Source column in Master Data and Defects Priority tabs.
This will reduce the rework and we can just remain as-is with filters on DP and Dashboard.

Sorry for this late thought and please let me know your thoughts on this.
Want to reduce additional code and changes late at this moment with Dashboard.

Regards,
M

 

[Luke M]

It's actually easier to change the current Source column to be WS Source, then to add in a new column. I've already got that part figured out, so if you are okay with it, I'd prefer to go with changing column, rather than adding a column.

The ReSync error does seem to be gone, which is good.

 

[Mahendra S]

Thanks Luke, I am fine replacing the column with WS Source. Only caveat is what are the filter values in DP and Dashboard. Are those will get impacted with formulas we had.

 

[Mahendra S]

Can we keep current Dashboard Source filter as-is with out any change, if we are changing Source to WS Source in DP, not sure the dependency here.

 

[Mahendra S]

Sorry missed the attachment earlier, looking good at high level.
The issue comes here is in Dashboard Source Filter values.
This value needs to be original Source values (Example want to display unique values like Oracle, MS etc)

Because we always need Source at Primary level, but now we are displaying Secondary Source level
(Oracle-Prod, Oracle-QAT) this example.


If we can remain to original dashboard source values before enhancement, no issue.
To mitigate this either we need to open a new dashboard table and chart or some other way to represent
Source at Primary level.

Please let me know your thoughts on this.


Regards,
M

 

[Mahendra S]

Hi Luke, I think we should go with additional column route for DP and MD tabs, otherwise things will be more complicated at DaB(Dashboard source filter), please let me know on this.

Regards,
M

 

[Luke M]

Ok, I'll switch it back and proceed to add a column. Not sure yet on the unique/blank identifier. It might be better/easier to just assign every record a unique identifer, something like worksheetname + row number. That method would guarantee that every record is unique, and has the added benefit of letting me/macro know exactly where to go to find the record. What do you think?

 

[Mahendra S]

Thanks Luke, I support your idea to have unique record worksheetname + row number.

I have a question though, is this an additional column dynamically populated only MD and DP tabs or it will add in every source tabs.

We should not add any another additional column in source tabs, source tabs like original defect data flow from multi source, and te latest tabs will be replaced in to template as and when, which is the reason I may not add new column in source tabs (Oracle Prod Defects, Oracle QAT Defects etc..)

 

[Mahendra S]

Luke, I think the approach is ok even if u add worksheetname + row number in every source tab/ worksheet.
It doesn't alter anything and I just need to see how it comes through, let us see.

For time being I would take back the previous statement sorry for this.
(We should not add any another additional column in source tabs, source tabs like original defect data flow from multi source, and te latest tabs will be replaced in to template as and when, which is the reason I may not add new column in source tabs (Oracle Prod Defects, Oracle QAT Defects etc..)

This will be like trial and err attempt hopefully it works worksheetname + row number in every source tab


Regards,
M

 

[Luke M]

Okay, after a long bit, I think I've got it stable again, and it should be worth the effort.

1. Added WS Source column to both Master and DP sheets. These are populated used data from Super Admin.
2. After running Populate macro, source sheets will have a column fill with unique ID's. These are not needed after the macro in source sheets, so could be deleted if needed. I've put a line in the CopyRows macro, but currently commented out.
3. UniqueID column is in Master and DP sheets. This column could be hidden, if you want
4. Change made to DP sheet will add a small flag into UniqueID col. Reason for this in next line
5. ReSync macro has been greatly optimized. Having Unique ID lets us speed up having to find where to put data. Additionally, using flag from previous, we only update lines that are out of Sync. This further improves our speed.
6. Various changes in code, changing col letter references to Table structure references. Makes code more robust in case of column additions/deletions/moves.

 

[Mahendra S]

Thank you very much Luke for great work and quick turn around on the issues and enhancements.
I believe the template reached an important milestone to have resync functionality. now we need to make sure all features works.

Will verify to see how re-sync works later during weekend and if any issues will post.

Have a good weekend!

Best,
M

 

[Mahendra S]

Hi Luke, Hope you are doing good and few updates I have made to the template as below.

Enahncement1:Added the code to support enable/disable worksheet command bar controls which you can see in RibbonCalls.
This was one of the long time requirement pending which we discussed some time back and now it is implemented and working fine. Please take a look and let me know if you come across any issues also verify the code as part of your code review.

When you have disable ribbon in super admin you will not see the worksheet tab right click where as you will see when you click on enable ribbon.

This enhancement is done, please review and provide your feedback.

Enhanceemnt2: Also added a rounded rectangle in super admin(row2) to have good visual representation and brought all controls (button and option button on to this rectangle)

I want to discuss few issues here.

Issue1: I did notice the PublishDefectSet button is completely greyed out. Enhancement2 related rectangle created any issue but not sure. The basic rule when we implemented to grey out this button is only when all rows in Publish column displays "No". Is this button greying out based on any formula let me know. We should stict to original rule as defined above. If we have any Yes or multiple Yes for Publish column in Publish table then the PublishDefectSet button always enabled.

Issue2: In SuperAdmin, FilterTable we have Status and Priority columns.
However we are repeating Status and Priority (I3-J4) and (I7-J8) can we just merge these two tables in to one.
But we have code looking for <>Closed Critical and <>Closed High as first two rows and remaing icon 10 rows will be in same Filtertable with total of 12 records. This will help us to clean this table and we can avoid duplicate headers (Status and Priority). Please let me know on this.

Issue3: UpdateFilters (manual) the text of this embed control I would like to change to Update DB Filters. and also can we have this button as normal button / command button like we have for others ex DP/Source Sync.
Also the text of this button is bit cutting off at bottom as I want to keep the height of all button same. Please let me know on this adjustment.

Attaching the latest template for your reference.

Regards,
M

 

[Luke M]

Hello again M.

Enhancement1 looks good. Just out of curiosity, what is the "Ply" command bar? I'm not familiar with that one.

Enhancement2 looks nice, purely visual.

Re: Issue1
You are correct, there is a formula. Formula was in cell B1, and I had used a hard coded reference to cell B1. But, it looks like a row got inserted, shifting the cell, causing code to break. I've change this to used a Named reference, so the link won't get broken if we add/delete rows. Formula currently sits in cell B2.

Re: Issue2
No, we should not merge them. The top table is being used as CriteriaRange for Adv. Filter, merging would cause this to break. However, we have lots of flexibility with Table 2. If you click on the cell, in Table2, you'll notice it's not actually a XL Table object, but just some formatted cells. None of the macros read these cells, they are stricly a reference for user to know what the icons are. (Additionally, the icons have specific names, which the CopyShapes macro is looking for. This will be important to remember if you ever decide to change the icons.) Since the cells aren't truly a table, we can move them to be adjacent to the table above, giving the appearance of a single table. Let me know what you think of the look. The indication that table doesn't include shape rows is the little blue triangle in bottom of cell J5. If, for whatever reason, the criteria in FilterTable needs to expand, you would copy/shift the Icon rows down and add the filter rows.

Re: Issue3
Button has been deleted, replaced with Forms - Command Button. Text, font, size, and alignment have been adjusted. I also middle-aligned everything to try and polish up the look.
The Publish button is the only ActiveX button left, and has to remain ActiveX in order to keep ability to enable/disable.

Enhancement3
I've gone through the code and changed sheet references to use VB code structure reference, rather than sheet names for Admin, dashboard, DP, Master sheet. This gives more robustness in case those sheet names get changed by user.

 

[Mahendra S]

Hi Luke, Thanks for the update and the Super Admin worksheet is having great look and feel now.
also I am inline now with Issue2 justification you gave above.

I believe Ply control is the short cut menu (picture as shown below)that shows when you right click a worksheet or tab



I will verify the template later and let you know any issues.
also I might come up with a requirement which will be interesting to implement to have new button of "Insert WS / Tab" with identified header columns automatically. It has some tricky part but will assess and let you know.

Have a good day!
Regards,
M

 

[Mahendra S]

Hi Luke, Thanks for all the great work especially with robustness in the performance of macros and best development practices across the code and good quality standards with template GUI, I really appreciate the great implementation with good understanding.

I would like to discuss following and need your valuable input here.
Please see the enhanced Super Admin screen with new buttons 'Create Worksheet' and 'WS-H Sync' in the latest template.

Requirement1:
As soon user clicks on 'Create Worksheet' button it should pop usrFrmWorksheet and cursor should be in Worksheet Name text box in this user form. A validation need to happen as below.

By default Ok button will be greyed out (very similar to what we implemented for Super Admin Login credentials check)

Only difference here is the worksheet name entered by user need to be validated with following

1.The worksheet name should not be same as existing worksheet names.
2. At any point of time there shoud not be duplicate names especially 'Super Admin', 'Dashboard', 'DefectsPriority', 'MasterData' and 'Snapshot History'
(I think if we follow rule1 we dont need to follow rule2) please let me know on this.​

3. Upon validation of the worksheet name Ok should be enabled and as soon as Ok click a new worksheet need to be created with fourteen column names identified as in table10 in superadmin (please change this table name to some appropriate name like tblWSHeaders)
4. The newly created worksheet should have 14 columns as identified above and should have in a empty table with 50 rows with alternate color(white/blue) combination empty rows.
5. The name of the worksheet created should reflect in the tabs at bottom and also the same name should be updated in Publish table (WS Name) in Super Admin as last row as soon as the new worksheet is created.
6. It would be nice if we control columns width while creating the worksheet but this we can tackle later..
​

We can tweak this to handle more things later but first if we can implement this setup which would be awesome! please let me know your thoughts on above req.

Requirement2: I have a new button WS-H Sync as soon as this button clicked we need a macro to run and make sure all the worksheets except ('Super Admin', 'Dashboard', 'DefectsPriority', 'MasterData' and 'Snapshot History') should have same column names for consistency with column names identified in table10. if any column name is incorrect in any worksheet it should just update with correct column name.

Please let me know on this requirement. This is one of the valuable requirement to have all source sheets to have same columns.

Requirement3: Currently the uniqueid is populating in source worksheets and I have noticed in some worksheets we dont have any column name if we dont have a table in worksheet. should we just give a column name like WSRowId or something suitable and also this column need to have same format rules like center aligned and all borders to have good visual representation.

Requirement4: In continuation to the reverse sync macro we had, as soon the text of a status, assigned team, assigned owner, eta to fix, progress any changes in DP , we are synching back to source tabs. also can we highlight the WSRowId cell of the source tab with what ever color we have identified in superadmin(currently we have yellow in cell Q8) so that we know exactly which row changed in DP and synched with source.

Please let me know if we are on same page for above req.

Thank you very much,

Best,
M

 

[Luke M]

Re: Requirement1
While enabling the "ok" button worked well when entering a password combination, I don't think it would be the best idea here. First, we would again have to be running a few lines of code after every character that user types, checking list of worksheets. Second, as soon as user types a single character, button would be enabled, as we don't have any single letter worksheets. In short, the number of valid answers is almost infinitely greater than invalid.
In attached, I used a simple InputBox with a loop to check the names. I think it still accomplishes the goal, w/o being too code heavy.

Also, rather than building a new worksheet and having to construct table/format/etc., I propose we have a "template" worksheet. I've set this up already using an XL table. Then code can just copy the template sheet and rename it. Template is set to "veryHidden", so you need to use VB to unhide it, can't just right-click. This may be over secure than what you need, let me know if so. To unhide, go to the VBE, select wsTemplate, go to properties menu (hit F4), and change the Visible setting. Or, run this line:
wsTemplate.Visible = True
Since we're using a template, gives us much easier control over color, name, column width, etc.

Anyway, let me know what you think about it. I think I got it running pretty smooth with current setup.

Re: Requirement2
New macro written, in SyncChecks module. Goes through all the sheets (including template) and updates the columns' headers. One note of caution, this doesn't check if columns get reordered, but I don't think user can mess that up.

Re: Requirement3
Done, sheets have been formatted. Template setup with correct format to catch any future changes.

Re: Requirement4
Done, code line added to ReSync macro.

Misc.
Due to so many tabs, I added some color to the worksheet tabs, to try and help them stand out.

 

[Mahendra S]

Hi Luke, Thanks for quick update, at high level regrading req1, actually I have created an user control form (usrFrmWorksheet) like below, this form doesn't have any pwd validation to make sure we are on same page.



Can we go with this route rather with inputbox, please let me know, inputbox if you dont enter a name and say ok it might crash..
I think we should not verify each character, all we need verification with tab names from WS Name column from Publish table.

I will also take a look at template approach ...

Regards,
M

 

[Luke M]

Hi M,

I've got a built-in check if User leaves box blank (it's same result if user hits cancel or "X"), so that's not a problem.
To explain further what I mean by each character, process would look like this. Let's say user wants to input a name of "Standard"

Box starts blank, Ok is disabled.
User types first letter, "S"
Immediately, the change event macro gets fired, checks list of worksheet names, sees that no sheet is already named "S", so Ok is enabled.
User types next letter, making "St"
Immediately, the change event macro gets fired, checks list of worksheet names, sees that no sheet is already named "St", so Ok is enabled.
User types next letter, making "Sta"
Immediately, the change event macro gets fired, checks list of worksheet names, sees that no sheet is already named "Sta", so Ok is enabled.
....
User types last letter, making "Standard"
Immediately, the change event macro gets fired, checks list of worksheet names, sees that no sheet is already named "Standard", so Ok is enabled.

That's a lot of code going on, and doesn't actually give you the desired goal, since 'ok' gets enabled immediately. From a code perspective, there's no way of knowing when a user has finished typing the name, so we have to check at every change. Hope that's clearer.

 

[Mahendra S]

Hi Luke, agree with this and I think template approach is good, let us see how it works.
wondering as soon as we give name in input box it should create using template.


is this correct, pl let me know.

Regards,
M

 

[Luke M]

Correct, as soon as you hit Ok, code will copy the Template and create a new worksheet.