PDA

View Full Version : Solved: only partly activity in Evene_Change



AnswerSeeker
04-03-2012, 12:06 AM
Hello all,

I have a form, where in B7 the current month and year is displayed. In case the user want to input his data for another date, then he/she has to input the desired date in B5 from where it is taken over to B7.
However, in case the user wants the current date, he/she has just to delete the content in B5 and B7 should show the current date automatically. But here nothing is happening, but only in case of date input in B5?

Private Sub Worksheet_Change(ByVal Target As Range)

otherdate = Range("B5").Value

Select Case Target.Address = "$B$5"
Case True
Select Case IsEmpty(Range("B5"))
Case True
Range("B7").Value = Format(Date, "mmm-yy")
Case Else
Range("B7").Value = otherdate
End Select
End Select

End Sub

thx for any help

Bob Phillips
04-03-2012, 12:57 AM
It seems to work for me but you might want to make the following small change



Private Sub Worksheet_Change(ByVal Target As Range)
Dim otherdate As Date

otherdate = Me.Range("B5").Value

Select Case Target.Address = "$B$5"
Case True
Select Case IsEmpty(Target.Value)
Case True
Me.Range("B7").Value = Date
Case Else
Me.Range("B7").Value = otherdate
End Select
Me.Range("B7").NumberFormat = "mmm-yy"
End Select

End Sub

AnswerSeeker
04-03-2012, 01:13 AM
I got the same result (doing nothing), but I found out I have to press the 'backspace' button rather than the 'delete' button