The code below creates a ComboBox consisting of all of the
first (day) in month dates in between a start date and an
end date. I am having problems with the formatting of the
ComboBox control. When I click on the drop down, I see all
of the dates in the correct date format (e.g. Jan-00, Feb-
00, etc). However, when I select one of the dates, it
appears as a serial value and not formatted. How do I get
it formatted? Thanks in advance - any help is appreciated.

Ray Kanner

Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date

s = Range("Start").Value
e = Range("End").Value

ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop

End Sub

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
End Sub

Do you mean in Range("NewStart")?

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
Range("NewStart").NumberFormat = "mmm-yy"
End Sub

since you are loading the combobox dropdown with strings, you shouldn't have
any problem with what is displayed in the combobox itself. You don't have
the combobox linked to NewStart do you? If so, break the link.

--
Regards,
Tom Ogilvy


Ray Kanner wrote in message
...
The code below creates a ComboBox consisting of all of the
first (day) in month dates in between a start date and an
end date. I am having problems with the formatting of the
ComboBox control. When I click on the drop down, I see all
of the dates in the correct date format (e.g. Jan-00, Feb-
00, etc). However, when I select one of the dates, it
appears as a serial value and not formatted. How do I get
it formatted? Thanks in advance - any help is appreciated.

Ray Kanner

Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date

s = Range("Start").Value
e = Range("End").Value

ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop

End Sub

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
End Sub

Tom, Thanks. I actually meant the combobox itself (not
NewStart), but I did have the combobox linked to NewStart
which I broke. Unfortunately, now, nothing displays. It
seems like the ComboBox1.Clear clears it. If I comment it
out, everything works and formats correcetly, except that
the items don't reset themselves when I change the value
of the ranges Start or End, which is what I really need. I
must be missing something basic here. Thanks

Ray
-----Original Message-----
Do you mean in Range("NewStart")?

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
Range("NewStart").NumberFormat = "mmm-yy"
End Sub

since you are loading the combobox dropdown with strings,
you shouldn't have
any problem with what is displayed in the combobox
itself. You don't have
the combobox linked to NewStart do you? If so, break the
link.

--
Regards,
Tom Ogilvy


Ray Kanner wrote in message
...
The code below creates a ComboBox consisting of all of
the
first (day) in month dates in between a start date and
an
end date. I am having problems with the formatting of
the
ComboBox control. When I click on the drop down, I see
all
of the dates in the correct date format (e.g. Jan-00,
Feb-
00, etc). However, when I select one of the dates, it
appears as a serial value and not formatted. How do I
get
it formatted? Thanks in advance - any help is
appreciated.

Ray Kanner

Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date

s = Range("Start").Value
e = Range("End").Value

ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop

End Sub

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
End Sub


.

this worked for me:

Public bBlockEvents As Boolean
Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date
If bBlockEvents Then Exit Sub
bBlockEvents = True
s = Range("Start").Value
e = Range("End").Value

ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop
bBlockEvents = False
End Sub

Private Sub ComboBox1_Change()
If bBlockEvents Then Exit Sub
Range("NewStart").Value = ComboBox1.Value
End Sub

or
Remove the Combobox1_Change event and reestablish the link.

--
Regards,
Tom Ogilvy

Ray Kanner wrote in message
...
Tom, Thanks. I actually meant the combobox itself (not
NewStart), but I did have the combobox linked to NewStart
which I broke. Unfortunately, now, nothing displays. It
seems like the ComboBox1.Clear clears it. If I comment it
out, everything works and formats correcetly, except that
the items don't reset themselves when I change the value
of the ranges Start or End, which is what I really need. I
must be missing something basic here. Thanks

Ray
-----Original Message-----
Do you mean in Range("NewStart")?

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
Range("NewStart").NumberFormat = "mmm-yy"
End Sub

since you are loading the combobox dropdown with strings,
you shouldn't have
any problem with what is displayed in the combobox
itself. You don't have
the combobox linked to NewStart do you? If so, break the
link.

--
Regards,
Tom Ogilvy


