Hi

I'm hoping you can assist as i can't find any relevant information on my
problem. I have two listboxes on a userform. When a command button is
clicked, it combines listbox1 and listbox2 together and creates a period into
listbox3. Listbox1 & listbox2 information are created when the userform is
Initialised. listbox1 is a multi select and listbox2 is a single select.
Example below:

Private Sub UserForm_Initialize()

With Me.ListBox1
..AddItem "Jan-Mar"
..AddItem "Apr-Jun"
..AddItem "Jul-Sep"
..AddItem "Oct-Dec"
..AddItem "Quarterly Calendar Year"
end With

With Me.ListBox2
..AddItem "2000"
..AddItem "2001"
..AddItem "2002"
..AddItem "2003"
..AddItem "2004"
end With

end Sub

Private Sub CommandButton1_Click()

Dim slist As String
Dim tlist As String
Dim lIndex As Long

For lIndex = 0 To 22

If ListBox1.Selected(lIndex) Then
slist = slist & ", " & ListBox1.List(lIndex)
tlist = " " & ListBox2.Value
End If
Next

ListBox3.AddItem Mid(slist, 3) & tlist

end Sub

My query is how can i stop duplications from been entered into listbox3?

I was confused about the slist and tlist, but this seemed to work ok for me.

I did add a label to indicate any errors:

Option Explicit
Private Sub UserForm_Initialize()
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

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 tlist = Me.ListBox3.List(iCtr) Then
OkToAdd = False
Exit For
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



bluewatermist wrote:

Hi

I'm hoping you can assist as i can't find any relevant information on my
problem. I have two listboxes on a userform. When a command button is
clicked, it combines listbox1 and listbox2 together and creates a period into
listbox3. Listbox1 & listbox2 information are created when the userform is
Initialised. listbox1 is a multi select and listbox2 is a single select.
Example below:

Private Sub UserForm_Initialize()

With Me.ListBox1
.AddItem "Jan-Mar"
.AddItem "Apr-Jun"
.AddItem "Jul-Sep"
.AddItem "Oct-Dec"
.AddItem "Quarterly Calendar Year"
end With

With Me.ListBox2
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
end With

end Sub

Private Sub CommandButton1_Click()

Dim slist As String
Dim tlist As String
Dim lIndex As Long

For lIndex = 0 To 22

If ListBox1.Selected(lIndex) Then
slist = slist & ", " & ListBox1.List(lIndex)
tlist = " " & ListBox2.Value
End If
Next

ListBox3.AddItem Mid(slist, 3) & tlist

end Sub

My query is how can i stop duplications from been entered into listbox3?

--

Dave Peterson

Hi Dave

I tried your coding and it works but like my original one if you select for
example Jan-Mar and Apr-Jun and Jul-Sep and then select 2002 it would place
this in listbox3 as Jan-Mar, Apr-Jun, Jul-Sep 2002. I have tried to change
your code but without success. Are you able to give me a suggestion.

Many thanks.

"Dave Peterson" wrote:

I was confused about the slist and tlist, but this seemed to work ok for me.

I did add a label to indicate any errors:

Option Explicit
Private Sub UserForm_Initialize()
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

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 tlist = Me.ListBox3.List(iCtr) Then
OkToAdd = False
Exit For
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



bluewatermist wrote:

Hi

I'm hoping you can assist as i can't find any relevant information on my
problem. I have two listboxes on a userform. When a command button is
clicked, it combines listbox1 and listbox2 together and creates a period into
listbox3. Listbox1 & listbox2 information are created when the userform is
Initialised. listbox1 is a multi select and listbox2 is a single select.
Example below:

Private Sub UserForm_Initialize()

With Me.ListBox1
.AddItem "Jan-Mar"
.AddItem "Apr-Jun"
.AddItem "Jul-Sep"
.AddItem "Oct-Dec"
.AddItem "Quarterly Calendar Year"
end With

With Me.ListBox2
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
end With

end Sub

Private Sub CommandButton1_Click()

