Compile Data and Produce a Dashboard

[Luke M]

M,

I am not able to duplicate the error with last uploaded book. Have tried publishing just QVR Prod, as well as a group. Can you outline specific steps that cause error?

 

[Luke M]

Search output box split into 2 outputs, now have first line in red. Font size reduced and regular font.

 

[Mahendra S]

Thanks Luke, not able to re pro the previous run time err some how, wish I could have recorded let us see, will run more tests with publish and see if we can catch any trace.

just code review perspective do we have covered all error handling. please let me know.

I will also take a look but we can follow our rule of thumb to have err handler across

Regards

 

[Mahendra S]

Have a good weekend Luke! and we can discuss some calendar functionality next week which you will like it...

 

[Mahendra S]

Hi Luke, I hope you had good weekend. A change in Search interface and functionality as below,

Enhancement1: can we have search function operates based on input in text field(remain same as-is) and user need to click Search button(this is a change). Then remaining information need to be shown as we have earlier.
Also would like to avoid the Search color code in SA tab.
Please let me know on this modification of search interface and functionality.

Attaching the updated template with change in search interface..

Regards,
M

 

[Luke M]

Morning M,

Curious, your last request seems to be more of a downgrade than an upgrade. Current setup provides real-time feedback to search. Proposed setup requires user to type, click search, and then go back to typing if value not found. Can you explain reasoning for change?

Deleting the Search cells is simple enough. As I mentioned earlier, can delete whichever search function we don't want to use. The Search cell is just a regular formula, which we can delete.

Attached workbook has search cell removed.

 

[Mahendra S]

Good Morning Luke, thanks for quick update,
with current setup if you enter first two letters of defect it is giving last saved results or when you close the template and start a fresh search window, still gives previous results which is not the expected...

Correct functionality and correct output results takes priority than functionality downgrade or upgrade which is the driving factor here.

Please let me know if you can adjust the immediate search with the interface design I gave.
if we can not make with search interface that I gave which is fine we can live with.

Regards

 

[Luke M]

We could change it, but it would function in a similar manner. Rather than having the textbox_change event trigger the call, it would be the button. If there's a problem in the output, we will need to isolate the issue. Could you do some screenshots showing what error/problem you are seeing? In my tests, every time I open the search dialogue it starts with a clean slate.

If you can show me what the problem is with results retrieval, we'll prevent the same issue from appearing with the delayed results type user form.

 

[Mahendra S]

I think the button event is preferable in this case, the issue I am seeing is as soon as you type first two letters like below, showing some results, this can be true how ever we should display the search results based on button event only.



Please let me know on above issue also I have arranged the interface bit differently (removed the Search For label and Please enter text more as frame name itself, if we can adjust to the interface that would be great)

 

[Luke M]

Still seems backwards, but as I said, easy enough to move the code to be called by the button rather than the change event.

Might also consider using just a regular cell and formula instead of the UserForm then, since real time results are not needed. User inputs entry into a cell, hit 'Enter', see result. Just an after-thought.

 

[Mahendra S]

Thanks Luke, the issue still exists, not sure is this issue related to button event or something else,the result should have shown no record found in below case, where I entered only PB, but instead it returns a value which is incorrect



please let me know on this issue.

 

[Luke M]

Ah! If you want to just search only on whole word value, rather than partial match, we will need to adjust the Find. This is what I was trying to figure out at the beginning of today when you said it wasn't working. Didn't need to make any changes to the form, just need to tell the Find method to do a whole match, not partial.

 

[Mahendra S]

Cool, Thanks much Luke, this helps!

 

[Mahendra S]

Hi Luke, I have following enhancement, please let me know your thoughts.

Enhancement1: Can we have Microsoft Progress bar control added probably below status table after Postponed row, in sa tab, this progress bar need to start run when Publish starts (as is we have default circle comes during publish run, instead can we have progress bar control starts progress dots from left to right till the publish completes)

only change is just added a progress bar in new draft in sa.

Regards,
M

 

[Luke M]

I couldn't find the progress bar that you mentioned. I did add in some updates to the Status bar to display messages letting user know progress of macro. Let me know what you think.

If we try to to get complicated with a status bar/image, it could backfire, since any code we have to run to update a status bar will add time to how long it takes to run publish.

 

[Mahendra S]