Ray Kanner wrote in message
...
The code below creates a ComboBox consisting of all of
the
first (day) in month dates in between a start date and
an
end date. I am having problems with the formatting of
the
ComboBox control. When I click on the drop down, I see
all
of the dates in the correct date format (e.g. Jan-00,
Feb-
00, etc). However, when I select one of the dates, it
appears as a serial value and not formatted. How do I
get
it formatted? Thanks in advance - any help is
appreciated.

Ray Kanner

Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date

s = Range("Start").Value
e = Range("End").Value

ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop

End Sub

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
End Sub


.

Actually, that didn't totally work. Try it this way.

Public bBlockEvents As Boolean
Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date
If bBlockEvents Then Exit Sub
bBlockEvents = True
s = Range("Start").Value
e = Range("End").Value
sVal = ComboBox1.Value
ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop
ComboBox1 = sVal
bBlockEvents = False
End Sub

Private Sub ComboBox1_Click()
If bBlockEvents Then Exit Sub
Range("NewStart").Value = ComboBox1.Value
End Sub


--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
this worked for me:

Public bBlockEvents As Boolean
Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date
If bBlockEvents Then Exit Sub
bBlockEvents = True
s = Range("Start").Value
e = Range("End").Value

ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop
bBlockEvents = False
End Sub

Private Sub ComboBox1_Change()
If bBlockEvents Then Exit Sub
Range("NewStart").Value = ComboBox1.Value
End Sub

or
Remove the Combobox1_Change event and reestablish the link.

--
Regards,
Tom Ogilvy

Ray Kanner wrote in message
...
Tom, Thanks. I actually meant the combobox itself (not
NewStart), but I did have the combobox linked to NewStart
which I broke. Unfortunately, now, nothing displays. It
seems like the ComboBox1.Clear clears it. If I comment it
out, everything works and formats correcetly, except that
the items don't reset themselves when I change the value
of the ranges Start or End, which is what I really need. I
must be missing something basic here. Thanks

Ray
-----Original Message-----
Do you mean in Range("NewStart")?

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
Range("NewStart").NumberFormat = "mmm-yy"
End Sub

since you are loading the combobox dropdown with strings,
you shouldn't have
any problem with what is displayed in the combobox
itself. You don't have
the combobox linked to NewStart do you? If so, break the
link.

--
Regards,
Tom Ogilvy


Ray Kanner wrote in message
...
The code below creates a ComboBox consisting of all of
the
first (day) in month dates in between a start date and
an
end date. I am having problems with the formatting of
the
ComboBox control. When I click on the drop down, I see
all
of the dates in the correct date format (e.g. Jan-00,
Feb-
00, etc). However, when I select one of the dates, it
appears as a serial value and not formatted. How do I
get
it formatted? Thanks in advance - any help is
appreciated.

Ray Kanner

Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date

s = Range("Start").Value
e = Range("End").Value

ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop

End Sub

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
End Sub


.

Tom, Thanks. Did this work for you? I can't get it to
display anything for me. I just get a drop down box of 1
item and it's blank. I also tried to remove the Change
procs and relinking the cell NewStart but that does the
same thing, i.e. one line item to select and nothing in
it. Thanks.

Ray
-----Original Message-----
Actually, that didn't totally work. Try it this way.

Public bBlockEvents As Boolean
Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date
If bBlockEvents Then Exit Sub
bBlockEvents = True
s = Range("Start").Value
e = Range("End").Value
sVal = ComboBox1.Value
ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop
ComboBox1 = sVal
bBlockEvents = False
End Sub

Private Sub ComboBox1_Click()
If bBlockEvents Then Exit Sub
Range("NewStart").Value = ComboBox1.Value
End Sub


--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
this worked for me:

Public bBlockEvents As Boolean
Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date
If bBlockEvents Then Exit Sub
bBlockEvents = True
s = Range("Start").Value
e = Range("End").Value

ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop
bBlockEvents = False
End Sub

Private Sub ComboBox1_Change()
If bBlockEvents Then Exit Sub
Range("NewStart").Value = ComboBox1.Value
End Sub

or
Remove the Combobox1_Change event and reestablish the
link.

--
Regards,
Tom Ogilvy

Ray Kanner wrote in message
...
Tom, Thanks. I actually meant the combobox itself (not
NewStart), but I did have the combobox linked to
NewStart
which I broke. Unfortunately, now, nothing displays.
It
seems like the ComboBox1.Clear clears it. If I
comment it
out, everything works and formats correcetly, except
that
the items don't reset themselves when I change the
value
of the ranges Start or End, which is what I really
need. I
must be missing something basic here. Thanks

