Gantt Chart (1 Viewer)

Jonny45wakey

Member
Local time
Today, 05:35
Joined
May 4, 2020
Messages
40
Hi all

Hoping someone could help with a little structuring of a formula in a text box i have which uses dates in other text boxes to populate a Gantt chart (image attached)

When dates are entered in the reference date text boxes the formula in each of the squares representing each week of the year will change colour using the following formula in the text box control source = =IIf(DateValue([Finish Date1])>=[Text1159]+7*0,IIf(DateValue([Start Date1]<=[Text1159]+6+7*0,"True","False"),"False")

The conditional formatting of the text box adds the blue colour shading if true condition is met, what i would like is to get rid of the Error messages you see below

#Name?

Thanks

Jonny

gantt.jpg
 

cheekybuddha

AWF VIP
Local time
Today, 05:35
Joined
Jul 21, 2014
Messages
2,237
Hi,

Perhaps test that you have dates for all you inputs first:
= =IIf(IsDate([Finish Date1]) AND IsDate([Start Date1]) AND IsDate([Text1159]), IIf(DateValue([Finish Date1])>=[Text1159]+7*0,IIf(DateValue([Start Date1]<=[Text1159]+6+7*0, True, False), False), False)
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:35
Joined
Oct 29, 2018
Messages
21,358
Hi Jonny. Belated welcome to AWF!
 

Jonny45wakey

Member
Local time
Today, 05:35
Joined
May 4, 2020
Messages
40
Thanks cheekybuddha, i tried your suggestion but got the following:-

1591369152706.png
 

plog

Banishment Pending
Local time
Today, 00:35
Joined
May 11, 2011
Messages
11,613
Cheeky is missing a right parenthesis, but I think that's only going to fix the #Type errors not the #Name errors.

Are you sure [Text1159] is a valid reference? Compare the code In Row 10 Day 19 to Row 10 Day 20. Ones a #Type and the other is #Name. The answer is there.
 

cheekybuddha

AWF VIP
Local time
Today, 05:35
Joined
Jul 21, 2014
Messages
2,237
>> Cheeky is missing a right parenthesis <<
True, but copied from OP - apologies, didn't check my inputs! 😬
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:35
Joined
May 7, 2009
Messages
19,169
you can also use:

=Switch(DateValue(Nz([Finish Date1],0))>=[Text1159]+7*0 And DateValue(Nz([Start Date1],0)<=[Text1159]+6+7*0, "True", True, "False")
 

Jonny45wakey

Member
Local time
Today, 05:35
Joined
May 4, 2020
Messages
40
Hi all, thanks for the help but still cant sort the #Type! error!

The Textbox control source looks as follows:
1591622490817.png


Text 1159 refers to the following calculation in textbox1159

1591622544812.png


Essentially the #Type! error occurs when there are no dates entered into [Start Date6] and [Finish Date6] text boxes, what i need is something to leave textbox2337 blank if no dates in these text boxes?

1591622824751.png


Thanks in advance for further help

Jonny
 

Attachments

  • 1591622796776.png
    1591622796776.png
    142.5 KB · Views: 132

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:35
Joined
May 7, 2009
Messages
19,169
add a Public function to your form:


Code:
Public Function CellValue(byval StartDate As Variant, Byval EndDate As Variant) As Variant
StartDate=Trim(StartDate & "")
EndDate = Trim(EndDate & "")
If StartDate = "" Or EndDate = "" Then
    Exit Function
End If
StartDate = DateValue(CDate(StartDate))
EndDate = DateValue(CDate(EndDate))
CellValue=(EndDate >= ([Text1159]+7*0) And StartDate <= ([Text1159]+6+7*0))
End Function

on Each Unbound textbox:

=CellValue([Start Date1], [End Date1])
=CellValue([Start Date2], [End Date2])
=etc...
 

Dreamweaver

Well-known member
Local time
Today, 05:35
Joined
Nov 28, 2005
Messages
2,466
Hi Jonny45wakey

Correct me if I'm wrong but you are adding unbound controls a line at a time at least thats what it looks like in post 11 Your OP didn't show it.

if you are you will run out of controls at around 750

why all the mess with the Expressions you could very easly do what you need with a loop.

I've built a number of this type of object and would never use your approch.

mick
 

Jonny45wakey

Member
Local time
Today, 05:35
Joined
May 4, 2020
Messages
40
Hi Jonny45wakey

Correct me if I'm wrong but you are adding unbound controls a line at a time at least thats what it looks like in post 11 Your OP didn't show it.

if you are you will run out of controls at around 750

why all the mess with the Expressions you could very easly do what you need with a loop.

I've built a number of this type of object and would never use your approch.

mick


Hi MickJav

Are you able to share any of these objects so i can see if i can apply these to my project?

Thanks

Jonny
 

cheekybuddha

AWF VIP
Local time
Today, 05:35
Joined
Jul 21, 2014
Messages
2,237
Essentially the #Type! error occurs when there are no dates entered into [Start Date6] and [Finish Date6] text boxes, what i need is something to leave textbox2337 blank if no dates in these text boxes?
Did you try with the correction made to the expression I suggested in post#2 ?
Code:
=IIf(IsDate([Finish Date1]) AND IsDate([Start Date1]) AND IsDate([Text1159]), IIf(DateValue([Finish Date1])>=[Text1159]+7*0,IIf(DateValue([Start Date1])<=[Text1159]+6+7*0, True, False), False), False)
 

cheekybuddha

AWF VIP
Local time
Today, 05:35
Joined
Jul 21, 2014
Messages
2,237
Out of curiosity, what does this part of the expression mean?
DateValue([Start Date1])<=[Text1159]+6+7*0

Surely it's thsame as writing:
DateValue([Start Date1])<=[Text1159]+6

??
 

Users who are viewing this thread

Top Bottom