List box messing up controls (1 Viewer)

[agehoops]

Whenever I click into a list box and select an item, it changes all of the text box controls to " #Error "

The text box controls have a control source of the following:

=Format("3/1/"+[cboYear].[Text],"dddd")+" 3rd"

But slightly different for each one. Why when I click in the list box to these suddenly change? And how do I correct it?

Thanks
Adrian

 

[MarkK]

Maybe: the Text property of a control is only available when that control has the focus. It is very unusual to reference this property in VBA. Use the Value property, or since that is the default, you can just use the name of the control.

 

[agehoops]

I don't think it's due to the focus. Basically, the user selects a year at the top of the form, and then the text boxes use the value selected from that combo box, and change their value accordingly, however the user may suddenly change the year, so I can't set it as a default value?

 

[MarkK]

Your call. This:

cboYear.Text

returns an error if cboYear does not have the focus. Try this...

=Format("3/1/" & [cboYear], "dddd") + " 3rd"
 -or-
=Format("3/1/" & [cboYear].[Value], "dddd") + " 3rd"

 

[agehoops]

WOOHOO!!!! Thank you so much!! The first one worked (without the .[text]) works a treat, thank you! Was really annoying me! I'm making the system for uni and that's in about 2 weeks and it was just slowing me down so yea. Really appreciate it

 

[MarkK]

WOOHOO! Right on man.