Dropdown behaviour shows different data depending on selection

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Also, I couldn't figure out how the text can change from exclusive to inclusive.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
Probably the empty cells. If you enter valid start and end dates, then it should work.

You need custom format for when the box is unchecked ;;;"Exclusive"
and custom conditional format for when the box is checked ;;;"Inclusive" (see post #5)
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Thank you for your reply. I have never worked with conditional formatting before and didn't understand it at all.
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Is there a way to make the checkbox invisible until data is entered into one of the cells? I can't see a way of using the usual =IF(ISBLANK(?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
You could make the text behind the checkbox invisible with conditional formatting, but hiding the actual checkbox would need vba.

Add a new conditional formatting rule to check if the data cell is empty, i.e. =D1="" (change D1 to the reference of the data cell.
Set the text colour to white (or the same colour as the cell background).
Make sure that this rule is the first in the list and that the 'stop if true' box is ticked.
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Thanks for your reply. I like the appearance of the text, which I moved into the column with all the other text.


VBA is the only way to make the checkbox invisible?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
VBA is the only way to make the checkbox invisible?
It is, more specifically, you would need a worksheet change event, something like this.
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address(0, 0)
        Case "A1"
            ActiveSheet.Shapes("CheckBox1").Visible = (Len(Target.Value)>0)
        Case "B1"
            ActiveSheet.Shapes("CheckBox2").Visible = (Len(Target.Value)>0)
    End Select
End Sub
The code above shoud hide checkbox 1 when A1 is empty and make it visible when A1 is not empty, then the same for checkbox 2 and B1. More can be added as needed.
Note that the code will not 'correct' the initial state of the checkboxes, if they are already visible while the corresponding cell is empty. In such cases you would need to enter something into the cell, then delete it for the code to be triggered.
 

TheJay

Board Regular
Joined
Nov 12, 2014
Messages
141
Thanks for your reply.

The sheet is called Calculator and the checkbox appears to be called Check Box 1.

VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address(0, 0)
        Case "C7"
            ActiveSheet.Shapes("Check Box1 ").Visible = (Len(Target.Value) > 0)
    End Select
End Sub
At the moment I think I haven't done something correctly as this isn't working. I'm not sure what else I need to add/amend.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
I expect that it should be "Check Box 1" not "Check Box1 " (space in the wrong place makes a difference).
 

Forum statistics

Threads
1,078,286
Messages
5,339,303
Members
399,292
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top