if statement with checkboxes

[blh]

Hi I am VERY new to Excel VB and require some assistance

I have used a large number of form check boxes which i have linked to a various cells (say A1:A10) on a worksheet (call it worksheet 1).

When any of the check boxes in the range is checked (ie TRUE) I want to write "True" in another cell in another worksheet (say worksheet 2)

If more than one checkbox in the range is checked (True). I want it to write "True" in another cell in the worksheet in the next available cell. eg if checkbox 1 is checked then is will write in cell A1, if checkbox 2 is checked it will write in cell A2 etc. So it must just write the text in the next available cell. Not all the checkboxes may necessarily be checked (if only checkbox 3 is checked then it must still write in cell A1)

I have tried to write an if statement for this but I keep getting errors.

Can anyone help???

[Zagrijs]

Please upload the file?

[Andy Pope]

The attached uses some formula to determine checkbox select and displays an ordered list of those checked.

[blh]

Thanks this looks good so far you get the idea of what I would like, but unfortunately I cant see your code?? how did you do it?

Also I would like the text to be place in another worksheet (eg Worksheet 2)

Lastly, I see the text changes according to numerical value eg if check box 4 is selected then checkbox 3 the texts shows textbox 3 in the cell above text box 4. I would the text to reflect the sequence in which the checkbox was selected.

Thank you SO SO SO much

[Andy Pope]

There is no code. It was done with formula.

The example has code and stores the selections in order. I have used a named range on the activesheet so you can see the results whilst testing. All you need to do then is alter then named range to another location.

Please Login or Register  to view this content.

[blh]

Hi again,

For a newbie like me your code is a bit too complicated. I have attached my file. If you see in the worksheet "Maintenance Plan" there are numerous checkboxes indicating whether a a major overhaul etc for a particular year was done or not (unchecked means not done, checked means done).

What I would like to do is, once the box is checked (ie a major overhaul was done etc), then this information is updated on the Plant History worksheet. Ie the year, the unit, the comment, etc. The comment must reflect the type of maintenance done eg Major over haul, speed damper etc.

Also on the maintenance worksheet, if I click the button saying unit 6. It scrolls to the heading unit 6, but then you no longer have the buttons on the left hand side. I want to keep the pane on the left such that the buttons are always visible.

Hope you can help.

Thank you!
Thanks

[blh]

Please see attached file

[blh]

Can any one help?Please!!!

[Andy Pope]

You can assign all the checkboxes to this routine.

Please Login or Register  to view this content.

Unlike the previous example this will not remove any items from the history sheet, it simply appends. Although unchecked items have a prefix on the comment so you can find them easily.


If you want your buttons to remain is view when viewing Units lower down the sheet you will need to place them in the top part of the frozen pane.
Or create a dedicated toolbar (xl2003) or custom ribbon(2007+)

[blh]

Hi thanks but I keep getting a run time error.

Can you please adapt the code in the file attached in the previous post on one of the check boxes so I can see how it is done?

Thank you

[Andy Pope]

See attached

[blh]

Hi Andy,

Ok I think I have figured your last comment out. It works great but how can I keep it such that it appends when checked but removes the insert in "plant history" when unchecked. Or is that a mission?

Thanks

[Andy Pope]

So if somebody clicks a checkbox you keep a record of it. But if they then subsequently uncheck you don't care about keeping a history of that?
If that's the case why the need to store clicks in order?

[blh]

Yes because if someone unclicks it it means the job was not done therefore I dont want to keep history of that. I have adapted your code to make a msg box show to warn the person that the box will be unchecked and hence the record of it being checked will be removed. I want to store the clicks in chronological order. Ideally a person would click a checkbox in 2011 before checking it in 2012. So i would like the plant history to reflect the dates sequentially. So I dont want the code to switch the sequence of the checkbox number like the previous code did (your code shifted checkbox 1 to always be first-this might not always be the case in my situation).

Sorry for the confusion

[Andy Pope]

This will append a checked item or remove an unchecked one.

[blh]

awe its not working! it seems to work in your file I tried to copy paste the macro into my file but it doesnt seem to work.

sorry I am completely useless at this!

[blh]

awe its not working! it seems to work in your file I tried to copy paste the macro into my file but it doesnt seem to work.

sorry I am completely useless at this!

[blh]

THANK YOU SO MUCH you really helped its working great now!

[blh]

Hi Andy,

Any chance you can adapt the code such that once the checkbox is checked the information appends to the top of the table instead of at the bottom. This is to ensure that the latest information is at the top and the older information is at the bottom of the table.

Thanks
Regards

[blh]

Hi there

What is wrong with the following code statement? I keep getting an error. It says that the 'item with the specified name wasn't found'

Set shpCheckbox = ActiveSheet.Shapes(Application.Caller)

Can anyone please help.

Thanks

[blh]

can anyone help please?????????