Thanks Luke, the status bar updates are good, however not sure bit puzzle how to display the progressbar at run time.
infact the above mentioned progressbar is still in sa as it is not getting shown correctly. If u go in design mode u can see as below


it would be nice if we can implement this control please let me know otherwise we can leave it ...

 

[Mahendra S]

Luke, I know the progress bar implementation is quite tricky, can we attempt on separate pop up and have this progress bar and label, basically the messages showing in status bar need to displayed on the label below progress bar which would be on new popup window. this option I am considering if we can not make progress bar run on sa itself.

let me know your creative thoughts please!

 

[Luke M]

If we're going to do something other than the status bar, a User Form would be preferred. To update an object in a sheet, we'd have to turn ScreenUpdating back on, which has the negatives of increasing time significantly plus causing possible screen flashing to user.
Can't get that exact appearance of a progress bar in a UserForm, but I can make a rectangle grow in width that looks like a traditional progress bar.

You can change the messages/update amounts by looking through the CopyRows macro. Feel free to play around with changing the messages and/or the amount of progress each step should be credited with.

 

[Mahendra S]

Hi Luke, Thanks for the update and agree we need to have user form for this progress operation, we need to see how to mitigate the screen flash.

how ever trying to understand the challenge with progress bar implementation.
the rectangle attempt was good and I certainly appreciate.

Enhancement1: need to further tweak this to have label under the rectangle bar to display the sheet names as soon as completed and at the end we need to display total number of sheets completed in a row and snapshot history updated record from Snapshot History tab in this label as well.
please see the updated draft with enhanced userfrmprogressbar.

Please let me know on this adjustment, this is good to have otherwise also fine.

I have a general q regarding progress bar, I had implemented the progressbar earlier in old vb version, the issue I am facing is I could not import the frm file as current vba is not supporting this from excel.
is there a way can I load vbp and frm files in vba please let me know.

Regards

 

[Luke M]

I tried to interpret what you wanted...wasn't completely sure. If this is not right, would be good for you to write down at what steps you want a change in progress bar to happen, what value of progress, what caption, etc.

I'm not exactly sure what you are asking about the VB. Are you trying to import/export the UserForm?

 

[Mahendra S]

Thanks for the update Luke, yes you have interpreted correctly. however if you notice based on current run (the condition here is the tabs that got executed based on Publish = Yes from sa_sheets table)
please see below, we have RN Prod Defects got repeated 8 times which is not correct,
is it going in to loop, we need to correct




regarding the vb, .frm and .vbp are the files which are created in vb5 or vb6 but I am unable to load them as vba not able to load them.

 

[Luke M]

You were right, I had the message box getting updated at the wrong place in code. I've moved it so that only sheets called out in SA get listed. I also noticed that the progress bar's width needed to increase to fit inside the box, and I have adjusted that as well.

Not 100% sure on the VB stuff, but my guess would be that's it a version compatibiliaty issue.

 

[Mahendra S]

Thanks for the update Luke, need to verify this with more tabs. what is the design behind % increment (0%, 10%, 25%...done) progress bar, is it just random or based on number of tabs and completion of evaluation of each tab, please let me know.

The general vb question: vba has the Import file capability, and you can choose .frm file when you click on File -> Import File from menu, but it is unable to load my .frm file saying the frm can not be loaded. Is there any other way I can open .frm file to see the content, please let me know

 

[Luke M]

For the most part, it's random in the sense that I just picked some numbers to assign value. E.g., copying the source sheets is worth 70%, formatting is worth 10%, etc. While it's looping through the source sheets, that part is calculated as the fraction of available percentage. So, if we say it's worth 70% total, and it's half done, then it will calculate 35%.

For the file, I would try to find the source of where it was exported from. I don't import userForms typically, but build from scratch. Reason for me is this avoids "ownership rights" when dealing with paid consultancy projects, and I've found that people tend to want slight customizations on each one.

 

[Mahendra S]

thanks for the update on progress bar, yes agree it is always better to build from scratch., of course the sample frm file I mentioned earlier was a test file I had created in vb6 in the past..now vb6 is not supported anyways will find otherways, this is just for a reference only.

one enhancement as below:
Enhancemnet1: Can we fill the only small rectangle current color 'Yellow'(Data Refresh and Publish as of..) in SA, DB, DP and MD tabs with what ever color in R8 cell in SA tab
This will also apply to pdf reports that gets this header.
Please let me know on this.