• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MEDIUM SIZE "project", need VBA help

I

igorsusa

Member
Hello fellow friends and Excel gurus.

I have one medium sized work ahead, for a lot of you it could be even small one... The principle of the file is quite easy, there are just few conditions to be followed.

I am attaching the file "risk_chando" and "sample", while "risk_chandoo" is the file in which I need help and "sample" the sample pattern what I want the VBA to look like and what it does.

I will truly be grateful for you willignes to help!

In case of any questions dont hesitate to aks me, I tried my best to describe my needs as much as I could.

Thanks!
 

Attachments

  • risk_chandoo.xlsx
    20.9 KB · Views: 4
  • sample.xlsm
    128.7 KB · Views: 5
Hey,

Data sheet:

All the values in Yellow are constants and please make it read only.

The rest will be edited and please make sure only these fields are editable in the sheet.

This sheet need to be accessed using user name and password only and by default is hidden/ shifted to left (does not appear when you open the file).

Thank you!
 
So every time a new entry is made into data sheet, these cells can also be editable?

Why I am asking you this because you have dependent drop downs on the other sheet. So every time a user enter a new risk he/she can change these values say Negative strategies or Impact Names or their values?

Regards,
 
May be one more question?

Will the positive / Negative strategies will always be 3 or it can be 5 , 7 or any thing?

Regards,
 
For now only 3, but maybe it will change in the future.

You could do it now just for 3, if its not a lot of work to change in the future.

Thanks.
 
Hey Somendra, so far so good, thanks!

Just one comment: The value in “Ref ID” should be incremented. You can see in the sample file how it is done.

A little more work ahead of us, but we are in the right direction!

Thanks!
 
@igorsusa

You comment in column M says, open a calendar to choose a date. Do you have a Microsoft Monthview Control available on your system or on every system where this file will go to enter the data?

Regards,
 
If you search on the net you will get lot of options but they all include heavy programming lines of themselves. I will suggest to put the date manually with a data validation in it.

Regards,
 
OK, lets do it like that. I must make sure first if everybody has ms month view available first... Will it take a lot of changes if I decide after to have calendar option?

KR
 
OK, then lets do it for now that I manually put in the date and if we decide to have the calendar option we can include it after.

Thanks!
 
@igorsusa

See this file, implemented increasing Ref id based on data sheet and transfer of closed risk to closed sheet from open sheet. Comment on this.

Regards,
 

Attachments

  • risk_chandoo.xlsm
    39.3 KB · Views: 3
Hey, great!

few comments:

format of Risk ID is just R-XXXX, so for example R-0001, if value in data sheet is 1.

When transfering lines between sheets, I want them to be sorted by Risk ID.

In general it is really impressive, just code for vice versa (from closed back to open risk if status is open) is missing and to hide (lock) the data sheet and this is it :)

Thanks man!
 
@igorsusa

I found a problem in the logic: Say you have ref id on open sheet as:
R-0005
R-0004
R-0003
R-0002
R-0001

and you transfer R-0005 & R-0003 to closed sheet, than you open sheet will look like
R-0004
R-0002
R-0001

So if you now add a new risk it will be given a id of R-0005, which is already there. Have a thought on this.

Regards,
 
Hello! That must not be the case. There must not be any duplicates. Cant you use the cell in data sheet which is incremented by 1 everytime user click on the add new risk, so that way it doesnt come to duplicates? ( not dependable whether lines are transformed between sheets)?
 
You must log in or register to reply here.
Back
Top