Dim slist As String
Dim tlist As String
Dim lIndex As Long

For lIndex = 0 To 22

If ListBox1.Selected(lIndex) Then
slist = slist & ", " & ListBox1.List(lIndex)
tlist = " " & ListBox2.Value
End If
Next

ListBox3.AddItem Mid(slist, 3) & tlist

end Sub

My query is how can i stop duplications from been entered into listbox3?

--

Dave Peterson

For those selections, what do you want added to Listbox3?

--
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, originally posted via msnews.microsoft.com

"bluewatermist" wrote in message
...
Hi Dave

I tried your coding and it works but like my original one if you select
for
example Jan-Mar and Apr-Jun and Jul-Sep and then select 2002 it would
place
this in listbox3 as Jan-Mar, Apr-Jun, Jul-Sep 2002. I have tried to
change
your code but without success. Are you able to give me a suggestion.

Many thanks.

"Dave Peterson" wrote:

I was confused about the slist and tlist, but this seemed to work ok for
me.

I did add a label to indicate any errors:

Option Explicit
Private Sub UserForm_Initialize()
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

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 tlist = Me.ListBox3.List(iCtr) Then
OkToAdd = False
Exit For
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



bluewatermist wrote:

Hi

I'm hoping you can assist as i can't find any relevant information on
my
problem. I have two listboxes on a userform. When a command button is
clicked, it combines listbox1 and listbox2 together and creates a
period into
listbox3. Listbox1 & listbox2 information are created when the
userform is
Initialised. listbox1 is a multi select and listbox2 is a single
select.
Example below:

Private Sub UserForm_Initialize()

With Me.ListBox1
.AddItem "Jan-Mar"
.AddItem "Apr-Jun"
.AddItem "Jul-Sep"
.AddItem "Oct-Dec"
.AddItem "Quarterly Calendar Year"
end With

With Me.ListBox2
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
end With

end Sub

Private Sub CommandButton1_Click()

Dim slist As String
Dim tlist As String
Dim lIndex As Long

For lIndex = 0 To 22

If ListBox1.Selected(lIndex) Then
slist = slist & ", " & ListBox1.List(lIndex)
tlist = " " & ListBox2.Value
End If
Next

ListBox3.AddItem Mid(slist, 3) & tlist

end Sub

My query is how can i stop duplications from been entered into
listbox3?

--

Dave Peterson

I wondered why you did it that way.

If you add: Jan-Mar, Apr-Jun, Jul-Sep 2002
then what is considered a duplicate:

Does it have to be:
Jan-Mar, Apr-Jun, Jul-Sep 2002
or
are
Jan-Mar 2002
Apr-Jun 2002
Jul-Sep 2002
all duplicates

Would Quarterly Calendar Year make any other choice a duplicate?

This kind of thing would make the code more challenging. I think I would bite
the bullet and create one listbox with all the options for each year and not try
to combine two listboxes this way.

.AddItem "Jan-Mar 2002"
.AddItem "Apr-Jun 2002"
.AddItem "Jul-Sep 2002"
.AddItem "Oct-Dec 2002"
.AddItem "Quarterly Calendar Year 2002"
.AddItem "Jan-Mar 2003"
.AddItem "Apr-Jun 2003"
.AddItem "Jul-Sep 2003"
.AddItem "Oct-Dec 2003"
.AddItem "Quarterly Calendar Year 2004"
.AddItem "Jan-Mar 2004"
.AddItem "Apr-Jun 2004"
.AddItem "Jul-Sep 2004"
.AddItem "Oct-Dec 2004"
.AddItem "Quarterly Calendar Year 2004"

And those quarter calendar year options will even make this more difficult--you
shouldn't be able to seelect all 4 quarters and the quarterly calendar year for
the same year, right?



bluewatermist wrote:

Hi Dave

I tried your coding and it works but like my original one if you select for
example Jan-Mar and Apr-Jun and Jul-Sep and then select 2002 it would place
this in listbox3 as Jan-Mar, Apr-Jun, Jul-Sep 2002. I have tried to change
your code but without success. Are you able to give me a suggestion.

