Tailored button to write value on selected check box

[tranhaithang]

Dear all,
May I ask your help on this:
I have a loss tree file, which will be used to record why some weeks sale is lower than forecast. Users will need to click on some weeks, then click on the list of loss tree on the right side & click on button "Record". They need to record each & every week in the chart, and there will be different SKUs to be listed in this sheets.
What I'm trying to do is: a button that will find where the check box is ticked & which loss tree code is ticked, then write 1 to the range B31:AN46

I must confess I am a noob in VBA and don't know how to write this. Could anybody help me?

Thank you
Thang Tran

[Richard Buttrey]

Hi,

One way, see attached

[tranhaithang]

Thank you Buttrey,
It delivers what I mean perfectly.
If I want to add in/remove criteria. I need to adjust inside the name manager, is that correct?

Thank you
Thang Tran

[Richard Buttrey]

Indeed so. Just change the range name as necessary.

If it's likely to change frequently then I suggest you create a dynamic range name so that everything will adjust automatically and you can forget about it.

Personally I'd have used a data validation drop down cell to get the input and used the same range name as the source for the list. As a general principle keep things as simple as possible. A forms control is not really necessary and is IMO overcomplicating things.

[Richard Buttrey]

OK, glad to have helped and thanks for the rep.

[tranhaithang]

Hello Buttrey,
May I ask for your help once again.
I'm trying to modify the file that you make. However I encounter one problem:
- the loss code need to input for all weeks that occurs the same loss. I cannot input weeks separately, for example: Select W1-5 with loss code 1, then deslected week & move to loss code 2. However, later on at week 30, it occurs same loss code 1. If I dont tick week 1-5 again, it will delete everything I tick before that.

--> could you help edit for me: I just want the code to record exactly the loss code at week that is ticked.

Thank you in advance
Thang Tran

[Richard Buttrey]

Hi,

Does the attached help

[tranhaithang]

Thank you Buttrey for quick reply,
Hmm, however it not work properly. somehow it keep retun to record week 10 & week 14...
Could you have a second look pls
Thank you

[tranhaithang]

Sorry Buttrey,
Could you please help me re-read the file please, the new macro is not working.
Thank you!