Compile Data and Produce a Dashboard

[Mahendra S]

Hi Luke, Thanks for the quick update.

I am seeing following err with user login after filling the user name and password and click ok.
Is it possible to have username to be popped in combobox in the userlogin form, so that no need of typing.
I can select the name from combo list and enter the pwd and click ok.



other issue is still the double click text box is popping for col N and col O in Defects Priority tab.
looks like the double click text boxes are not detached, please confirm on this.
we are going to and fro on this, please detach the control & un comment the related code so that we will have normal cell.

The row1 master filters look ok but will let you know after through check.
Appreciate your timely response!

Regards,
M

 

[Luke M]

If we don't need username to be secure, I'd suggest a simple InputBox again, since we only have 1 input. This also solves the Unloading issues.

Text box has been disabled now. I left the code in there, but wrote an Exit Sub right at the beginning, so it doesn't do anything for now.

 

[Mahendra S]

Hi Luke, Looks like the cells for Desc and Prg/Resolution are getting saved, this is back to normal.

I want to clarify the need for combox for security.
I would like to have two usernames names like "sa" and "mahendra" and these values should be in combo list.
we can have these values in D1 and E1 of row1 in super admin worksheet. The combo box should pick the values from above cells and populate in the list of combobox.

password can be stored as constant in the code or in the cell of row1.
once these are verified it should let you get in to superadmin.

I hope this clarifies.
Please let me know if you can adjust this.

Regards,
M

 

[Xen]

Uh, I have a macro-less solution for original problem (creating master sheet), it's probably not what you need to solve real problem, and you already have macro that works the way you want, but I'm still posting it.

 

[Mahendra S]

Hi Luke, I hope you had great weekend!

The above problem with Run time error(361) is resolved now, please find latest template attachment for your review.

Still we need some definite improvements on this template and following are outstanding and need your continuous support. I appreciate all the effort and time you had spent so far.

Issue1: Currently If user clicks on "Super Admin" tab first time it is not asking for user credentials by popping modal userform. where as if user first time goes to other tabs and come back to "Super Admin" tab it is asking for username and pwd. At any point of time if first time user clicks on Super Admin tab it should ask for user credentials and based on successful verification allow to edit any cells/perform copy row/tranfer or audit the table data etc.

Issue2: Sign On Form now getting user names in to combo box. However we should not store any hard code or constant values in code. I have created unames table in super admin tab and both uname,pwd combination should be validated, need your support on enhancing this dynamic logic to verify both username and pwd from super admin uname table. The user should not input (user name is non editable) any values for User Name combobox, only the selection from drop down is applicable and password should be user entered.

Issue3: In Defects Priority tab if "ETA To Fix" column cell is showing with future date then it should not display the cell with red color, the cell should be in no color and text can be with black color with center aligned.

Issue4: Currently when I go to File -> Options it is not functioning/opening the Excel Options screen, is it possible to have this controlled in Super Admin tab (Excel Options enabled / Excel Options Disabled) like very similar to Ribbon options we implemented.

Issue5: Similar to the above issue, is it possible to 'disable the view code' from right click on worksheet and implement with option controls similar to ribbon options we have in super admin tab.



Issue6: How to open the workbook/this excel template always in maximam( ) mode.
some times if user restores down the file, next time when I clicks it come as restore mode, instead at any time when the workbook loaded/opened is it possible to enforce to open in maximize mode using the code.

Please let me know possible solutions for above issues.
Looking forward for improvements!

Best Regards,
M

 

[Luke M]

Re: Issue 1
I setup the Workbook_Open event to have the workbook open to Dashboard sheet. Now there is no chance of starting on Super Admin and skipping the security check.

Re: Issue 2
I believe we have this setup correctly now.

Re: Issue 3
Red formatting was incorrectly getting applied everwhere. I've changed the code so that it resets the table's formats so we start clean, and then re-builds it. Future dates are no longer getting colored red.

