ComboBox Value Problem

[bhartman36]

Hi, Everyone.

My macro contains the following code:

Dim y as Integer

ComboBox3.AddItem "January"
ComboBox3.AddItem "February"
ComboBox3.AddItem "March"
ComboBox3.AddItem "April"
ComboBox3.AddItem "May"
ComboBox3.AddItem "June"
ComboBox3.AddItem "July"
ComboBox3.AddItem "August"
ComboBox3.AddItem "September"
ComboBox3.AddItem "October"
ComboBox3.AddItem "November"
ComboBox3.AddItem "December"
ComboBox3.Visible = True

If ComboBox3.Value = "January" Or "March" Or "May" Or "July" Or
"August" Or "October" Or "December" Then
For y = 1 To 30
ComboBox4.AddItem y
Next
Else
For y = 1 To 31
ComboBox4.AddItem y
Next
End If

I'm getting a "Type Mismatch" error with this code. Can anyone set me
straight?

Thanks for your help!

 

[Perry]

How about:
(note: Leapyears!!)

private sub UserForm_iitialize()
Dim x as integer
combobox3.columncount = 2
For x = 1 To 12
ComboBox3.additem Format(dateserial(1, x, 1), "mmmm")
ComboBox3.List(Combobox3.Listcount - 1, 1) = x
next
end sub

private sub combobox3_Change()
dim iDays As integer
dim x as Integer
Select case combobox3.column(2)
case 1, 3, 5, 7, 8, 10, 12
idays = 31
Case 2
idays = 28
Case Else
idays = 30
End Select
for x = 1 To idays
Combobox4.addItem CStr(x)
next
End sub

Krgrds,
perry

 

[Jay Freedman]

Your code is trying to "OR" together a bunch of string values:

"January" Or "March" Or "May" Or "July" Or "August" Or "October" Or
"December"

The Or operator requires Boolean (true/false) values, so you're getting a
type mismatch.

The brute-force way is to say

If (ComboBox3.Value = "January") Or _
(ComboBox3.Value = "March") Or _
(ComboBox3.Value = "May") ' ... etc.

With more than three choices, though, a Select Case is more efficient. More
on that later...

Once you get past the syntax error, you'd find that your code (if it's all
in the same procedure) doesn't do the right stuff. It needs to be split
between two different event procedures. All the .AddItem calls for ComboBox3
should go into Userform_Initialize. Also in there, I'd recommend including

ComboBox3.ListIndex = 0
ComboBox3.MatchRequired = True

The first one makes January appear in the box instead of a blank. The second
one says that the user can't type in a value that isn't one of the entries
already in the list.

Everything else belongs in the ComboBox3_Change procedure, which fires each
time the user clicks a different item in the box. The first thing it has to
do is remove any values already in the ComboBox4 list (otherwise you'll get
multiple copies of the list of dates). Then it can reload ComboBox4 -- this
is where the Select Case comes in. You can use this, although you might want
to adjust February for leap years.

Private Sub ComboBox3_Change()
Dim y As Integer, LastDay As Integer
Select Case ComboBox3.Value
Case "February"
LastDay = 28
Case "April", "June", "September", "November"
LastDay = 30
Case Else
LastDay = 31
End Select

ComboBox4.Clear
For y = 1 To LastDay
ComboBox4.AddItem y
Next
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Doug Robbins - Word MVP]

To avoid getting that error, the syntax of the If statement needs to be:

If ComboBox3.Value = "January" Or ComboBox3.Value = "March" Or
ComboBox3.Value = "May" etc.

But you have other problems as well, not the least of which is that the
months in your If statement all have 31 days yet you are giving the user the
choice of only 30 days and for the months that have less than 31 days, you
are giving them the opportunity to select the 31st day of the month.

Then, you need to split the code into two routines, one to populate
combobox3 and one the populate combobox4 based on the month selected in
combobox3

Dim y As Long

ComboBox3.AddItem "January"
ComboBox3.AddItem "February"
ComboBox3.AddItem "March"
ComboBox3.AddItem "April"
ComboBox3.AddItem "May"
ComboBox3.AddItem "June"
ComboBox3.AddItem "July"
ComboBox3.AddItem "August"
ComboBox3.AddItem "September"
ComboBox3.AddItem "October"
ComboBox3.AddItem "November"
ComboBox3.AddItem "December"
ComboBox3.Visible = True

Private Sub ComboBox3_Change()
Dim i As Long
ComboBox4.Clear
If ComboBox3.Value = "January" Or ComboBox3.Value = "March" Or
ComboBox3.Value = "May" Or ComboBox3.Value = "July" _
Or ComboBox3.Value = "August" Or ComboBox3.Value = "October" Or
ComboBox3.Value = "December" Then
For y = 1 To 30 'Should be 31
ComboBox4.AddItem y
Next
Else
For y = 1 To 31 'should be 30 except for February and then what about
leap years?
ComboBox4.AddItem y
Next
End If
End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[bhartman36]

Hi, Perry.

Thanks for the help and suggestion. Was the problem with my code the
fact that I was using words instead of numbers for the months? One of
the reasons I settled on month names instead of numbers was to make it
obvious for the user to distinguish. I've got some European users,
and, of course, their date format goes DD-MM-YYYY instead of the
American MM-DD-YYYY.

I know It's a minor point, and if I have to use numbers that's okay,
but I'm just wondering if it's the words that screwed the whole thing
up.

 

[bhartman36]

Hi, Perry.

Thanks for the help and suggestion. Was the problem with my code the
fact that I was using words instead of numbers for the months? One of
the reasons I settled on month names instead of numbers was to make it
obvious for the user to distinguish. I've got some European users,
and, of course, their date format goes DD-MM-YYYY instead of the
American MM-DD-YYYY.

I know It's a minor point, and if I have to use numbers that's okay,
but I'm just wondering if it's the words that screwed the whole thing
up.

 

[bhartman36]

To avoid getting that error, the syntax of the If statement needs to be:

If ComboBox3.Value = "January" Or ComboBox3.Value = "March" Or
ComboBox3.Value = "May" etc.

But you have other problems as well, not the least of which is that the
months in your If statement all have 31 days yet you are giving the user the
choice of only 30 days and for the months that have less than 31 days, you
are giving them the opportunity to select the 31st day of the month.

Then, you need to split the code into two routines, one to populate
combobox3 and one the populate combobox4 based on the month selected in
combobox3

Dim y As Long

ComboBox3.AddItem "January"
ComboBox3.AddItem "February"
ComboBox3.AddItem "March"
ComboBox3.AddItem "April"
ComboBox3.AddItem "May"
ComboBox3.AddItem "June"
ComboBox3.AddItem "July"
ComboBox3.AddItem "August"
ComboBox3.AddItem "September"
ComboBox3.AddItem "October"
ComboBox3.AddItem "November"
ComboBox3.AddItem "December"
ComboBox3.Visible = True

Private Sub ComboBox3_Change()
Dim i As Long
ComboBox4.Clear
If ComboBox3.Value = "January" Or ComboBox3.Value = "March" Or
ComboBox3.Value = "May" Or ComboBox3.Value = "July" _
Or ComboBox3.Value = "August" Or ComboBox3.Value = "October" Or
ComboBox3.Value = "December" Then
For y = 1 To 30 'Should be 31
ComboBox4.AddItem y
Next
Else
For y = 1 To 31 'should be 30 except for February and then what about
leap years?
ComboBox4.AddItem y
Next
End If
End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP












- Show quoted text -

Wow! Thanks for all the help! That OR operator really screwed me
up. I'll revise with your suggestions.

Thanks again!