Many thanks.

"Dave Peterson" wrote:

I was confused about the slist and tlist, but this seemed to work ok for me.

I did add a label to indicate any errors:

Option Explicit
Private Sub UserForm_Initialize()
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

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 tlist = Me.ListBox3.List(iCtr) Then
OkToAdd = False
Exit For
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



bluewatermist wrote:

Hi

I'm hoping you can assist as i can't find any relevant information on my
problem. I have two listboxes on a userform. When a command button is
clicked, it combines listbox1 and listbox2 together and creates a period into
listbox3. Listbox1 & listbox2 information are created when the userform is
Initialised. listbox1 is a multi select and listbox2 is a single select.
Example below:

Private Sub UserForm_Initialize()

With Me.ListBox1
.AddItem "Jan-Mar"
.AddItem "Apr-Jun"
.AddItem "Jul-Sep"
.AddItem "Oct-Dec"
.AddItem "Quarterly Calendar Year"
end With

With Me.ListBox2
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
end With

end Sub

Private Sub CommandButton1_Click()

Dim slist As String
Dim tlist As String
Dim lIndex As Long

For lIndex = 0 To 22

If ListBox1.Selected(lIndex) Then
slist = slist & ", " & ListBox1.List(lIndex)
tlist = " " & ListBox2.Value
End If
Next

ListBox3.AddItem Mid(slist, 3) & tlist

end Sub

My query is how can i stop duplications from been entered into listbox3?

--

Dave Peterson


--

Dave Peterson

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?

"Dave Peterson" wrote:

I wondered why you did it that way.

If you add: Jan-Mar, Apr-Jun, Jul-Sep 2002
then what is considered a duplicate:

Does it have to be:
Jan-Mar, Apr-Jun, Jul-Sep 2002
or
are
Jan-Mar 2002
Apr-Jun 2002
Jul-Sep 2002
all duplicates

Would Quarterly Calendar Year make any other choice a duplicate?

This kind of thing would make the code more challenging. I think I would bite
the bullet and create one listbox with all the options for each year and not try
to combine two listboxes this way.

.AddItem "Jan-Mar 2002"
.AddItem "Apr-Jun 2002"
.AddItem "Jul-Sep 2002"
.AddItem "Oct-Dec 2002"
.AddItem "Quarterly Calendar Year 2002"
.AddItem "Jan-Mar 2003"
.AddItem "Apr-Jun 2003"
.AddItem "Jul-Sep 2003"
.AddItem "Oct-Dec 2003"
.AddItem "Quarterly Calendar Year 2004"
.AddItem "Jan-Mar 2004"
.AddItem "Apr-Jun 2004"
.AddItem "Jul-Sep 2004"
.AddItem "Oct-Dec 2004"
.AddItem "Quarterly Calendar Year 2004"

And those quarter calendar year options will even make this more difficult--you
shouldn't be able to seelect all 4 quarters and the quarterly calendar year for
the same year, right?



bluewatermist wrote:

Hi Dave

I tried your coding and it works but like my original one if you select for
example Jan-Mar and Apr-Jun and Jul-Sep and then select 2002 it would place
this in listbox3 as Jan-Mar, Apr-Jun, Jul-Sep 2002. I have tried to change
your code but without success. Are you able to give me a suggestion.

Many thanks.

"Dave Peterson" wrote:

I was confused about the slist and tlist, but this seemed to work ok for me.

I did add a label to indicate any errors:

Option Explicit
Private Sub UserForm_Initialize()
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

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 tlist = Me.ListBox3.List(iCtr) Then
OkToAdd = False
Exit For
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



bluewatermist wrote:

Hi

I'm hoping you can assist as i can't find any relevant information on my
problem. I have two listboxes on a userform. When a command button is
clicked, it combines listbox1 and listbox2 together and creates a period into
listbox3. Listbox1 & listbox2 information are created when the userform is
Initialised. listbox1 is a multi select and listbox2 is a single select.
Example below:

Private Sub UserForm_Initialize()

With Me.ListBox1
.AddItem "Jan-Mar"
.AddItem "Apr-Jun"
.AddItem "Jul-Sep"
.AddItem "Oct-Dec"
.AddItem "Quarterly Calendar Year"
end With

With Me.ListBox2
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
end With

end Sub

Private Sub CommandButton1_Click()

Dim slist As String
Dim tlist As String
Dim lIndex As Long

For lIndex = 0 To 22

If ListBox1.Selected(lIndex) Then
slist = slist & ", " & ListBox1.List(lIndex)
tlist = " " & ListBox2.Value
End If
Next

ListBox3.AddItem Mid(slist, 3) & tlist

end Sub

My query is how can i stop duplications from been entered into listbox3?

--

Dave Peterson


--

Dave Peterson

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.


bluewatermist wrote:

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?

"Dave Peterson" wrote:

I wondered why you did it that way.

If you add: Jan-Mar, Apr-Jun, Jul-Sep 2002
then what is considered a duplicate:

Does it have to be:
Jan-Mar, Apr-Jun, Jul-Sep 2002
or
are
Jan-Mar 2002
Apr-Jun 2002
Jul-Sep 2002
all duplicates

Would Quarterly Calendar Year make any other choice a duplicate?

This kind of thing would make the code more challenging. I think I would bite
the bullet and create one listbox with all the options for each year and not try
to combine two listboxes this way.

.AddItem "Jan-Mar 2002"
.AddItem "Apr-Jun 2002"
.AddItem "Jul-Sep 2002"
.AddItem "Oct-Dec 2002"
.AddItem "Quarterly Calendar Year 2002"
.AddItem "Jan-Mar 2003"
.AddItem "Apr-Jun 2003"
.AddItem "Jul-Sep 2003"
.AddItem "Oct-Dec 2003"
.AddItem "Quarterly Calendar Year 2004"
.AddItem "Jan-Mar 2004"
.AddItem "Apr-Jun 2004"
.AddItem "Jul-Sep 2004"
.AddItem "Oct-Dec 2004"
.AddItem "Quarterly Calendar Year 2004"

And those quarter calendar year options will even make this more difficult--you
shouldn't be able to seelect all 4 quarters and the quarterly calendar year for
the same year, right?



bluewatermist wrote:

Hi Dave

I tried your coding and it works but like my original one if you select for
example Jan-Mar and Apr-Jun and Jul-Sep and then select 2002 it would place
this in listbox3 as Jan-Mar, Apr-Jun, Jul-Sep 2002. I have tried to change
your code but without success. Are you able to give me a suggestion.

Many thanks.

"Dave Peterson" wrote:

I was confused about the slist and tlist, but this seemed to work ok for me.

I did add a label to indicate any errors:

Option Explicit
Private Sub UserForm_Initialize()
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

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 tlist = Me.ListBox3.List(iCtr) Then
OkToAdd = False
Exit For
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



bluewatermist wrote:

Hi

I'm hoping you can assist as i can't find any relevant information on my
problem. I have two listboxes on a userform. When a command button is
clicked, it combines listbox1 and listbox2 together and creates a period into
listbox3. Listbox1 & listbox2 information are created when the userform is
Initialised. listbox1 is a multi select and listbox2 is a single select.
Example below:

Private Sub UserForm_Initialize()

With Me.ListBox1
.AddItem "Jan-Mar"
.AddItem "Apr-Jun"
.AddItem "Jul-Sep"
.AddItem "Oct-Dec"
.AddItem "Quarterly Calendar Year"
end With

With Me.ListBox2
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
end With

end Sub

Private Sub CommandButton1_Click()

Dim slist As String
Dim tlist As String
Dim lIndex As Long

For lIndex = 0 To 22

If ListBox1.Selected(lIndex) Then
slist = slist & ", " & ListBox1.List(lIndex)
tlist = " " & ListBox2.Value
End If
Next