Re: Issue 4
Options menu can't be opened while an ActiveX control is selected. If it happens again, just click somewhere in worksheet, and then you can get to Options.

Re: Issue 5
Can't remove this option from sheets, but we can protect the VB code. For now, I've applied the password of "Chandoo" to view the code. This is done/editable by right-clicking on the project in Explorer window, select VBAProject Properties, and go to Protection tab.

Re: Issue 6
Not sure we can make it 100% full-proof, but similar to ribbon, set it up so that if workbook is activated, XL will be maximised.

 

[Mahendra S]

Hi Luke, Thanks for taking the review and adjusting especially password setup for code, it makes sense and good to see the default worksheet as dashboard, appreciate the idea.

The template is a good example to see all important features.
If we can tweak below that would be really awesome.

Issue1: ETA To Fix column date data should always be greater than Defect Open Date (MasterData) . In case if ETA To Fix date is less than Defect Open Date then the ETA To Fix (Master Data sheet) Date need to adjusted to Defect Open date +1 Network day (weekday).

Issue2: Please confirm these date adjustments are not required in Defects Priority tab since we do get the adjusted dates from Master Data by Transfer Rows operation.

Issue3: When the workbook is loaded by default the "Disable Ribbon" should be option selected, need to enforce this.

Issue4:Is it possible to to make non editable for the User name combo box, as-is it is allowing to type in to the combo box, user should not enter any text /numbers where as only to be able to select from drop down.

Please let me know on above issues.
Thanks again...

Regards,
M

 

[Luke M]

Re: Issue 1
DateCheck macro has been edited, checks the Fix Date column and adjusts date if necessary.

Re: Issue2
Correct, the adjust dates macro is only run on MasterData, which then feeds Defects.

Re: Issue3
Added a line to Workbook_Open to set the option button

Re: Issue4
We can't disable the box completely, or user can't use the dropdown. I can force a match, however, so user can't type random stuff, so in a sense you have to pick from dropdown.

 

[Mahendra S]

Hi Luke, Thank you so much for prompt responses.

For Issue4, did attempted and could not find an easiest way to handle, how ever I will keep it simple,
for friendly user experience, I have added the ControlTipText, please take a look with latest template.

Issue5: Currently The Ok button is always enabled in the user form.
Is it possible to only enable the Ok button upon uname selection and input in to pwd, if combination matches then only enable the Ok button otherwise to pop the notification message only in the label (lblMessages) which is placed below the buttons.

Even if user combination doesn't match the message needs to be shown only in the label (lblMessages), so that we can avoid multiple clicks and popups.

Please let me know on above issue.

Best Regards,
M

 

[Luke M]

I like that idea. It moves all the security checks to within the form, and simplifies things a little. I believe I got the lblMessages to displays things correctly, but please check the usrFrmLogin code to make adjustments if needed.

 

[Mahendra S]

Cool, this is perfect Luke!
Will do the load test with data and see if how it goes and if any issues will let you know we can resolve hopefully as we have good template now

Thanks for the great support.

Regards,
M.

 

[Mahendra S]

Hi Luke, The User credential form is working in good in normal flow.