Ray
-----Original Message-----
Do you mean in Range("NewStart")?

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
Range("NewStart").NumberFormat = "mmm-yy"
End Sub

since you are loading the combobox dropdown with
strings,
you shouldn't have
any problem with what is displayed in the combobox
itself. You don't have
the combobox linked to NewStart do you? If so,
break the
link.

--
Regards,
Tom Ogilvy


Ray Kanner wrote in message
...
The code below creates a ComboBox consisting of
all of
the
first (day) in month dates in between a start date
and
an
end date. I am having problems with the formatting
of
the
ComboBox control. When I click on the drop down, I
see
all
of the dates in the correct date format (e.g. Jan-
00,
Feb-
00, etc). However, when I select one of the dates,
it
appears as a serial value and not formatted. How
do I
get
it formatted? Thanks in advance - any help is
appreciated.

Ray Kanner

Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date

s = Range("Start").Value
e = Range("End").Value

ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop

End Sub

Private Sub ComboBox1_Change()
Range("NewStart").Value = ComboBox1.Value
End Sub


.





.

Yes, Ray, it worked fine for me.

--
Regards,
Tom Ogilvy

Ray Kanner wrote in message
...
Tom, Thanks. Did this work for you? I can't get it to
display anything for me. I just get a drop down box of 1
item and it's blank. I also tried to remove the Change
procs and relinking the cell NewStart but that does the
same thing, i.e. one line item to select and nothing in
it. Thanks.

Ray
-----Original Message-----
Actually, that didn't totally work. Try it this way.

Public bBlockEvents As Boolean
Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date
If bBlockEvents Then Exit Sub
bBlockEvents = True
s = Range("Start").Value
e = Range("End").Value
sVal = ComboBox1.Value
ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop
ComboBox1 = sVal
bBlockEvents = False
End Sub

Private Sub ComboBox1_Click()
If bBlockEvents Then Exit Sub
Range("NewStart").Value = ComboBox1.Value
End Sub


--
Regards,
Tom Ogilvy

