Solved: Manual override via check box

[Aussiebear]

I have a two part problem which requires a greater mind than mine, ( so what's new I hear you ask?)

The attached spreadsheet works fine in its company form, however I firstly need on the Contracts sheet a method of adding a checkbox in column K everytime I add a new Contract. How is this done?

Secondly the checkbox is needed to override the formula in column E of the Whiteboard sheet. The reason being that head Office sometimes declare a contract closed, for reasons not indicated to us mere mortals at the bottom of the employee food chain, even though it has not reached a value equal to or greater than the indicated contract tonnage. Can this be done as well?

Ted

[Aussiebear]

Sorry guys, the origonal uploaded file is missing the formula in column C of the contracts sheet- =If(I3>=H3,"Yes,"No"). Somewhere in preparing the workbook, I missed including the formula.

The formula in column E of the Whiteboard sheet checks the column C value of the relevant contract is see if it is closed or not. My wanting the check box is to override the formula in column C.

[Bob Phillips]

I would ditch the checkbox, and set the column font to Marlett. typing an a (lower-case A) in the cell then gives a tick mark. In Whiteboard you can then use that value and test in the formula.

[Aussiebear]

Ok Thanks Bob

[Aussiebear]

Bob, I've changed the font type to Marlett for column K and changed the formula in column C to =IF(K3<>"","Yes",IF(I3>H3,"Yes","No")) and it seems to work fine. All I do now is add a tick to the relevant cell to manualy close the contract.

Thank you for your assistance

Ted

[mdmackillop]

Hi Ted,
Expanding on Bob's suggestion, you could change format automatically if other text may be entered in the tick cells, using a limited Target range of course.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "a" Then
Target.Font.Name = "Marlett"
Else
Target.Font.Name = "Arial"
End If
End Sub

[Aussiebear]

G'day MD. Might be getting a little too complicated with your suggestion for what my need is, however in following your thinking here... does this mean that the target value needs to be only a single "a" to be regarded as formatting the text as Marlett or would the same thing occur if the target value was, say.. "a case of apples"

[mdmackillop]

Be adventurous Ted. Give it a try in a "practice" worksheet!.

[Bob Phillips]

I think I agree with Ted here, a change event is overkill for what he is doing. That is why i didn't suggest it.

[Aussiebear]

MD, I might, but not on this occassion. As Bob suggests, this change he advised on does the job for me. I had a number of non achieving contacts sitting there, because head office had closed them for what ever reason. Now when these contracts appear I can kill them off simply by using the formatted cell to over ride the formula which until last night was simply relying on the vendor dec tonnage to be equalled or passed.

The company makes no effort to reward me either financially or with words of encouragement, so if I can paddle across the swamp with a paperbark canoe rather than having to rebuild the Queen Mary, then that's the way I'd rather go.

I'll have a go using your method in another spreadsheet.

Ted

[mdmackillop]

The company makes no effort to reward me either financially or with words of encouragement
I know the feeling. Overkill accepted, but the basic principle is useful to have. Most of my coding at work is for my benefit, not for "distribution".

[lucas]

I know the feeling. Overkill accepted, but the basic principle is useful to have. Most of my coding at work is for my benefit, not for "distribution".

Me too. With that in mind, this is what I use:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim CheckmarkCells As Range
Set CheckmarkCells = Range("I2:I12")
If Not Intersect(Target, CheckmarkCells) Is Nothing Then
Target.Value = ""
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CheckmarkCells As Range
Set CheckmarkCells = Range("I2:I12")
If Not Intersect(Target, CheckmarkCells) Is Nothing Then
Target.Value = "a"
Target.Font.Name = "Marlett"
End If
End Sub


click in the range for a checkmark...doubleclick to remove

[mdmackillop]

Hi Steve
Save a click. How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CheckmarkCells As Range
Set CheckmarkCells = Range("I2:I12")
If Not Intersect(Target, CheckmarkCells) Is Nothing Then
If Target.Font.Name = "Marlett" Then
Target.ClearContents
Target.Font.Name = "Arial"
Else
Target.Value = "a"
Target.Font.Name = "Marlett"
End If
End If
End Sub

[lucas]

I like it....thanks Malcolm

[mdmackillop]

Hi Ted,
Apologies for the thread hijack.
:bump:

[Bob Phillips]

Hi Steve
Save a click. How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CheckmarkCells As Range
Set CheckmarkCells = Range("I2:I12")
If Not Intersect(Target, CheckmarkCells) Is Nothing Then
If Target.Font.Name = "Marlett" Then
Target.ClearContents
Target.Font.Name = "Arial"
Else
Target.Value = "a"
Target.Font.Name = "Marlett"
End If
End If
End Sub


If you use the SelectionChange then I think it is a good idae to shift to another cell, so that the same cell can be selected again if it was done by mistake. Just a tad friendlier IMO.

[lucas]

I apologize also Ted but somtimes good things come from these exchanges. That's a great idea Bob. It makes it friendlier yet, as you said:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CheckmarkCells As Range
Set CheckmarkCells = Range("I2:I12")
If Not Intersect(Target, CheckmarkCells) Is Nothing Then
If Target.Font.Name = "Marlett" Then
Target.ClearContents
Target.Font.Name = "Arial"
' Range("G16").Select
Target.Offset(0, 1).Select
Else
Target.Value = "a"
Target.Font.Name = "Marlett"
' Range("G16").Select
Target.Offset(0, 1).Select
End If
End If

End Sub

[mdmackillop]

Hi Steve,
Try selecting rows 1 to 20.

[Aussiebear]

You two call that a hijack? Not me. That was simply a speedbump on the road to information.

[lucas]

Yeah, I noticed that Malcolm....goes all the way down the colulmn. It did that before the cell select was added though and I think I can personally live with it. Any suggestions of a way around it?

[mdmackillop]

Any suggestions of a way around it?
Just check the range size first.

If target.cells.count>1 then exit sub

[lucas]

So simple and efficient......that just about resolves my issues with this. Thanks Malcolm.

[Bob Phillips]

Or just pick up the first cell



With Target.Cells(1, 1)