Nope.
I think you'll have to look to see if the entire calendar year was chosen. If
not, then you could strip off the year from each entry to see if the years
match.
If the years do match, you could use instr() to see if your the listbox1 entry
shows up in that string.
I didn't do the concatenation like you did, but this may give you an idea how to
go about checking.
Personally, it looks like too much trouble to me. I think a re-think of the
design is in order. Checking to see what's there and what could be added on the
next click gets really complex (what should be removed and what should be added
when...)
Say I add Jan-Mar 2002, then choose Apr-Jun 2002. Then do the entire year on
the third attempt. I would think that the first two should be thrown away and
the entire year should be kept.
Anyway here's the code:
Option Explicit
Dim BlkProc As Boolean
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub ListBox1_Change()
Dim iCtr As Long
Dim CalYearWasSelected As Boolean
Dim HowManySelected As Long
If BlkProc = True Then
Exit Sub
End If
CalYearWasSelected = False
HowManySelected = 0
With Me.ListBox1
'everything but the "Quarterly Calendar Year" item
For iCtr = 0 To .ListCount - 2
If .Selected(iCtr) = True Then
HowManySelected = HowManySelected + 1
End If
Next iCtr
'check to see if "Quarterly Calendar Year" was selected
If .Selected(.ListCount - 1) = True _
Or HowManySelected = .ListCount - 1 Then
CalYearWasSelected = True
End If
If CalYearWasSelected = True Then
BlkProc = True
For iCtr = 0 To Me.ListBox1.ListCount - 2
.Selected(iCtr) = False
Next iCtr
.Selected(.ListCount - 1) = True
BlkProc = False
End If
End With
End Sub
Private Sub UserForm_Initialize()
BlkProc = True
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.AddItem "Jan-Mar"
.AddItem "Apr-Jun"
.AddItem "Jul-Sep"
.AddItem "Oct-Dec"
.AddItem "Quarterly Calendar Year"
End With
With Me.ListBox2
.MultiSelect = fmMultiSelectSingle
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
End With
BlkProc = False
Me.Label1.Caption = ""
End Sub
Private Sub CommandButton1_Click()
Dim tlist As String
Dim lIndex As Long
Dim OkToAdd As Boolean
Dim iCtr As Long
Dim LB1HasASelection As Boolean
If Me.ListBox2.ListIndex < 0 Then
Beep 'nothing selected inme.listbox2
Me.Label1.Caption = "Please select a year!"
Exit Sub
End If
LB1HasASelection = False
For lIndex = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(lIndex) = True Then
LB1HasASelection = True
tlist = Me.ListBox1.List(lIndex) & " " & Me.ListBox2.Value
OkToAdd = True
For iCtr = 0 To Me.ListBox3.ListCount - 1
If Me.ListBox2.Value = Right(Me.ListBox3.List(iCtr), 4) Then
'years match
If Left(Me.ListBox3.List(iCtr), 23) _
= "Quarterly Calendar Year" Then
'it's there, don't add it
OkToAdd = False
Else
If InStr(1, Me.ListBox3.List(iCtr), _
Me.ListBox1.List(lIndex), vbTextCompare) > 0 Then
'found it, don't add
OkToAdd = False
Exit For
End If
End If
End If
Next iCtr
If OkToAdd Then
Me.ListBox3.AddItem tlist
End If
End If
Next lIndex
If LB1HasASelection = False Then
Me.Label1.Caption = "Please select at least one calendar option"
Beep
Else
Me.Label1.Caption = ""
End If
End Sub
===========================
I'm not sure how many years you're doing, but maybe a userform laid out like:
Year 2002 2003 2004 ....
Jan-Mar x x
Apr-Jun x
Jul-Sep
Oct-Dec x
Entire Year x
You could have checkbox for each option. If the entire checkbox is checked,
then you uncheck the previous 4.
==============
If you want to try:
This code goes behind the FrmChooseQtr (that's the name of the userform I used):
Option Explicit
Dim ChkBoxes() As New Class1
Private Sub CommandButton1_Click()
Dim yCtr As Long
Dim oCtr As Long
Dim StartPos As Long
Dim EndPos As Long
Dim myStr As String
Dim myQtrs(1 To 4) As String
myQtrs(1) = "Jan-Mar"
myQtrs(2) = "Apr-Jun"
myQtrs(3) = "Jul-Sep"
myQtrs(4) = "Oct-Dec"
Me.ListBox3.Clear 'clear old values
For yCtr = 1 To 3 '3 years for my example
If Me.Controls("Checkbox" & yCtr * 5).Value = True Then
'year was chosen
Me.ListBox3.AddItem "Quarterly Calendar Year" _
& " " & Me.Controls("Label" & yCtr)
Else
myStr = ""
'loop through each quarter like you did before
StartPos = (yCtr * 5) - 4
EndPos = StartPos + 3
For oCtr = StartPos To EndPos
If Me.Controls("Checkbox" & oCtr).Value = True Then
myStr = myStr & ", " & myQtrs(oCtr Mod 5)
End If
Next oCtr
If myStr = "" Then
'nothing chosen, do nothing
Else
myStr = Mid(myStr, 3) & " " & Me.Controls("label" & yCtr)
Me.ListBox3.AddItem myStr
End If
End If
Next yCtr
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
Dim CBXCount As Long
Dim Ctrl As Control
Me.CommandButton1.Caption = "Ok"
Me.CommandButton2.Caption = "Cancel"
Me.Label1.Caption = "2002"
Me.Label2.Caption = "2003"
Me.Label3.Caption = "2004"
Me.Label4.Caption = "Jan-Mar"
Me.Label5.Caption = "Apr-Jun"
Me.Label6.Caption = "Jul-Sep"
Me.Label7.Caption = "Oct_Dec"
Me.Label8.Caption = "Entire Year"
For iCtr = 1 To 5 * 3 '5 rows by 3 columns
Me.Controls("Checkbox" & iCtr).Caption = ""
Next iCtr
CBXCount = 0
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
CBXCount = CBXCount + 1
ReDim Preserve ChkBoxes(1 To CBXCount)
Set ChkBoxes(CBXCount).CBXGroup = Ctrl
End If
Next Ctrl
End Sub
Now insert a new Class module (Insert|Class Module). It should be called
Class1. This class module is going to do all the processing when you click on a
checkbox. Paste this code in this class module.
Public WithEvents CBXGroup As MSForms.CheckBox
Private Sub CBXGroup_Change()
Dim WhichOne As Long
Dim StartOne As Long
Dim EndOne As Long
Dim HowMany As Long
WhichOne = Mid(CBXGroup.Name, Len("Checkbox") + 1)
If WhichOne Mod 5 = 0 Then
'clicking the year for that column.
'so enable/disable the others
StartOne = WhichOne - 4
EndOne = StartOne + 3
For iCtr = StartOne To EndOne
FrmChooseQtr.Controls("Checkbox" & iCtr).Enabled _
= CBool(CBXGroup.Value = False)
Next iCtr
Else
'clicking on quarter option
StartOne = Int(WhichOne / 5) + 1
EndOne = StartOne + 3
HowMany = 0
For iCtr = StartOne To EndOne
If FrmChooseQtr.Controls("Checkbox" & iCtr).Value = True Then
HowMany = HowMany + 1
Else
'not checked, don't bother checking the rest
Exit For
End If
Next iCtr
If HowMany = 4 Then
'all checked. Use the Year.
'this event will fire automatically and disable the quarters
'for this year.
FrmChooseQtr.Controls("Checkbox" & EndOne + 1).Value = True
End If
End If
End Sub
===========
If you want a workbook with all this stuff in it, let me know. I saved a copy.
The userform needs a lot of prettying up, but it works.
You'll have to share your email address.
Do it like:
BlueWaterMist at myISP dot com
By munging the address, it may mean that your email address won't be retrieved
by some email address bot. And that means you may not get more spam.
Hi Dave
if you add Jan-Mar, Apr-Jun, Jul-Sep 2002 then if for example selected
Apr-Jun 2002 again that would be considered a dupe. Also if you selected
again Jan-Mar, Apr-Jun, Jul-Sep 2002 they would be dupes.
I know what you're saying but i just wanted the year to be at the end of the
periods selected. Currrently listbox1 has if statements to stop from
selecting all periods and quarterly calendar year at the same time.
Do you think I'm asking too much?
