Compile Data and Produce a Dashboard

[Mahendra S]

Hi Luke, Thanks for the update on Issue1 and Req1.

Regarding Req1 your assumption is correct to not have any filters in DP which is good.
only caveat I see is having three labels (
Select By Env: Select By Source: Select By Team
which doesnt mean any since this is only xlsx.

Also can we get rid of merged cells and bring "Total number of not closed critical and high defects text and dynamic formula value in the center of the row, please let me know.

Regards
M

 

[Mahendra S]

Yes the DP should be protected. forgot this

 

[Mahendra S]

also realized the xlsx file do not have any merged cells in row2 which is good. however still need to think of those un relevant filter labels...

 

[Luke M]

Labels removed in this version, and as you noted, no merged cells.
New DP is protected.
What do you want to define as the middle of the row for the "Total number of not closed critical and high defects" formula? For now I've just done the simplest which is to slide it over to where the dropdowns were.

 

[Mahendra S]

Thanks Luke, this looks good.
I'm thinking about can we just transfer the "Total number of not closed critical and high defects" next to Critical and High Priority Defects View to show as below in rounded rectangle

Critical and High Priority Defects View: "Total number of not closed critical and high defects <dynamic value>

Please let me know on this, if this doable then we can eliminate the second row itself.
Also in Template DP if we can make same change it will be consistent.
However in template the filters remain stay as-is, may be later we can tweak with much easier design.

 

[Luke M]

I like the idea. In the template, I moved the linked cells and formula up to row 1, hidden behind the header bar, and the header bar is now linked to the formula. I think this looks pretty nice now.

Then, in the new DP, we just delete row 2.

 

[Mahendra S]

Luke, thanks again for making this header adjustment at both template and xlsx which is really nice.

I think we are still seeing issues with range when you attempt to to do Publish.
The err 1004 is coming with range as below:



This is happening if you have reduced Publish set (Publish = Yes) for few, pl see the latest template
I have only Yes for QVR & ATR.
We need to make sure Publish never fails especially in any combination like select all (Yes) & and any permutation and combination (like alternative rows with Yes) or even single row with Yes, two rows with Yes from Publish.

Please let me know your thoughts on this.
I hope header adjustment is not the root cause here, but to know at root level what is causing this failure.
attaching the template for ref.

Regards

 

[Luke M]

Hmm. We never counted on there being no open Critical/High records in MS, so nothing got copied to DP. code was trying to shrink the table to 0 rows (not possible). I've added a small If check after the AdvFilter, to see if there's at least 1 record. If not, display a short msg to user and abort trying to change DP table.

 

[Mahendra S]

Thanks Luke, the condition check to see zero records is good to mitigate this known err.

one small issue I did'nt realize earlier is in SA tab we are displaying Data Refresh in cellU, can we display
Data Refresh & Publish at the corner of right hand side of the header to be consistent.

Also when we import the worksheet tabs the WSRowId column is not getting with table format with borders,
ex as we see in RN Prod Defects.
I believe we are running WS-H sync after import and also to make sure table format aligns along with table names,
in above case is this an exception, please clarify.

 

[Luke M]

Re: timestamp display, the answer is yes and no. We have to have the formula stored in a cell somewhere, but I can hide it if necessary. Perhaps in the same columns as passwords, since that column already gets hidden.

You are right, I was not previously re-sizing the tables to include last row. The WS-H macro now resizes to have # of columns equal to what is specificed in SA Header list.

 

[Mahendra S]

Luke, I think the approach is perfect with timestamp table and consistent.
I will verify with WS-H & Publish later and if I come across any other issue we can discuss tomorrow.

Thank you,
Have a nice day!

 

[Mahendra S]

Hi Luke, I appreciate all the great work and template works perfect.

Issue1: I have modified the headers in SA, DB, MD to have a smaller rounded rectangle with thin border and made the timestamp font bold. Please take a look with modified template. However when I try to place this small round rectangle in DP and pdfreport tabs they are getting deleted I believe..

Please let me know if we can have this tiny rectangle across same including on SA, DB, MD, DP and pdfrep headers.
May be we can call this tiny rectangle as HeaderTimestampBar, let me know your thoughts on this.

Enhancement1: Can we have "Total Defect Count <Dynamic value from snapshothistory-Total Snapshot Records>" in MD tab header next to Master Defect View as below

Master Defect View: "Total Defect Count <Dynamic value from snapshothistory-Total Snapshot Records>"

Enhancement2: Can we generate the excel defect report with Disable ribbon features, basically all tabs need to be protected and plycontrols (right click of tabs not to show) as is what we have in template.
I believe this we can call the same proc/code before generating the xlsx.
Please let me know on this need.

Regards

 

[Luke M]

Re: Issue1
Rather than having the Time Stamp box and another shape floating on top of it, I made them a single shape and it seems to work now.

Re: Enhancement1
Similar to TimeStamp, I've hidden a formula on MD in cell B1 to calculate the number. HeaderBar is linked to this cell

Re: Enhancement2
I don't think this is an option as you described. We can protect all the sheets and workbook structure. However, things like the Ribbon and Right-click menu are actively controlled through Macros. Since we are saving the file as an xlsx, and more importantly, the new file doesn't have the macros stored in it, we can't use these same macros. I would need to learn how to have VB write macros in itself, which would also mean work on user's end as they need to give permission to macros to write in VBE (you can imagine how macros that can write their own code and change could be used for ill-intent, and thus a security concern).
I've change the export to at least do a simple protection on each sheet, and then protect the workbook structure. We can even add a password to this, if desired (note that I am not implying password makes it "secure", just harder).

 

[Mahendra S]

Thanks Luke, I think simple protection is fine as related to enhancement2, let us leave it as -is.
Yes I am in line as xlsx would be macro free otherwise to imply with ply controls is different path.

Will further evaluate this design and come back.

Thanks again!

 

[Mahendra S]

Hi Luke, hope you had a good weekend and doing good.

Want discuss on following design related to enhancement2
Is it possible to populate WS names which are intended to export in a dynamic form / pre defined user form
as soon as click on export button.

Basically If I have 15 worksheets target for export, as user i should be able to choose which ones required to protected and which ones not. This number 15 is just an example, how ever it can be variable(out of 15 I may want 7 as protected and 8 un protected) or all of them either protected or un protected before export.

Ideal user experience would be to populate ws names with checkboxes and provide flexibility to user to choose.
I understand you have mentioned implementation with check box logic complex when we attempted on Publish elect All.

How ever this is critical to choose which ws need to be protected before export and deliver as xlsx.

i have not prepared a user form on this as I thought I will verify with you first.
Please let me know on this dynamic enhancement, if we implement it will be amazing.

Regards

 

[Luke M]

A few ideas/thoughts, in order that I came up with them?

1. Since new WB will have Ribbon enabled, user can fairly easily protect/unprotect the sheets themselves if desired
2. If we go with a default option, should default be protected, or unprotected?
3. I could have a short pop-up appear for each ws, as the code goes through it's loop. It would ask something like "Protect worksheets: <sheet name>? Yes/No
   User clicks response, and then next pop-up appears for next sheet
4. See attached, used a ListBox in a user form. You can highlight the sheets you want protected. Runs fairly smooth.

 

[Mahendra S]

Thanks Luke for the update here.

Regarding

#2 by default all should be Un Protected except DP.
#3 would be more tedious as we should avoid asking each time, but I dont see this in latest draft which is good, please confirm.

#4 Yes, you can highlight the sheets you want protected.

Need to validate further on this and get back

 

[Luke M]

Sorry last post was a bit confusing, wrote it as I was brainstorming ideas, and realised by #4 that it wouldn't be quite as hard as initially thought. We are on the same page with #2. I will abandon #3 idea, I didn't like it too much.
#4 is what's in the attached. Obviously, we can adjust font/colors/layout if you want.

 

[Mahendra S]

Hi Luke, Thanks for the update and appreciate the good work regarding#4.

I would like to see some feasibility on below enhancements
Enhancemennt1: Is it possible can we implement similar as#4 for Import also. some times we may have additional tabs which really don't need, as-is after importing we are deleting, instead if admin has choice to highlight / probably by default highlight all and ask for confirmation to Import, we can have Accept&Import and Cancel button with list box to have all worksheet names. Click on Accept&Import would bring in to template.

Please let me know on this enhancement which will consistency with export feature.

Enhancement2: Similar to above when we have same wsnames already imported and if admin tries to import again we are asking for overwrite each time, instead can we populate like in Enhancement1 and confirm rather asking a message each time for each worksheet, please let me know.

Issue1: I am getting excel crash when I try to import same tabs and save the template it is giving below err
Wondering is this due to some overwrite on readonly or some thing else not sure.



I will also further investigate on this.
latest template has some ui changes like button name changed to Accept&Export.

attaching the zip file including latest template and sample file for Import in case if u need for above enhancements.

Regards

 

[Luke M]

Re: Enhancement1 and 2
New form created for the importing, looks similar to Export. Sheets that would cause an overwrite are marked with asterisk. Importing new sheets seems to work nice and smooth.
Caution: I too am getting major problems (code not working correctly) after importing and overwriting. Will investigate as well, but wanted to give you an update on the enhancement.
Due to this caution, currently have default of NO sheets selected. We can change this later, but am erroring on side of caution for now.

 

[Mahendra S]

Thanks for the Update Luke, looks good with Import, need to verify few scenarios with this design and get back.
and please let me know if you see further any root cause with crash.

 

[Luke M]

After testing several imports, the problem with saving would occur after deleting a worksheet. I wasn't able to find anything definitive online, but did find a suggestion of making sure deleted object is active. So, more out of a hunch than definitive reason, I added a line to make sure the Summary workbook is active, rather than the workbook being imported. This seems to have eliminate the error occuring, even doing a full overwrite.

Moving past that, ideas for selecting sheets to import:

• Currently, the ListBox is set to multi-single clicks. Meaning, you just click on each object to toggle it, but can't select a group. I could change this to Multi-group select, meaning it will act similar to selecting cell(s) in XL. Regular clicking deactivates previous "selection", but you can use Ctrl and/or Shift to do multiple selections.
• As I mentioned, default now is to not import unless selected. I could swap this around...maybe even make it so that item is highlighted unless it would be a re-write, forcing user to confirm the re-write?

 

[Mahendra S]

Hi Luke, good attempt on excel hang and crash issue certainly it looks like deal with tab deletion a root cause and thanks for validation on this critical issue, will keep monitor on this issue, hopefully we will not come across, let us see if any intermittent failures !

And the * indicator in front of worksheet names in list is very good idea.
How ever few minor tweaks we need to make sure following

Issue1: By default Accept&Import needs to be greyed out till a list item selected by user.
This would be the same case for Accept&Export.

Issue2: I still see when you import or overwrite 'RN Prod Defects' worksheet the WSRowId col o still looks like outside table and with out borders


I believe this was fixed earlier but not sure what caused this issue and this out of sync structure is back,
please let me know on this.

Enhancement1: A small change in the Import UI, I have added the checkbox select all /un select all
Basically when user selects checkbox all the list items needs to be high lighted and un select the select all it should not highlight the list items(worksheet names).

Hopefully this is possible to implement and if we can that would be awesome!
Once if above enhancement is implementable same logic and design applies to Export.

Note we also need to keep current logic, if user wants to select individually each item that is still valid scenario.
Please let me know on above, attaching the template for ref.

Regards

 

[Luke M]

Re: Issue1
Change made to the button being enabled. With the export, I changed the caption on "Cancel" to "Cancel/None", to hopefully clarify to user that clicking that button doesn't Cancel the export, but rather just signifies that no source sheets should be protected.

Re: Issue2
I learned something new on this. If the table was the result of a query, it won't let me change/resize the table, but it won't throw an error. I kept trying and trying, and everything looked good, but it wouldn't change. I only saw the clue when I tried to convert the table back to a range, and XL warned me about losing query definition.
To solve this, I re-ordered the sync code so that changing table size is the last step, after headers copied and links broken. This seems to have resolved the issue.

Re: Enhancement1
Select All feature has been setup.

Note:
I notice that you put some frame borders around buttons on the User Forms. I'm guessing this was just for cosmetics, rather than functionality? There seems to also be some strange issue with buttons changing size. Note sure if these two items are related, but let me know what your testing turns up.

 

[Mahendra S]

Thanks for the update Luke, looks awesome and very interesting to know about query result impact with Issue2.
Framework is cosmetic for now however the button size seems an issue, I just gave a height of 23.25 may be this is ok for now.

regarding Enhancement1, looking really good with select all feature set, I have added the similar select all in Export as well, please let me know on the feasibility of select all feature implementation for export.
Thanks for great support!