Tom, I did get it to work (kind of), but for the life of
me, I am not sure I understand what is going on. What I
mean by kind of, is I don't really think it works with the
format I have used. If I use a "mm/dd/yy" format, it works
fine. However, when I use the "mmm-yy" format shown, then
when Range("NewStart").Value = ComboBox1.Value is
executed, it does not do the date conversion correctly. It
just takes the "mmm-yy" and converts it to "mm/yy/04" (04
because that's the current year). Is that what you get too?

Thanks again.

Ray
-----Original Message-----
Yes, Ray, it worked fine for me.

--
Regards,
Tom Ogilvy

Ray Kanner wrote in message
...
Tom, Thanks. Did this work for you? I can't get it to
display anything for me. I just get a drop down box of 1
item and it's blank. I also tried to remove the Change
procs and relinking the cell NewStart but that does the
same thing, i.e. one line item to select and nothing in
it. Thanks.

Ray
-----Original Message-----
Actually, that didn't totally work. Try it this way.

Public bBlockEvents As Boolean
Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date
If bBlockEvents Then Exit Sub
bBlockEvents = True
s = Range("Start").Value
e = Range("End").Value
sVal = ComboBox1.Value
ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop
ComboBox1 = sVal
bBlockEvents = False
End Sub

Private Sub ComboBox1_Click()
If bBlockEvents Then Exit Sub
Range("NewStart").Value = ComboBox1.Value
End Sub


--
Regards,
Tom Ogilvy




.

Actually, I didn't notice it was doing that. But this fixed it:

Private Sub ComboBox1_Click()
Dim sStr As String
Dim lVal As Long
' I needed this next line for xl97.
ActiveCell.Activate
If bBlockEvents Then Exit Sub
sStr = ComboBox1.Value
sStr = Left(sStr, 3) & " 1, "
lVal = Right(ComboBox1.Value, 2)
If lVal 90 Then
lVal = lVal + 1900
Else
lVal = lVal + 2000
End If
sStr = sStr & Format(lVal, "0000")
Range("NewStart").Value = sStr
Range("NewStart").NumberFormat = "mmm-yy"
End Sub

--
Regards,
Tom Ogilvy


Ray Kanner wrote in message
...
Tom, I did get it to work (kind of), but for the life of
me, I am not sure I understand what is going on. What I
mean by kind of, is I don't really think it works with the
format I have used. If I use a "mm/dd/yy" format, it works
fine. However, when I use the "mmm-yy" format shown, then
when Range("NewStart").Value = ComboBox1.Value is
executed, it does not do the date conversion correctly. It
just takes the "mmm-yy" and converts it to "mm/yy/04" (04
because that's the current year). Is that what you get too?

Thanks again.

Ray
-----Original Message-----
Yes, Ray, it worked fine for me.

--
Regards,
Tom Ogilvy

Ray Kanner wrote in message
...
Tom, Thanks. Did this work for you? I can't get it to
display anything for me. I just get a drop down box of 1
item and it's blank. I also tried to remove the Change
procs and relinking the cell NewStart but that does the
same thing, i.e. one line item to select and nothing in
it. Thanks.

Ray
-----Original Message-----
Actually, that didn't totally work. Try it this way.

Public bBlockEvents As Boolean
Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date
If bBlockEvents Then Exit Sub
bBlockEvents = True
s = Range("Start").Value
e = Range("End").Value
sVal = ComboBox1.Value
ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop
ComboBox1 = sVal
bBlockEvents = False
End Sub

Private Sub ComboBox1_Click()
If bBlockEvents Then Exit Sub
Range("NewStart").Value = ComboBox1.Value
End Sub


--
Regards,
Tom Ogilvy




.

Tom, Thanks a lot - that did it.

Ray
-----Original Message-----
Actually, I didn't notice it was doing that. But this
fixed it:

Private Sub ComboBox1_Click()
Dim sStr As String
Dim lVal As Long
' I needed this next line for xl97.
ActiveCell.Activate
If bBlockEvents Then Exit Sub
sStr = ComboBox1.Value
sStr = Left(sStr, 3) & " 1, "
lVal = Right(ComboBox1.Value, 2)
If lVal 90 Then
lVal = lVal + 1900
Else
lVal = lVal + 2000
End If
sStr = sStr & Format(lVal, "0000")
Range("NewStart").Value = sStr
Range("NewStart").NumberFormat = "mmm-yy"
End Sub

--
Regards,
Tom Ogilvy


Ray Kanner wrote in message
...
Tom, I did get it to work (kind of), but for the life of
me, I am not sure I understand what is going on. What I
mean by kind of, is I don't really think it works with
the
format I have used. If I use a "mm/dd/yy" format, it
works
fine. However, when I use the "mmm-yy" format shown,
then
when Range("NewStart").Value = ComboBox1.Value is
executed, it does not do the date conversion correctly.
It
just takes the "mmm-yy" and converts it to "mm/yy/04"
(04
because that's the current year). Is that what you get
too?

Thanks again.

Ray
-----Original Message-----
Yes, Ray, it worked fine for me.

--
Regards,
Tom Ogilvy

Ray Kanner wrote in message
...
Tom, Thanks. Did this work for you? I can't get it to
display anything for me. I just get a drop down box
of 1
item and it's blank. I also tried to remove the
Change
procs and relinking the cell NewStart but that does
the
same thing, i.e. one line item to select and nothing
in
it. Thanks.

Ray
-----Original Message-----
Actually, that didn't totally work. Try it this
way.

Public bBlockEvents As Boolean
Private Sub ComboBox1_DropButtonClick()
Dim s As Date, e As Date, i As Date
If bBlockEvents Then Exit Sub
bBlockEvents = True
s = Range("Start").Value
e = Range("End").Value
sVal = ComboBox1.Value
ComboBox1.Clear
i = DateSerial(Year(s), Month(s), 1)

Do While i < e
ComboBox1.AddItem Format(i, "mmm-yy")
i = DateAdd("m", 1, i)
Loop
ComboBox1 = sVal
bBlockEvents = False
End Sub

Private Sub ComboBox1_Click()
If bBlockEvents Then Exit Sub
Range("NewStart").Value = ComboBox1.Value
End Sub


--
Regards,
Tom Ogilvy




.



.