Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 21
  • Last Modified:

Fixing a Checkbox Macro

EE Pros,

I have tried to replicate a worksheet macro, where in Column C, when you doubleclick, it cycles through 4 stages of Identification ( Blank, A check mark, A Question mark and a X).  I've included the actual workbook/sheet and previous code that worked.

I'm using this as a quick, "check sheet", to have someone using it, declare the status of certain statements.

Any help would be most appreciated.  Thank you in advance.

B.
Checkmarks.xlsm
0
Bright01
Asked:
Bright01
  • 5
  • 3
1 Solution
 
Jonathan KellyCommented:
Bright01,

This part of your macro seems to say that the code only runs where the value in col A =1

In the example file a checkmark is places in col c row 4 because the the value in col A row 4 = 1

If ActiveCell.Offset(0, -2).Value = "1" Then
            Togglecheckmarks Target
            Cancel = True
        End If

Also the following line
Application.EnableEvents = False fires after after you double click the first cell in the range thereby stopping further events from firing - i.e. your macros will stop working after the first pass.

I wonder why this is designed in ?

Rgds,
Jonathan
0
 
Jonathan KellyCommented:
To add to above comment the
Application.EnableEvents = False is called only if the Target is Blank.

By removing

1.
If ActiveCell.Offset(0, -2).Value = "1" Then

AND
2.
Application.EnableEvents = False

The macros seem to work as you have described. I will attach my updated file.

Rgds,
Jonathan
Checkmarks.xlsm
0
 
Bright01Author Commented:
Datrias,

Thank you very much for your comments.  I remember now that we had originally put a "1" in column A to demonstrate that the macro only ran when there was a "1" in that column.  I'm good with that.  I had forgotten that we used that as a way to designate the row should be active.  So you are right and my sample is not right.  Put a 1 in each row, Col. A for each of the cells that should be active.  

Also, it shouldn't stop after the first check.  So that should also be changed.

I wish I could do this on my own but I'm not that good at diagnosing problems.

Thanks,

B.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jonathan KellyCommented:
Bright01,

Practice & Patience go along way with this stuff !

Do you want me to make any changes to the original file and send it back to you?

Rgds,
Jonathan
0
 
Bright01Author Commented:
That would be great.  I just downloaded your fix and couldn't get it to work on a doubleclick.  It also came across as an XLSX and had to change it to an XLSM?

Thanks!
0
 
Jonathan KellyCommented:
XLSM have macro execution turned on by default.

I will edit the original and upload it.
0
 
Jonathan KellyCommented:
I have commented out the line
Application.EnableEvents
and put a comment next to it.

Seems to work ok for me.

You might need to enable macro's under Security.

Rgds,
Jonathan
Checkmarks.xlsm
0
 
Bright01Author Commented:
Fantastic!  Thank you very much.  Works perfectly.

B.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now