• 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.

VBA CODE HELP

Jet Fusion

Member
Hi all,

Trust you all safe.

Please can someone assist with the code in the attached file. I am trying to do it to the following link (
) but it doesn't see to work when clicking on the check boxes. o_O

Kind regards
Jet
 

Attachments

Jet Fusion

Member
see so simple, thank your for you help guys. :cool::eek:

also in future if I would like to make it more than one coloum how would I include that in the code?
 

p45cal

Well-Known Member
If target.column = 4 or target.column = 6 then
if target.column >=4 and target.column<=9 then

you takes your choice…
 

Jet Fusion

Member
Is there a way to stop it from bringing the debug dialog box up. It seems when you click on A (E6) and the amouont by average (G7) it brings up the debug code box. Run-time error "13" type mismatch.

Also is there a macro that you can use that can clear the checkboxes all in one go? :confused:
 

Attachments

Jet Fusion

Member
@vletm thank you so very much, works like a charm. Will alter it in future as I might need to add more coloums with for "B" etc so I wouldn't want to clear all check boxes for all coloums only per coloum.

Again thank you *well done, claps hands* ;):):cool:
 

p45cal

Well-Known Member
Is there a way to stop it from bringing the debug dialog box up. It seems when you click on A (E6) and the amouont by average (G7) it brings up the debug code box. Run-time error "13" type mismatch.
This is because when you select or double-click those cells you're selecting/clicking on 2 cells; you've merged them. So target.value is 2 values, not one, so the If Target.Value=… throws an error.
Several ways you can deal with it:
1. Don't merge the cells, instead, unmerge them and in the Format Cells… dialogue box, on the Alignment tab, choose Center Across Selection in the Horizontal alignment dropdown.
2. You can check early on in the code it Target is more than one cell - this is what Vletm has done.
3. You can be very specific about what cells to include/exclude from processing by the macros with the likes of:
If Not Intersect(Target, Range("E9:H108")) Is Nothing Then
or if you have a more complex arrangement of cells then:
If Not Intersect(Target, Range("E9:H108,J9:K24,M9:N24,P9:Q24")) Is Nothing Then
What's more you can combine it with checking for only a single cell with the likes of:
If Not Intersect(Target, Range("E9:H108")) Is Nothing And Target.Count = 1 Then
 
Last edited:

Jet Fusion

Member
This is because when you select or double-click those cells you're selecting/clicking on 2 cells; you've merged them. So target.value is 2 values, not one, so the If Target.Value=… throws an error.
Several ways you can deal with it:
1. Don't merge the cells, instead, unmerge them and in the Format Cells… dialogue box, on the Alignment tab, choose Center Across Selection in the Horizontal alignment dropdown.
2. You can check early on in the code it Target is more than one cell - this is what Vletm has done.
3. You can be very specific about what cells to include/exclude from processing by the macros with the likes of:
If Not Intersect(Target, Range("E9:H108")) Is Nothing Then
or if you have a more complex arrangement of cells then:
If Not Intersect(Target, Range("E9:H108,J9:K24,M9:N24,P9:Q24")) Is Nothing Then
What's more you can combine it with checking for only a single cell with the likes of:
If Not Intersect(Target, Range("E9:H108")) Is Nothing And Target.Count = 1 Then
Thank you, will def have a look into this and adjust it to suit. Appreciated the advice, thanks.
 

Jet Fusion

Member
Hi all,

Sorry I'm back again :rolleyes:

I've managed to change the clear cells with a macro (using the formulas you guys gave, thank you) but now it gives me a error when selecting more that one (by accident)
Code:
If Target.Value = Chr(254) Then
.

What I want to know is, I'm not good at coding, but how would I code it so that for each selcetion (D9) the checkmarks will be for that selction. So when I select number 4 (or whichever) the checkboxes are specific to that number in the (D9) and so on. And also the clear button should also only work for the current selction, not as it is now it will clear for all o_O

Thanks.
 

Attachments

vletm

Excel Ninja
Jet Fusion
You seems to skipped both hints how to avoid that Your challenge...
Of course, accidents happens ... but You could try to learn from those too.
Please, try to reread both for You given hints and modify Your code.
That way, You could learn ...
 

Jet Fusion

Member
Jet Fusion
You seems to skipped both hints how to avoid that Your challenge...
Of course, accidents happens ... but You could try to learn from those too.
Please, try to reread both for You given hints and modify Your code.
That way, You could learn ...
Like I said I'm not good at coding but do appreciate the advice. The error is not much of a issue, my issue is the below

What I want to know is, I'm not good at coding, but how would I code it so that for each selcetion (D9) the checkmarks will be for that selction. So when I select number 4 (or whichever) the checkboxes are specific to that number in the (D9) and so on. And also the clear button should also only work for the current selction, not as it is now it will clear for all o_O
i wouldn't even know where to start.

thanks anyways tho :)
 

vletm

Excel Ninja
Jet Fusion
If not 'good at coding' ... but You could read ... eg below text:
2. You can check early on in the code it Target is more than one cell - this is what Vletm has done.
You seems skipped that part too.

Please, try to read those hints and compare those code with Your the newest code.
That way, You could learn something.
Many can do copy & paste ( but only few can do that correct way).
 

Jet Fusion

Member
Jet Fusion
If not 'good at coding' ... but You could read ... eg below text:
2. You can check early on in the code it Target is more than one cell - this is what Vletm has done.
You seems skipped that part too.

Please, try to read those hints and compare those code with Your the newest code.
That way, You could learn something.
Many can do copy & paste ( but only few can do that correct way).
Thanks
 

Jet Fusion

Member
Any help with the second part of my question? How to have the checkboxes for each different selection. In D9 there is a list so if I select one of them the checkboxes should be for that selection. If I choose a new selection from D9 then I need the checkboxes for that selection plus if I've added checkboxes and select another item and go back to the previous selection then the checkboxes should still be there for that selection!

What I want to know is, I'm not good at coding, but how would I code it so that for each selcetion (D9) the checkmarks will be for that selction. So when I select number 4 (or whichever) the checkboxes are specific to that number in the (D9) and so on. And also the clear button should also only work for the current selction, not as it is now it will clear for all
 

p45cal

Well-Known Member
If someone picks, say 3, in cell D9's dropdown. Where/which are the checkboxes for 3? What/where is the selction for 3?
 
Top