Conditional formatting with like (1 Viewer)

[guinness]

Thanks vbaInet

That's brilliant and I'll mark this thread as solved as a result. I do have two questions however.

When I use the format wizard I can ask it to check two separate conditions so that if the field contains the word Delivery the background turns yellow and a second condition that says if the cell equals Delivery (with no other text) then the forecolor also turns yellow. This way I can have an event lasting five days. On day one it will say Delivery: Course name and on the remaining four days the box is just shaded yellow (at present boxes 2-4 say Delivery). I tried writing this:

.Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Delivery*" & Chr(34) & _
" Or " & _
"[" & ctl.Name & "] Like " & Chr(34) & "*Assessment*" & Chr(34)
.Item(0).Backcolor = vbYellow
.Add acExpression, , "[" & ctl.Name & "] = " & Chr(34) & "Delivery" & Chr(34) & _
" Or " & _
"[" & ctl.Name & "] = " & Chr(34) & "Assessment" & Chr(34)
.Item(0).ForeColor = vbYellow
.Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Design*" & Chr(34)
.Item(1).Backcolor = vbGreen
.Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Holiday*" & Chr(34)
.Item(2).Backcolor = vbRed

Without the code in bold everything works as expected. With the code in bold only delivery and assessment work and all text is in yellow. Any ideas?

Also with the code the form takes around two minutes to open. Is there any way to speed this up? I realise that it's quite a lot of formatting.

With your help I'm very close to being able to move on from this form and get on with the real work of the database.

Thanks again for all your help.

Guinness

 

[vbaInet]

Without the code in bold everything works as expected. With the code in bold only delivery and assessment work and all text is in yellow. Any ideas?

The order of the conditions is vital. Once it finds an expression that meets the condition(s) it will stop looking and in your case the first one met the condition. So you need to move this one to the top as the first expression.

Also with the code the form takes around two minutes to open. Is there any way to speed this up? I realise that it's quite a lot of formatting.

Conditional formatting alone is quite processor intensive and you already hundred tonnes of controls to format, the code alone is fast but it's setting the formatting that's taking time. The only small step (it won't help much or if at all) I can advise is to restrict the search to the Details section alone, which I believe is the section where all your textboxes reside:

For Each ctl In Me[COLOR="Blue"].Detail.Controls[/COLOR]

NB: When next you post code, you can use code tags (so it looks nice, neat and legible like the one I posted). Here's how:
http://www.access-programmers.co.uk/forums/showthread.php?t=200247

 

[guinness]

Thanks vbaInet

You have really helped. One last question if you don't mind. Could I also set the column width within that code? As the code Identifies all the date fields it would be good to get it to set the column width at the same time.

I know the format is .ColumnWidth = xx but don't know where to put that within the code.

Cheers

Guinness

 

[vbaInet]

If it's in Datasheet view then yes. Give it a try and see the result.

Each cycle in the loop is for each control that has a date type name.

 

[guinness]

Thanks vbaInet

The problem is that I'm struggling with how to write the column width in to the code.

The code at the moment looks like:

 For Each ctl In Me.Detail.Controls
        If ctl.ControlType = acTextBox And IsDate(ctl.Name) Then
            With ctl.FormatConditions
                .Delete
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Delivery*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Assessment*" & Chr(34)
                .Item(0).Backcolor = vbYellow
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Design*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Meetings*" & Chr(34)
                .Item(1).Backcolor = vbGreen
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Holiday*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*RDO*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Authorised absence*" & Chr(34)
                .Item(2).Backcolor = vbRed
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Scoping*" & Chr(34)
                .Item(3).Backcolor = RGB(255, 204, 153)
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Team Meetings*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Admin*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Travel*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Course prep*" & Chr(34)
                .Item(4).Backcolor = RGB(102, 102, 102)
                
            End With
        End If
    Next
End Sub

I imagine that it will be

 .Item(5).ColumnWidth= xx

but what would be the expression?

 

[vbaInet]

I see you've added lots more expressions. Your form must be really slow now.

I'll give you a hint. ctl is the textbox. Even though Intellisense doesn't suggest ColumnWidth when you type a dot after "ctl", it's still a valid method.

 

[guinness]

Yes, vbaInet the form is ridiculously slow to load. About three minutes.

I appreciate the hint but I'm afraid I don't have the skills to understand it.

I know that excel is probably the better solution and I may eventually have to go that route but that will cause me a whole new set of issues as I need this display to be available and manipulated in Access.

I guess I've bitten off way more than I can chew but I have learned a lot along the way.

Thanks as always

Guinness

 

[vbaInet]

I'm not surprised!

ctl.ColumnWidth

 

[guinness]

Still stuck. Where do I put this? I tried

For Each ctl In Me.Detail.Controls
        If ctl.ControlType = acTextBox And IsDate(ctl.Name) Then
            With ctl.FormatConditions
                .Delete
                [B]ctl.ColumnWidth = 5760
[/B]                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Delivery*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Assessment*" & Chr(34)
                .Item(0).Backcolor = vbYellow
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Design*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Meetings*" & Chr(34)
                .Item(1).Backcolor = vbGreen
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Holiday*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*RDO*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Authorised absence*" & Chr(34)
                .Item(2).Backcolor = vbRed
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Scoping*" & Chr(34)
                .Item(3).Backcolor = RGB(255, 204, 153)
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*Team Meetings*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Admin*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Travel*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*Course prep*" & Chr(34)
                .Item(4).Backcolor = RGB(102, 102, 102)
                
            End With
        End If
    Next
End Sub

But the column width didn't change and the colours stopped working.

 

[vbaInet]

Is this for a Datasheet view?

 

[guinness]

Yep. The default view is datasheet

 

[vbaInet]

Ok the default view is Datasheet but do you open it as a Datasheet?

 

[guinness]

Yep. It loads in datasheet view

 

[vbaInet]

I think it's doing what you want. You don't see it doing anything simply because it's done it before you can see it. But just to be completely sure, I'll move it up here:

ctl.ColumnWidth = 5760
With ctl.FormatConditions

 

[guinness]

That worked. Yipee

 

[vbaInet]

Woopee!!!!

 

[guinness]

Just a footnote to this.

As previously stated after applying the conditional formatting the form was ridiculously (unacceptably) slow to load.

By sheer accident however I discovered that if I put this form into another form as a subform things worked differently. When I tried to open the new form the debugger kicked in instantly with the warning that "Access cannot find the referenced item form "2014 resources"". This is correct as I was now using a subform however selecting "End" rather than "Debug" opened the form instantly with all the required formatting.

I tried adding

[SIZE=3][FONT=Calibri]On Error Resume Next[/FONT][/SIZE]

However this just slowed it back down. It only takes seconds to click "End" to the debugger but I suppose my question is, is there a way to write VBA that causes the debugger to End and also something to restart it after the form has run?

 

[vbaInet]

I'm not sure why you're spending more time on this trying to figure out a way to speed something I've already explained to you will not get any faster. If there was a way to speed it up I or someone else on here would have mentioned it.

If you hit End any code after the line where it broke just won't run. That's why it's called End.

It hasn't fully added the Format Conditions before it broke hence the deceiving speed.