Only allowing values to be copied between certain dates?

[Simon Lloyd]

Hi all, i have some code that i want to run only if the offset value of the cell i'm looking at falls between 2 dates supplied by textboxes, TB1 is the from date TB2 is the to date, below is the statement i am using but will only work if i set both checks to<= and then the if statement seems to be true for every date!


If Mycell.Value = ComboBox1.Value And Mycell.Offset(0, 1).Value >= _
Me.TextBox1.Value And Mycell.Offset(0, 1).Value <= _
Me.TextBox2.Value Then


Any ideas or suggestions why?

Regards,
Simon

P.S the TB's are on a userform and all the code resides in the CommandButton_Click in the userform module!

[Bob Phillips]

If MyCell.Value = ComboBox1.Value And _
MyCell.Offset(0, 1).Value >= CDate(Me.TextBox1.Value) And _
MyCell.Offset(0, 1).Value

[Bob Phillips]

If MyCell.Value = ComboBox1.Value And _
MyCell.Offset(0, 1).Value >= CDate(Me.TextBox1.Value) And _
MyCell.Offset(0, 1).Value <= CDate(Me.TextBox2.Value) Then
MsgBox "yes"
End If

[Simon Lloyd]

still having trouble with your account Bob?, thanks for the reply, what is CDate and its function?

Regards,
Simon

[Bob Phillips]

Sure am. CDate casts a value to a date, so is vital in VBA (IMO) to ensure that we don't all get corrupted by US style dates.

[Simon Lloyd]

LOL!, they've corrupted everything else we have and do!, am i right in thinkng that the textbox (regardless of the format a value was typed in) holds the date as a serial number (or is it just a plain number?) but excel doesnt recognise it as a date serial number when checking?, i did use Textbox1.Value=Format(Textbox1.value, "dd/mm/yyyy") to get the date to conform as i thought that was the problem, however if i used that in the TextBox1_Change it gave some odd results, it seemed that the very first digit i typed in the textbox produced a 1900 date but would not allow change.

Regards,
Simon

[Bob Phillips]

No, the textbox holds it as a string, pure and simple. In some circumstances, that string can be treated as a number, but it is never sure, so it is always best to cast it to whatever data type it should be.