help with dates on form combobox

A

anon

Hi,

I've created a userform which is a calendar. The month displayed is
controlled by a combobox on the userform, so whichever month selected
in the combobox controls the dates displayed in the textboxes below.

I have a problem however. The months available to select in the
combobox are;
May-08
Jun-08
Jul-08
Aug-08
Sep-08
Oct-08
Nov-08
Dec-08
Jan-09
Feb-09
Mar-09
Apr-09
May-09
Jun-09
Jul-09

These are in a sheet on my workbook and the combobox rowsource is set
to sheet4!af1:sheet4!af15 (where these are located). The code that
runs when a user makes a selection;

Private Sub ComboBox1_Change()
UserForm5.Controls("ComboBox1").Value =
Format(UserForm5.Controls("ComboBox1").Value, "mmm-yy")

If ComboBox1.Value = "May-08" Then Call may08
If ComboBox1.Value = "Jun-08" Then Call june08
If ComboBox1.Value = "Jul-08" Then Call july08
If ComboBox1.Value = "Aug-08" Then Call august08
If ComboBox1.Value = "Sep-08" Then Call september08
If ComboBox1.Value = "Oct-08" Then Call october08
If ComboBox1.Value = "Nov-08" Then Call november08
If ComboBox1.Value = "Dec-08" Then Call december08
If ComboBox1.Value = "Jan-09" Then Call january09
If ComboBox1.Value = "Feb-09" Then Call february09
If ComboBox1.Value = "Mar-09" Then Call march09
If ComboBox1.Value = "Apr-09" Then Call april09
If ComboBox1.Value = "May-09" Then Call may09
If ComboBox1.Value = "Jun-09" Then Call june09
If ComboBox1.Value = "Jul-09" Then Call july09
End Sub

When i load the form and select the combobox I can see all of the
options, however if I select a month in 2009 the combobox defaults to
the same month in 2008. So if I select Jul-09 from the combobox the
combobox shows Jul-08 and therefore the dates shown in my textboxes
are July 2008 dates.

I have checked all of the sub's named in the code exist but cannot
figure why this is happening. Does anyone have any ideas?
 
J

Jim Cone

Some suggestions...
Set the number format for the rowsource cells to text.
Make sure the cells display exactly what you want to display in the combobox.

Comment out the line... UserForm5.Controls("ComboBox1").Value =
Format(UserForm5.Controls("ComboBox1").Value, "mmm-yy")

Use a Select Case format instead of multiple If's.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"anon"
wrote in message
Hi,
I've created a userform which is a calendar. The month displayed is
controlled by a combobox on the userform, so whichever month selected
in the combobox controls the dates displayed in the textboxes below.

I have a problem however. The months available to select in the
combobox are;
May-08
Jun-08
Jul-08
Aug-08
Sep-08
Oct-08
Nov-08
Dec-08
Jan-09
Feb-09
Mar-09
Apr-09
May-09
Jun-09
Jul-09

These are in a sheet on my workbook and the combobox rowsource is set
to sheet4!af1:sheet4!af15 (where these are located). The code that
runs when a user makes a selection;

Private Sub ComboBox1_Change()
UserForm5.Controls("ComboBox1").Value =
Format(UserForm5.Controls("ComboBox1").Value, "mmm-yy")

If ComboBox1.Value = "May-08" Then Call may08
If ComboBox1.Value = "Jun-08" Then Call june08
If ComboBox1.Value = "Jul-08" Then Call july08
If ComboBox1.Value = "Aug-08" Then Call august08
If ComboBox1.Value = "Sep-08" Then Call september08
If ComboBox1.Value = "Oct-08" Then Call october08
If ComboBox1.Value = "Nov-08" Then Call november08
If ComboBox1.Value = "Dec-08" Then Call december08
If ComboBox1.Value = "Jan-09" Then Call january09
If ComboBox1.Value = "Feb-09" Then Call february09
If ComboBox1.Value = "Mar-09" Then Call march09
If ComboBox1.Value = "Apr-09" Then Call april09
If ComboBox1.Value = "May-09" Then Call may09
If ComboBox1.Value = "Jun-09" Then Call june09
If ComboBox1.Value = "Jul-09" Then Call july09
End Sub

