View original (French)

Show a checkbox if cell is not empty

Solved
Thmoney-team Posted messages 14 Status Member -  
swande Posted messages 7 Status Member -
Hello,

I'm looking to write a macro that would allow me to display a checkbox only if the cell to its left is not empty. I'm not sure if that's possible, but oh well.

Thank you!

15 answers

Answer 1 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Good evening,

your checkboxes are indeed form bar checkboxes, not ActiveX CheckBox

and how many controls (check boxes) does this concern

See you later
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.
1
Answer 2 / 15
Thmoney-team Posted messages 14 Status Member
 
Yes, they are those in the developer tab under the form control option. As for the number, I would say around twenty.

Thank you!
0
Answer 3 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Re,

it is possible to write a more technical code, but certainly harder to adapt. Assuming that your input column is column B and that on row 3 you create a checkbox named Checkbox 3 (but you can rename them as you wish)
the line of code would be
If Target.Address(0, 0) = "B3" And Target <> "" Then Shapes("Checkbox 3").Visible = True '
If Target.Address(0, 0) = "B3" And Target = "" Then Shapes("Checkbox 3").Visible = False

below is an example to manage cells B3, B4, B5, and B6 for checkboxes named Checkbox 3, Checkbox 4, Checkbox 5, and Checkbox 6
this code should be placed in the sheet properties (right-click on the tab of the concerned sheet/View Code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address(0, 0) = "B3" And Target <> "" Then Shapes("Checkbox 3").Visible = True '
If Target.Address(0, 0) = "B3" And Target = "" Then Shapes("Checkbox 3").Visible = False
If Target.Address(0, 0) = "B4" And Target <> "" Then Shapes("Checkbox 4").Visible = True '
If Target.Address(0, 0) = "B4" And Target = "" Then Shapes("Checkbox 4").Visible = False
If Target.Address(0, 0) = "B5" And Target <> "" Then Shapes("Checkbox 5").Visible = True '
If Target.Address(0, 0) = "B5" And Target = "" Then Shapes("Checkbox 5").Visible = False
If Target.Address(0, 0) = "B6" And Target <> "" Then Shapes("Checkbox 6").Visible = True '
If Target.Address(0, 0) = "B6" And Target = "" Then Shapes("Checkbox 6").Visible = False
End Sub

See you +
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.
0
Thmoney-team Posted messages 14 Status Member
 
Hello,

Thank you for this response. If I understand correctly, I need to paste the lines you wrote in "View Code," close Visual Basic, and when I type in cell B3, B4, B5, or B6, a checkbox will appear?


Thanks again!
0
Answer 4 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Re,

you need to start by creating your checkboxes and identifying their names or naming them afterward; then you paste this code into the properties of your sheet and assign a checkbox to a cell reference in the code.

--
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.
0
Answer 5 / 15
Thmoney-team Posted messages 14 Status Member
 
When I close the VBA window, the checkboxes do not disappear. Do I need to delete them or is my formula written incorrectly?

Here is my workbook: https://www.cjoint.com/?3JjsoZ5uZPI

Thank you!
0
Answer 6 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Re,

Several mistakes, to start with your first checkbox is not Checkbox 1, but Checkbox 274, the name of the checkbox is displayed in the address bar at the top left of your sheet when you right-click on the box.

Then you are using the syntax
Private Sub Worksheet_Activate()

which does not match with the code made for the syntax
Private Sub Worksheet_Change(ByVal Target As Range)

So what do you want to do, have the box displayed or hidden when you intervene in column F or do we need to create a loop to control each cell in column F and act accordingly when you activate the sheet or launch the procedure with a button

--
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.
0
Answer 7 / 15
Thmoney-team Posted messages 14 Status Member
 
Wow, thank you very much! It works perfectly!

Have a great day
0
Answer 8 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Hi,

If your expectations are met, please change the status of the discussion to resolved, or let me know to do it for you.

--
See you,
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.
0
Answer 9 / 15
swande
 
Hello,

I significantly need the same macro, but I have a different variant.
Let me explain:
This macro works very well under normal circumstances (i.e., if I manually enter "1" into the target cell B3, the checkbox appears, and if I manually enter "0" into cell B3, the checkbox disappears). However, my cell B3 contains an "if" formula that returns "1" or "0", and for some reason I don’t understand, this formula does not trigger the macro, even though the formula returns the result of 1 if true and 0 if false.

Would you have another solution to propose?

Thank you,
0
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Good evening,

certainly a format issue, we need to have your full formula and even better the file to take a look.
try adding *1 (multiply by 1) at the end of your conditional formula
0
Answer 10 / 15
swande Posted messages 7 Status Member
 
Sure! Please provide the text you would like me to translate.
0
Answer 11 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Re,

Yes, but except that you haven't attached your file, click on this link, then browse to select your file, create the link and paste the generated link in a response
https://www.cjoint.com/
--
See you later
Mike-31

A period of failure is a perfect time to plant the seeds of knowledge.
0
swande Posted messages 7 Status Member
 
Thank you for your help, I'm getting used to the site.
0
Answer 12 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Re,

If I understand your request correctly, try this code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address(0, 0) = "C3" And Target = 1 Then Shapes("Checkbox 1").Visible = True '
If Target.Address(0, 0) = "C3" And Target <> 1 Then Shapes("Checkbox 1").Visible = False
End Sub

If this is not what you are looking for, explain your expectations
--
Cheers
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.
0
swande Posted messages 7 Status Member
 
It's not quite that.

I'm attaching a file with exactly what I would like and with the correct macro based on cell D5. I can't understand why the checkbox doesn't appear since cell D5 <>""

https://www.cjoint.com/c/FBkvWeuakHf
0
Answer 13 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Hi,

your code can't work because the Worksheet_Change(ByVal Target As Range) action that occurs on cell C3, C5 as well as D5 is the result of a formula, not the Change action.

--
See you +
Mike-31

A period of failure is a perfect time to plant the seeds of knowledge.
0
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Re,
Remove the unnecessary formula in D5, replace your code with this one and test 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [C5] = "Amendment" Then Shapes("Checkbox 1").Visible = True '
If [C5] <> "Amendment" Then Shapes("Checkbox 1").Visible = False
End Sub


And if you want to test cell C3 which makes more sense because it's that cell that conditions C5 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [C3] = 1 Then Shapes("Checkbox 1").Visible = True '
If [C3] <> 1 Then Shapes("Checkbox 1").Visible = False
End Sub
0
swande Posted messages 7 Status Member
 
Fantastic! It works perfectly and thank you for the little course on Excel events.
0
swande Posted messages 7 Status Member
 
However, the Ctrl Z key no longer works.
0
Answer 14 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
RE,

it has nothing to do with the code, Ctrl and Z correspond to Undo last action and Ctrl and Y corresponds to Redo last action
once again the VBA code has nothing to do with this problem restart your computer see

--
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.
0
swande Posted messages 7 Status Member
 
https://www.cjoint.com/c/FBlrfbqh4Jf

I believe it has to do with the code, because when I remove the macro, my Ctrl Z works.... For example, in the file, if I put $10,000 in cell B8 and I want to calculate based on an amount of $15,000 and then revert to the $10,000, I cannot do it with Ctrl Z (even Ctrl Y does not work).

I have already restarted the computer and tested Ctrl Z on a blank file, and everything works. So, I'm having a hard time explaining why.
0
Answer 15 / 15
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 146
 
Re,

Modify the code like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [C3]) Is Nothing Then
If [C5] <> "" Then Shapes("Checkbox 1").Visible = True '
If [C5] = "" Then Shapes("Checkbox 1").Visible = False
End If
End Sub

--
A+
Mike-31

A period of failure is a perfect time to plant the seeds of knowledge.
0
swande Posted messages 7 Status Member
 
Indeed, it works! The only thing is that I have to select the box with my cursor, but that's just a detail.

Thank you a thousand times!!!!
0