Solved: msgbox display

[av8tordude]

I'm trying to create a message box that will look at the date in cell A1 and display the appropriate message. Can someone assist, Thank you in advance.

Fiscal Year 2011 starts 10-31-2011 and ends 09-30-2012.

If cell A1 has any dates between the two dates above, I would like the msgbox to display...

"Only 2011 database can be downloaded."

[msuresh]

Is this you are looking for

[VBA]Sub test()
Dim tempdate As Date
tempdate = Range("A1")
If tempdate >= "10 / 31 / 2011" And tempdate <= "9 / 30 / 2012" Then
MsgBox "Only 2011 database can be downloaded"
End If
End Sub[/VBA]

[av8tordude]

Not quite...here's what I managed to put together. The first part of the code works, I just need the msgbox box is Bold to be displayed.

In the listbox (See attachment), 2009, 2010, 2011, 2012 is displayed. Each fiscal year starts from 10/1 of the selected year and ends 09/30 of the following year. ( i.e. if 2010 is selected, the fiscal year starts 10/01/2010 and ends 09/30/2011).

If 2012 is selected in the listbox and 11/01/2009 is in cell B11, I would like the msgbox to display the following message…"Only 2010 database can be downloaded."

If the code below determines the current date in cell B11 falls in between the year selected in the list box (In this case, 2010), then a msgbox would display “Downloaded”. If any other date that falls outside the selected Fiscal year of the selected 2010 (i.e. 2009, 2011, 2012), then it should display a message box that says "Only 2010 database can be downloaded."

Legend:
Cur_Row, 2 = current row, column B
lstFYFiles.Column(1, lstFYFiles.ListIndex) = listbox column 2


[vba]If CDate(Cells(Cur_Row, 2)) > lstFYFiles.Column(1, lstFYFiles.ListIndex) And _
CDate(Cells(Cur_Row, 2)) < CDate("09/30/" & Format(lstFYFiles.Column(1, lstFYFiles.ListIndex), "yyyy") + 1) Then
Msgbox “ Downloaded”
Else
Msgbox "Only 2010 database can be downloaded."
end if[/vba]

[shrivallabha]

See if this approach works for your (untested):
[vba]Select Case Month(Cells(Cur_Row, 2).Value)
Case Is <= 9
If Year(Cells(Cur_Row, 2).Value) <> lstFYFiles.Column(0, lstFYFiles.ListIndex) Then
MsgBox "Only " & Year(Cells(Cur_Row, 2).Value) & " database can be downloaded."
Else
MsgBox "Downloaded"
End If
Case Else
If (Year(Cells(Cur_Row, 2).Value) + 1) <> lstFYFiles.Column(0, lstFYFiles.ListIndex) Then
MsgBox "Only " & (Year(Cells(Cur_Row, 2).Value) + 1) & " database can be downloaded."
Else
MsgBox "Downloaded"
End If
End Select[/vba]

[av8tordude]

Thanks you shrivallabha. After a little tweeking, I managed to accomplish what I was looking for. Just out of curiosity, what does this do...

[VBA]Case <=9[/VBA]

[shrivallabha]

Thanks you shrivallabha. After a little tweeking, I managed to accomplish what I was looking for. Just out of curiosity, what does this do...

[vba]Case <=9[/vba]

As you have set your layout:
1. The fiscal year (yyyy) starts from 10/1/yyyy - 1 and ends on 9/30/yyyy
e.g. 2010 begins from 10/1/2009 and ends on 9/30/2010.

2. So for first 9 months it will be the same year and 10-12 it will be previous year.

So we have implemented case check here:
[VBA]Select Case Month(Cells(Cur_Row, 2).Value)[/VBA]

Alternatively If Else would also work like:
[VBA]If Month(Cells(Cur_Row,2).Value) < 10 then
'The other if else here
Else
'The other if else here
End if[/VBA]