ListBox3.AddItem Mid(slist, 3) & tlist

end Sub

My query is how can i stop duplications from been entered into listbox3?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Ps. The class module stuff was stolen from John Walkenbach's site:
http://spreadsheetpage.com/index.php...one_procedure/

With minor changes to use checkboxes, not commandbuttons.

Dave Peterson wrote:

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.


bluewatermist wrote:

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?

"Dave Peterson" wrote:

I wondered why you did it that way.

If you add: Jan-Mar, Apr-Jun, Jul-Sep 2002
then what is considered a duplicate:

Does it have to be:
Jan-Mar, Apr-Jun, Jul-Sep 2002
or
are
Jan-Mar 2002
Apr-Jun 2002
Jul-Sep 2002
all duplicates

Would Quarterly Calendar Year make any other choice a duplicate?

This kind of thing would make the code more challenging. I think I would bite
the bullet and create one listbox with all the options for each year and not try
to combine two listboxes this way.

.AddItem "Jan-Mar 2002"
.AddItem "Apr-Jun 2002"
.AddItem "Jul-Sep 2002"
.AddItem "Oct-Dec 2002"
.AddItem "Quarterly Calendar Year 2002"
.AddItem "Jan-Mar 2003"
.AddItem "Apr-Jun 2003"
.AddItem "Jul-Sep 2003"
.AddItem "Oct-Dec 2003"
.AddItem "Quarterly Calendar Year 2004"
.AddItem "Jan-Mar 2004"
.AddItem "Apr-Jun 2004"
.AddItem "Jul-Sep 2004"
.AddItem "Oct-Dec 2004"
.AddItem "Quarterly Calendar Year 2004"

And those quarter calendar year options will even make this more difficult--you
shouldn't be able to seelect all 4 quarters and the quarterly calendar year for
the same year, right?



bluewatermist wrote:

Hi Dave

I tried your coding and it works but like my original one if you select for
example Jan-Mar and Apr-Jun and Jul-Sep and then select 2002 it would place
this in listbox3 as Jan-Mar, Apr-Jun, Jul-Sep 2002. I have tried to change
your code but without success. Are you able to give me a suggestion.

Many thanks.

"Dave Peterson" wrote:

I was confused about the slist and tlist, but this seemed to work ok for me.

I did add a label to indicate any errors:

Option Explicit
Private Sub UserForm_Initialize()
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

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 tlist = Me.ListBox3.List(iCtr) Then
OkToAdd = False
Exit For
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



bluewatermist wrote:

Hi

I'm hoping you can assist as i can't find any relevant information on my
problem. I have two listboxes on a userform. When a command button is
clicked, it combines listbox1 and listbox2 together and creates a period into
listbox3. Listbox1 & listbox2 information are created when the userform is
Initialised. listbox1 is a multi select and listbox2 is a single select.
Example below:

Private Sub UserForm_Initialize()

With Me.ListBox1
.AddItem "Jan-Mar"
.AddItem "Apr-Jun"
.AddItem "Jul-Sep"
.AddItem "Oct-Dec"
.AddItem "Quarterly Calendar Year"
end With

With Me.ListBox2
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
end With

end Sub

Private Sub CommandButton1_Click()

Dim slist As String
Dim tlist As String
Dim lIndex As Long

For lIndex = 0 To 22

If ListBox1.Selected(lIndex) Then
slist = slist & ", " & ListBox1.List(lIndex)
tlist = " " & ListBox2.Value
End If
Next

ListBox3.AddItem Mid(slist, 3) & tlist

end Sub

My query is how can i stop duplications from been entered into listbox3?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Hi Dave

I would love to have a copy of the workbook. There is a lot of info i need
to process and learn :)
My email address is

Many thanks for all your assistance. It's been a great help

Frederic

"Dave Peterson" wrote:

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.


bluewatermist wrote:

Hi Dave

if you add Jan-Mar, Apr-Jun, Jul-Sep 2002 then if for example selected