List Boxes

[Djblois]

I want to do two things with a listbox that I can't figure out yet.
1) I want the user to be able to select multiple Items in it.
2) If the user clicks on one of the option buttons in the form, it unselects everything in the listbox that the user has selected already.

[Bob Phillips]

1)Set the MultiSelect property of the Listbox to fmMultiSelectMulti

2)[vba]
Private Sub OptionButton1_Click()
Dim i As Long
If Me.OptionButton1 Then
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
.Selected(i) = False
End If
Next i
End With
End If
End Sub
[/vba]

[matthewspatrick]

I want to do two things with a listbox that I can't figure out yet.
1) I want the user to be able to select multiple Items in it.

In the properties for the ListBox, make MultiSelect = fmMultiSelectExtended

2) If the user clicks on one of the option buttons in the form, it unselects everything in the listbox that the user has selected already.

To select/deselect all:

[VBA]
Private Sub AllButton_Click()

Dim ListLength As Integer
Dim Counter As Integer

ListLength = ListBox1.ListCount

For Counter = 0 To ListLength - 1
ListBox1.Selected(Counter) = True 'or False to deselect
Next

End Sub
[/VBA]

[matthewspatrick]

Djblois,

The difference between fmMultiSelectMulti (Bob) and fmMultiSelectExtended (me) has to do with whether you can use Ctrl and/or Shift to select multiple items (Ctrl for individual items, Shift for all items between two points). fmMultiSelectExtended enables that behaviour.

[Djblois]

Ok both things work now. Unfortunately, now that I have it set to Multiselect-It won't unselect my option buttons. Here is the code I use for it:

[VBA]Email.workbook.Value = False
Email.Worksheet.Value = False[/VBA]

it works if I set it to accept only one sheet selected or the multiselect to 0

[Bob Phillips]

Works for me. What is the bigger context of that code?

[Djblois]

I am trying to create a form that gives the user three options to chose from:
1)Active workbook
2)Active Worksheet
3)A list box were the user can select multiple sheets within the workbook

after the user chooses what they want it is added as an attachment for an email.

[Bob Phillips]

Sorry, by context, I meant can you show all that code.

[Zack Barresse]

Slightly related, this adapted dialog sheet stems from Aaron Bloods original work of printing multiple sheets at once. I adapted it and added a Print All button, but you could adapt this to select multiple sheets to send via email...

[vba]Sub SelectSheets()
Dim i As Long, TopPos As Long, SheetCount As Long
Dim PrintDlg As DialogSheet, CurrentSheet As Worksheet
Dim cb As CheckBox, arrSheets() As String, wsCurr As Object
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
If Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible Then
SheetCount = SheetCount + 1
ReDim arrSheets(1 To SheetCount)
arrSheets(SheetCount) = CurrentSheet.Name
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i
TopPos = TopPos + 13
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount + 1).Text = "Print All"
PrintDlg.Buttons.Left = 240
With PrintDlg.DialogFrame
.Height = Application.WorksheetFunction.Max(68, PrintDlg.DialogFrame.Top + TopPos - 15)
.Width = 230
.Caption = "Select sheets to print"
End With
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
If cb.Text = "Print All" Then
Set wsCurr = ActiveSheet
Sheets(arrSheets).Select
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False
wsCurr.Activate
Exit For
End If
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If
Application.DisplayAlerts = False
PrintDlg.Delete
CurrentSheet.Select
End Sub[/vba]

HTH

[Djblois]

Here is the code. However I don't think it has anything to do with it, since it works with this code if I turn multiselect off.

[VBA]Private Sub ChangeSheets_Click()

Dim c As Control, F As Boolean
RNM = "ReportName"
LBL = "ReportLabel"
F = False
For Each c In Me.Controls
If c.Name = LBL Then
F = True
End If
Next c
If F = False Then
Email.Controls.Add bstrProgId:="Forms.Label.1", Name:=LBL, Visible:=True
Email.Controls(LBL).Top = 150
Email.Controls(LBL).Left = 6
Email.Controls(LBL).Height = 12
Email.Controls(LBL).Width = 182
Email.Controls(LBL).Caption = "What do you want to name the file?"
Email.Controls(LBL).Font.Bold = True
End If
For Each c In Me.Controls
If c.Name = RNM Then
F = True
End If
Next c
If F = False Then
Email.Controls.Add bstrProgId:="Forms.TextBox.1", Name:=RNM, Visible:=True
Email.Controls(RNM).Top = 166
Email.Controls(RNM).Left = 6
Email.Controls(RNM).Height = 14
Email.Controls(RNM).Width = 252
End If

Email.workbook.Value = False
Email.Worksheet.Value = False
End Sub[/VBA]

[Zack Barresse]

Why are you trying to add controls? Why would you not use a UserForm for this??

[Djblois]

I am adding the controls becuase I only want the controls to show up if a user clicks on the list box or the Activesheet. I don't have a problem adding the controls, that part works fine. I can't get it to unselect the two option buttons on the same form.

[Zack Barresse]

I don't understand why you don't drop the idea of programmatically creating controls when you can use a simple UserForm.

[Bob Phillips]

I accept that my test environment is not the same as yours, I have none of those controls, but I just ran it and it reset the buttons fine for me.

[Djblois]

Zack what do you mean? it adds the controls only when the user selects certain options. Otherwise, I don't want the controls there.

[Bob Phillips]

I think that he is suggesting that you should hide the controls and just show them when you need them. But as I say, I cannot replicate your problem so that shouldn't make any difference.

[Zack Barresse]

What I am saying is that I generally do not like the idea of programmatically adding controls when we can encapsulate them in a UserForm. To each their own though.

[Djblois]

Zack, I am trying to learn. Maybe your way would be more effective. Explain more? What do you mean Encapsulate them?

[Zack Barresse]

A userform with the controls, with a checkbox control with code such as ..

[vba]Private Sub CheckBox1_Click()
Dim i As Long
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = Me.CheckBox1.Value
Next i
End Sub[/vba]

Example workbook attached. HTH

[Bob Phillips]

I think this is getting confused. Isn't Email a userform already?