Dropdown behaviour shows different data depending on selection

[TheJay]

FIrst of all, apologies for the title, I don't know how to change it and realised I wanted to use a tick box instead of a drop down as I was writing.

I have a spreadsheet which calculates the charge for a date range.

Now I want to introduce a tick box in C6, which changes whether the inclusive or exclusive end is shown in C7 and relevant data in C8. As well as the data changing, when the tick box is clicked, I'd like the text in C6 to change from 'Exclusive End' to 'Inclusive End'.

The idea is that I don't want anything in row D because I need to incorporate other data eventually in that row. Not sure how this is achieved, can it be done without hidden cells?

The forum wont let me attach the spreadsheet

 

[TheJay]

Also, I couldn't figure out how the text can change from exclusive to inclusive.

 

[TheJay]

Okay, when I tested it again it is working fine (no errors).

https://imgur.com/fZh6F2A

Still can't produce the behaviour expected with the text changing. I added a custom format to the cell:

https://imgur.com/vusSopo

No matter whether it is checked or not, the text does not change. What am I doing wrong?

https://imgur.com/MuzUr2l

 

[jasonb75]

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]

Thank you for your reply. I have never worked with conditional formatting before and didn't understand it at all.

 

[TheJay]

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]

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]

Thanks for your reply. I like the appearance of the text, which I moved into the column with all the other text.

https://imgur.com/VrZYDuS

VBA is the only way to make the checkbox invisible?

 

[jasonb75]

VBA is the only way to make the checkbox invisible?

It is, more specifically, you would need a worksheet change event, something like this.

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]

Thanks for your reply.

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

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]

I expect that it should be "Check Box 1" not "Check Box1 " (space in the wrong place makes a difference).