When i load the form and select the combobox I can see all of the
options, however if I select a month in 2009 the combobox defaults to
the same month in 2008. So if I select Jul-09 from the combobox the
combobox shows Jul-08 and therefore the dates shown in my textboxes
are July 2008 dates.
I have checked all of the sub's named in the code exist but cannot
figure why this is happening. Does anyone have any ideas?
 
J

john

as a rule I would avoid linking combobox to worksheet.
Use the forms intialize event to populate it.
Paste updated code to your forms code page and hopefully, should solve your
problem. (Remember to remove the Rowsource values you entered from the
combobox properties before you run it.)

Private Sub ComboBox1_Change()
If ComboBox1.Text = "May-08" Then Call may08
If ComboBox1.Text = "Jun-08" Then Call june08
If ComboBox1.Text = "Jul-08" Then Call july08
If ComboBox1.Text = "Aug-08" Then Call august08
If ComboBox1.Text = "Sep-08" Then Call september08
If ComboBox1.Text = "Oct-08" Then Call october08
If ComboBox1.Text = "Nov-08" Then Call november08
If ComboBox1.Text = "Dec-08" Then Call december08
If ComboBox1.Text = "Jan-09" Then Call january09
If ComboBox1.Text = "Feb-09" Then Call february09
If ComboBox1.Text = "Mar-09" Then Call march09
If ComboBox1.Text = "Apr-09" Then Call april09
If ComboBox1.Text = "May-09" Then Call may09
If ComboBox1.Text = "Jun-09" Then Call june09
If ComboBox1.Text = "Jul-09" Then Call july09
End Sub


Private Sub UserForm_Initialize()
Dim myrange As Range

Set myrange = Worksheets("Sheet4").Range("AF1:AF15")

For Each Item In myrange
ComboBox1.AddItem Format(Item, "mmm-yy")
Next Item

End Sub
 
D

Dana DeLouis

combobox shows Jul-08 and therefore the dates shown in my textboxes
are July 2008 dates.

I have checked all of the sub's named in the code exist but cannot
figure why this is happening. Does anyone have any ideas?
Click to expand...

Hi. I believe what is happening is that you are giving a string with an
Month & Year, but Excel is seeing this as a Month and Day.
Step thru this code, and check the first two 'd's. The 08 & 09 are "assumed"
to be day, and the year is assumed to be the current year.
If you append something like a "1 " to the beginning, then Excel will
"Assume" the date as you expect.

Sub Demo()
Dim d As Date
d = "Nov-08"
d = "Nov-09"

d = "1 May-08"
d = "1 Aug-09"
End Sub

If I were writing it, I might do something like this...

Sub Demo2()
Dim Dte As Date
Dim sDte As String

sDte = "Aug-08"

'fix it here
Dte = "1 " & sDte

Select Case CDbl(Dte)
Case 29569 To 40025 '1May08 to 31Jun09
Run Format(Dte, "mmmmyy")
Case Else
MsgBox "Outside Range"
End Select
End Sub
 
D

Dana DeLouis

Oops! Bad programming practice as it's hard to catch the typo I had in the
date.
Let me change it to this...

Sub Demo2()
Dim Dte As Date
Dim sDte As String

sDte = "Aug-08" ' <- Your input here

'fix it here
Dte = "1 " & sDte

Select Case Dte
Case CDate("1 May 2008") To CDate("31 July 2009")
Run Format(Dte, "mmmmyy")
Case Else
MsgBox "Outside Range"
End Select
End Sub

--
HTH :>)
Dana DeLouis


<< snip >>
 
A

anon

Hi all and thanks for your help...all fixed now and tips taken on
board (including the select case which I hadn't used before). Cheers
guys!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date problem 2
How to Show 12 months dates on the basis of from today's date in 9
My own fiscal year 4
Automate Filter in Pivot table in VB Macro 0
If, then, for a long time 0
Copy from sheet1 and sheet2 and append to sheet3 in different format 13
code modification 1
unexpected cursor behavior while attempting to control tab order intextboxes and comboboxes on a spr 0

Top