Forum Discussion

GlebsKozlovskis's avatar
GlebsKozlovskis
Copper Contributor
Mar 09, 2020

Excel active checkbox

Main idea is:

I have a list, where company has to analyse candidates/products etc. There are 3 steps in evaluation: contacting (inmail), phone call (call), and meeting (tikšanas) (see picture below).

It is important to keep the dates of contacts and it is important to make it super easy for the end user. 

I have created 2 sheets: "main" and "properties".

I am trying to solve this problem, using the checkbox feature.

So i made the formula - if checkbox is enabled - on the "properties" sheet appears the word TRUE on a specific cell, if the cell says TRUE - the today's date appeared on the nearest cell and it is copied to the main sheet (pic2).

 

 

The problem #1: it is important for me to have the checkbox attached to the cells, so if the size and !position! of the row changes - checkboxes travel together with own cells. I know, that I might use ActiveX controls for this reason, BUT as I mentioned before, I need active checkboxes - so I need to use Form Controls and attach them to respective cells.

 

Problem #2: How to copy settings for the cells for 150 more examples, as if I roll down - i only copy settings for the cell, but not for the checkbox, so I should set up each 150 checkboxes again.

 

I tried my best to explain it. I apologize in advance, if my explanation is too complicated or if I created a very difficult way of making it. 

 

Look forward to hearing from you!

 

Thank you!

Formulas and Functions

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 10, 2020

    GlebsKozlovskis Allow me to offer the simplest of solutions, requiring no VBA and no checkboxes. Instruct the user to press Ctrl+; (control and semi-colon simultaneously). This enters the current date into the active cell.

     

    Otherwise, your solution looks nice but I fear that it will not work. The use of TODAY() makes the date dynamic. In your example you checked the box on the 9th of March. Have a look at the schedule now, and I bet that it reads March 10. 

    • GlebsKozlovskis's avatar
      GlebsKozlovskis
      Copper Contributor
      Mar 10, 2020

      Dear Riny_van_Eekelen, thank  you for your solution, yet the task is a bit more complicates. 

      Please, feel free to check another forum, where we're actively trying to figure out the situation, which appeared to be even more complicated.

       

      https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/excel-active-checkbox/003c8a9f-b391-4914-bef0-3aa2ac34cffb?tm=1583760644301&auth=1