Excel: Difference between 2 ComboBox Dates 1

[MeGustaXL]

Hello All

Among the 20 or so controls on a UserForm I have 2 ComboBoxes: cboDateON and cboDateOFF, and a TextBox txtDaysFitted.

the cbo's are populated at Initialise() from a list of dates like this:

01 Jan 03
02 Jan 03
03 Jan 03
04 Jan 03
05 Jan 03
06 Jan 03
07 Jan 03
.........etc.
If that list is named "Date", I've done this:

With cboDateON
.RowSource = [Date].Address
.ListIndex = 0
End With
With cboDateOFF
.RowSource = [Date].Address
.ListIndex = 0
End With

What I'd like to do is a kind of DATEDIF thing to populate txtDaysFitted, something like this:

Private Sub cboDateOFF_Change()
txtDaysFitted.Value = (cboDateOFF.Value) - (cboDateON.Value)
End Sub

Everything I've tried so far, including CDate(cboDateON) returns an error of some sort, usually "Type Mismatch".

Can you give me a clue as to the correct way of doing this calculation?

P.S. I'm still working on a way to only do it when there's a value in both cbo's!

Chris

 

[Bowers74]

I am assuming that Excel (or VBA) is interpreting the dates in the COmboBoxes as Text, so try this:

Private Sub cboDateOFF_Change()
txtDaysFitted.Value = DateValue(cboDateOFF) - DateValue(cboDateON)
End Sub

/color]

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

Chris,

don't add the /color] to the end of your code! ;-)

I was a little too excited and pressed "Submit Post" before I proof-read it.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

Nope, Sorry Mike, that gives me a Type Mismatch as well

I should mention that although the cbo's display the dates as printed above, once you select one, the value displayed is a number like 35678. That's why I thought I could just subtract one from the other.

BTW, where do you guys learn all this stuff - DateValue???

Chris

 

[SkipVought]

Hi,

Actually, VBA is probably reading the dates as dates in your combobox

txtDaysFitted.Text = cboDateOFF.Value - cboDateON.Value

Use your Watch Window to observe the value of cboDateOFF. you can learn alot!

;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MeGustaXL]

Cowabunga, Skip! That's fixed it nicely

I really can't wait until the postman delivers my copy of JW's book "XL 2000 Bible"; then I'll (maybe) remember the difference between .Value and .Text

BTW: The Watch Window didn't help in this case, it just says "Out of Context"

BTW2: It kinda blows that the dates are displayed as numbers when they're selected, but look like "dd mmm yy" in the drop-down list; any clues as to how/where to apply the formatting? (sheepish grin and fluttering eyelashes)

Chris

 

[SkipVought]

You can select the appropriate context in the Add or Edit window INCLUDING All Procedures and All Modules

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MeGustaXL]

Yep, got it now. Thanks again Skip!

Chris