help pls

[Maqsood]

Please see the attached sheet which shall be used by different users. it would be in a protected view. i need the following things in the sheet.
1) if the value in cell F19 is less than 2, the progress chart in row 31, should show a 20%.
2) if you see the activity status which starts from row 35 to row 42 (listed as 1,2,3, up to 8, it has checkbox in each row. suppose, if the user select the checkbox in row41, all the checkbox from row 35 to 41 should be ticked automatically and the percentage (as per the calculation in cell X35 to X41) should be shown in the progress chart.
3) if the checkbox is tick marked then the data validation list in cell O35 to O41 should be defaulted as "2". Still the user should be able to choose any item from the drop down list.
4) Lastly, the print command should be active if and only if the any of the checklist in cell D46 or D47 is tick marked. if any of these cells is not ticked and the user try to print the sheet, it should show him the message/ dialogue box as "please confirm that the checklist is completed".

i am not an expert in excel. appreciate if you could help me out in resolving this.

thanks
Abdul Rahiman

 

[Nebu]

Hi:
I could not make out head or tail out of your attached workbook.
please find what I understood from the file

1. F19 is independent of your column chart, so on what basis you want to change the chart to 20%. As far as I understand the data for chart is driven by the check boxes.
2. Automatic checking of the boxes can be done only using a VBA. I would assume that the bar chart will change automatically as it is linked to the check boxes.
3. This can be done using VBA.
4. can be done using VBA.

Thanks

 

[Maqsood]

Hi Nebu,

Thank you for your reply. you have rightly understood the file.

1) F19 is not linked to the chart data. is there any means where the chart data can be driven without checkbox or any other solution to have this cell data linked to the chart?
i am a newbee in VBA. if you could assist me on how #2, 3 and 4 can be done.

Thanks

 

[Nebu]

Hi:

I cannot guide you with linking F19 , I believe that will be a business decision which you have to make a call. I am not sure about the background of the exercise you are doing. With regards to your other needs I can help you with the macros not a problem. Please find the attached.

Thanks

 

[Maqsood]

Hi,
Thank you for the assistance. but now I am unable to check/ uncheck the checkbox (cells L35 - L40 & cell D46 - D48) . even the Print icon that you have inserted in the sheet is inactive. can you please advise on how to resolve this.

 

[Nebu]

Hi:

I guess the macros are disabled at your end. Follow the following link to enable the macro and try the check boxes and print button.
http://www.exceltrick.com/how_to/enable-macros-in-excel/

Thanks

 

[Maqsood]

Hi Nebu,

I enabled the macro but still the check box and print button is inactive. how do I proceed?

 

[Maqsood]

HI Nebu,
I have tried the macros in another worksheet as per my requirement (attached) . but still its not working. also how can I have the command box in the attached sheet? Can you please assist.

 

[Nebu]

Hi:

I am not sure what you are looking for. I have fixed your excel file . The coding I have done do the following.

Print button:
If you check either Check list 1 or 2 and click the print button it will print the range you have specified in the print area , if none of this check boxes are not checked it will give a warning message.

Check boxes from row 35 to 42
if checked the Performed by column will automatically change to 2, also any check boxes above will be checked automatically changing the corresponding performed by column to 2.

Both these macros are working fine at my end, its not complicated, I am not sure why it is not performing at your end only option if it is not working is to trouble shoot it at your end. Find the attached.

Thanks

 

[Maqsood]

Hi,
I am using the 2013 version of excel. the check box and the print button is still inactive.

 

[Nebu]

Hi:

Follow this link
http://stackoverflow.com/questions/27283831/excel-2013-buttons-disabled-cause
Or search in Google why buttons are disabled in 2013 , The macro is fine there is no issue with it. You can also go to VBA editor and start running the macro from there as well instead of clicking the button and test it.

Thanks

 

[Maqsood]

Hi Nebu,
The form works well in excel 2007 version. thanks .

 

[Nebu]

Cool