Create Dashboard for my Organization

[kaustubh]

Hello Friends

My name is Kaustubh Patel and this is my first post in this forum. First of all thanks for creating such a beautiful and helpful community.

Please note, I am not an excel or VBA expert but with the help of Google, I learned how to create and edit basic VBA code. Now coming to my question

I am working in an organization, with five branches say A, B, C, D, and E. We have to create a payment dashboard for these five branches. Their variable salary is paid based on scores on this dashboard. I am planning to create a beautiful dashboard using VBA and Graphs.

My Current Process

These all five branches shared various different data via email to me. Based on that data, I work out some final figure and then scores are allocated to them. These scores are in numeric format say 5 out of 10. Once the scores are collected the individual branch wise data are sent back to respective users to confirm. After getting confirmation, the dashboard is release.

What I want

1. I am looking for a VBA code or any option to lock and unlock excel data. I want once the branch input their data, they will click on freeze button to lock the data., Once freeze, the approval option will start appearing to me. I will either approve or reject the data. If rejected it will go back to branch to confirm again.

2. I am collecting data from five branches on a network. I want to give access to the particular worksheet to a particular person based on their computer name. For example, If A branch opens the file on their network then data related to A branch is only visible to them.

Please help me with these two problems.

 

[Wanderer_geologist]

Xls is perhaps not the appropriate solution and maybe you should invest in a custom web service to facilitate your endeavours.

What you are requesting is fairly complex and you are maybe taking it on a different tactic.

Protected sheet are kind of a pain for macro as any action requiere to activate and deactivate the worksheet protection, in some case it s fairly easy to break that process and gain access to data. a

Perhaps you can use an ID and password system to limitate the user access and effectively use or freeze option based on those type of control.

Best option is to the Use this ID system to facilitate the control by showing or hidding worksheet using xls veryhidden option.

For this you need to look at the way macro command can be loaded using workbook properties and additional control when exit or saving

Now, mind that xls is not fit for multiuser edition in same time, so you may consider a proper database for input/output or a system to sync multiworkbook to control data.

All of that is possible but that ‘s a noticeable amount of work.