Issue1: I see one issue & is throwing below err (something with form property's) when I select value from dropdown and hit backspace or delete the value that got selected from drop down. then click on cancel.



Please let me know if we can fix above err with property value adjustment or by code.
This is exceptional case but good to solve to have error-free credential validation.

Clarification1: Is there a specific reason for this macro ( Sheet21.PopulateCBs) named like this, can it be just PopulateCBs? or is it essential to tag with sheetnumber, please clarify.

(attaching the latest template, you can see some text updates in user credentials..)

Thanks for all the support!

Regards,
M

 

[Luke M]

Re: Issue1
Hmm. After some more reading, I change the style of the dropdown to be fmStyleDropDownList. Now the user has to pick from list, can't free type. This resolves the issue of invalid value being chosen. User can use dropdown, or type first letter of a name (or hit repeatedly to toggle though names starting with same letter).

Re: Clarification1
It's because that macro is within a Sheet module, not a regular module. I have it in the Sheet module because we're referring to objects (the cb's) that are a part of the Sheet object, so I needed the code there. Having the sheet name ebfore the macro name helps the code know where to look. Now, I originally wrote it that way because we only had one set of cb's, and then later added some to Dashboard. It would be possible to re-write the macro so it sits in a regular module, but there's no real pro/con to doing so other than the appearance.

 

[Mahendra S]

Thanks Luke, the resolution with change the combobox style (fmStyleDropDownList) works correct and resolves the "Invalid Property Value" Err and explanation on sheet21 cb's.


Further clarification on combobox style (fmStyleDropDownList):

Should we maintain the consistant style for other comboboxes in "Defects Priority" and Dashboard" tabs,to make sure above err not to occur. However I dont see an err, but user still able to delete the text / backspace on selected dropdown value in "Defects Priority" and Dashboard" tabs.

Please advise.

Regards,
M

 

[Mahendra S]

Hi Luke, I went ahead and changed the style for other combo boxes in "Defects Priority" and Dashboard" tabs. see the latest attachment.
please take a look one more time if any hidden/unknown code related to these activex combo boxes.

Also it resolved another err case, where if backspace/delete are functional with original combo style, you will see some unknown text it was getting displayed in chart titles.

I appreciate the cool fix with the style which resolved many err's like above.

I have seen some object not defined err's related to doubleclick event for multiline text, but commented everything related to descbox, please do the code review one more time to make sure not left with unwanted code(if any unwanted code left, please comment it out)

Enhancement1: Is it possible to have err handler in each and every procedure and function to capture err number, err desc and procedure/function name if an err occurs.

I understand this will be an extended effort, but as pro active step with this approach it will give an immense value, if any unknown err occurs / encounters then we know where exactly to nail down the problem/issue.

Please advise.

Regards,
M

 

[Luke M]

Woohoo! Feels like we are very close. I did notice that you commented out the Worksheet_Change event code, which was flagging cells that became changed. Was this an accident, or do we no longer need to keep track of cells that are "unsynched"?

I've gone through and added an ErrCheck for all of the main macros that can fail, I believe. Hopefully it's never needed, but I agree, adds some polish to the final product.

 

[dor]

just a newbie however was looking for something quite similar to what you guys are working (you guys rock). When I go to enable the editing I get an error Number: 1004 Description: Method 'Worksheets' of object '_Global' failed Source: PopulateCBs

My problem I am trying to solve is multiple back end systems (as you have) that I am trying to assign a global Ranking. You can only have 1 true number 1 defect (across the multiple backends) but first you have to combine them.

For instance you have 15 critical items. I apply the concept of stack ranking. Its a field for stack rank (or rank sort) whatever you like but it is the global ranking across systems environments etc that is only possible to assign after one creates the master data sheet as you have.

 

[Luke M]

just a newbie however was looking for something quite similar to what you guys are working (you guys rock). When I go to enable the editing I get an error Number: 1004 Description: Method 'Worksheets' of object '_Global' failed Source: PopulateCBs

My problem I am trying to solve is multiple back end systems (as you have) that I am trying to assign a global Ranking. You can only have 1 true number 1 defect (across the multiple backends) but first you have to combine them.

For instance you have 15 critical items. I apply the concept of stack ranking. Its a field for stack rank (or rank sort) whatever you like but it is the global ranking across systems environments etc that is only possible to assign after one creates the master data sheet as you have.

Hi dor
I think it would be better if you started a new thread with this question, and possible upload a sample workbook. I'm worried that by posting in this thread, the other forum members won't see this, and it won't get as much help.
Thanks.!

 

[Mahendra S]

Hi Luke,

Yes while I was reviewing the code for color change and got commented, and un-commented the Worksheet_Change event code now and still need this (see the attached latest template)

Issue1: I see one new issue with User form, when you click on Cancel button with out selection of uname/pwd or with selection it is throwing below err



Is this coming now b'cos of err handling, please let me know.
Cancel should clear the values and go to Dashboard as it use to be before, please let me know.

Clarification1: Luke, I am thinking if we have one single errCheck procedure and call in every procedure/function, will it be make more simple rather calling the same errCheck code in every procedure/function.
This is just to make it simple, but need your valuable suggestion.

Regards,
M

 

[dor]

Luke - Let me try to clarify. I was looking at your example and it looks very useful. I was trying to add in to the conversation

Enhancement:I consider the ability to allow for someone to prioritize in your DefectsPriority tab. As this is the one spot in managing your multiple back end systems where you can "rank" all of your defects. In your current exmaple there are 25 defects which is most critical? ideally it would not allow for the use of the same number so that you can only have 1 number one.

Error: I was talking about your spreadsheet where i get the error. not in something I had a specific question about.

 

[Mahendra S]

Hi Dor, can you give exact repro steps on above 1004 err?

What action you did? so that we can look.
regarding the number we will have repetition and what we have built is correct, as Luke suggested you might open a different thread for your specific need.

Thanks,
M

 

[Mahendra S]

Hi Luke, I have enhanced few things for your reference, please see the attachment.

Enhancement1: Added the code to make the dashboard cells read-only, please see the code, but need to tweak based on following need.

Ultimately on Dashboard tab user should not be able to edit / delete the data in any cell, should not be able to edit chart title and any other content related to charts. This will help not to tamper with output data which is getting displayed.

Please see the Worksheet_SelectionChange code I have added, if you think some thing better than this please enhance.
But the above code still let you to edit/delete the chart title which shouldn't happen at any time, and don't want to enforce password to protect this dashboard. all it is required to be read-only and drop down operations still continue as-is.

Enhancement2: I am thinking if we have one single centralized errCheck procedure and call in every procedure/function, will it be make more simple rather calling the same errCheck code in every procedure/function.
This is just to make it simple, please let me know if this is not possible due to any limitations, then we can stay with what we have now.

Issue1: I see one new issue with User form, when you click on Cancel button with out selection of uname credentials or with selection of usercredentials and click on Cancel button or "X"(Close) on top right corner of the pop up it is throwing 438 err, this requires a fix in my understanding, please let me know.

Issue2: Not sure about 1004 err with PopulateCB's, I am curious how this is happening, this might be another symptom of activex controls, should we rather change to normal list box (fine with out drop down arrow visible) and connect with table from admin sheet, which will make things more easier.
It would be better if we go with all 5 drop-downs with this alternative approach, Please adviseand do needful.


Regards,
M

 

[Luke M]

Re: Enhancement 1
For the Dashboard sheet, rather than using a macro, we'll simply protect the worksheet. The two cells that the dropdowns are linked to need to be unprotected, but they are hidden behind the dropdown, and user can't get to them. You can click on the charts, but you can't make any changes to it.

Re: Enchancement 2
Unfortunately, no, the ErrCheck would have to be in each code block. The On Error method can only have a GoTo or Resume. You also said you wanted information about where error occured, so each line needs to be different so that it can pass that information along.

Re: Issue 1
I accidentally removed the "Exit Sub" line in OpenAdmin. There wasn't really an error, the code was just running along normally and didn't stop, and proceeded to the error message. Oops!
Seems to be functioninig correctly now.

Re: Issue 2
I have not been able to duplicate the error, so I'm unsure how to diagnose. If you discover how to create the error, please let me know and I'll address. We could switch, but we will lose a lot of functionality. I would prefer to hold off on this till we know more about what causes the error.

 

[dor]

to reproduce the 1004 error I was taking your latest version, opening it and enabling editing. I got it to stop doing that by disabling the options under the protected view in the trust center.

 

[Luke M]

I suppose that makes sense. If editing is not enabled, the macros can't make their changes.