Hi,

I'm trying to use VBA to change the date format of a cell when a
preceeding cell is changed. I am using data validation so it is a list
box, and the contents of the list box change depending on if the
variable "annual" or "monthly" is selected:

=IF($N$3="Annual",DATE_RANGE_ANN,DATE_RANGE_MON)

Here is the code behind the sheet I am trying to run when cell(N3) is
selected:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$N$3" Then

If Target.Value = "Annual" Then

Target.Offset(0, -1).NumberFormat = "yyyy"

Else

Target.Offset(0, -1).NumberFormat = "mmmm-yyyy"

End If

End If

End Sub


I should point out that both cells N3 and N4 are merged cells. I was
not sure if that would create a problem. Any help is greatly
appreciated...thanks.

Try Worksheet_Change instead of Worksheet_SelectionChange.

"Packman" wrote:

Hi,

I'm trying to use VBA to change the date format of a cell when a
preceeding cell is changed. I am using data validation so it is a list
box, and the contents of the list box change depending on if the
variable "annual" or "monthly" is selected:

=IF($N$3="Annual",DATE_RANGE_ANN,DATE_RANGE_MON)

Here is the code behind the sheet I am trying to run when cell(N3) is
selected:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$N$3" Then

If Target.Value = "Annual" Then

Target.Offset(0, -1).NumberFormat = "yyyy"

Else

Target.Offset(0, -1).NumberFormat = "mmmm-yyyy"

End If

End If

End Sub


I should point out that both cells N3 and N4 are merged cells. I was
not sure if that would create a problem. Any help is greatly
appreciated...thanks.

Still nothing....

Where is your code ? It should be in worksheet where the changed cells are
(right-click == View code== add code).

In my test, I have N3/N4 merged with a drop down with "Annual", "Monthly".

In M3, I have a named range of dates and the format changes according to the
selection in N3.

Is this what you are expecting?

"Packman" wrote:

Still nothing....

I should have explained this a bit better on the merging...my
apologies. And yes, I have the code behind the actual worksheet.

Cells N3 and O3 are merged and have a data validation list where you
can select Annual or Monthly.

Cells N4 and O4 are merged and contain a data validation list which
changes to either show a list of years or a list of months. In the
data validation properties for this cell, I have the following formula:

=IF($N$3="Annual",DATE_RANGE_ANN,DATE_RANGE_MON)

So, all I'm really trying to do is get the date format to change in the
N4/O4 cell when the annual/monthly option in N3/O3 is changed.

Hope this makes sense and thanks for taking the time to try to help...