#1   Report Post  
Old June 20th 07, 03:54 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 26
Default ListBox Click Event

I have a listbox control on a user form in Excel. I want to add code
whenever an item in the listbox is selected/unselected. I assumed I could do
this in the "click" event of the list box. However, clicking on any of the
items in the listbox does not trigger the event. Can anyone help?

  #2   Report Post  
Old June 20th 07, 04:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,814
Default ListBox Click Event

xyz = Activesheet.ListBox1. Value
would put the selection into variable xyz for use in your code

"BHatMJ" wrote:

I have a listbox control on a user form in Excel. I want to add code
whenever an item in the listbox is selected/unselected. I assumed I could do
this in the "click" event of the list box. However, clicking on any of the
items in the listbox does not trigger the event. Can anyone help?

  #3   Report Post  
Old June 20th 07, 04:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 26
Default ListBox Click Event

The control is on a userform dialog box. It seems as if it should be
straight forward. Below is a code example:

Private Sub listbox1_Click()
'if any items in listbox1 are not selected, set "select all" checkbox to
false
For i = 0 to listbox1.ListCount - 1
If listbox1.Selected(i) = False Then
checkbox_all.Value = False
End If
Next
End Sub

"Steve" wrote:

xyz = Activesheet.ListBox1. Value
would put the selection into variable xyz for use in your code

"BHatMJ" wrote:

I have a listbox control on a user form in Excel. I want to add code
whenever an item in the listbox is selected/unselected. I assumed I could do
this in the "click" event of the list box. However, clicking on any of the
items in the listbox does not trigger the event. Can anyone help?

  #4   Report Post  
Old June 20th 07, 05:11 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default ListBox Click Event

Maybe using ListBox1_Change would work better.



BHatMJ wrote:

The control is on a userform dialog box. It seems as if it should be
straight forward. Below is a code example:

Private Sub listbox1_Click()
'if any items in listbox1 are not selected, set "select all" checkbox to
false
For i = 0 to listbox1.ListCount - 1
If listbox1.Selected(i) = False Then
checkbox_all.Value = False
End If
Next
End Sub

"Steve" wrote:

xyz = Activesheet.ListBox1. Value
would put the selection into variable xyz for use in your code

"BHatMJ" wrote:

I have a listbox control on a user form in Excel. I want to add code
whenever an item in the listbox is selected/unselected. I assumed I could do
this in the "click" event of the list box. However, clicking on any of the
items in the listbox does not trigger the event. Can anyone help?


--

Dave Peterson
  #5   Report Post  
Old June 20th 07, 05:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 26
Default ListBox Click Event

I attempted that but the checkbox and the listbox are dependent on each
other. Every time the check box is clicked it changes the selection status
in the lsitbox and triggers the listbox change event. On the other hand,
when the listbox item is selected, it triggers the click event of the
checkbox. It needs to be the Click event.

Here is the situation (should be simple, right?):

checkbox for "Select All Items" in listbox
listbox items - if all items are NOT selected, checkbox should be false






"Dave Peterson" wrote:

Maybe using ListBox1_Change would work better.



BHatMJ wrote:

The control is on a userform dialog box. It seems as if it should be
straight forward. Below is a code example:

Private Sub listbox1_Click()
'if any items in listbox1 are not selected, set "select all" checkbox to
false
For i = 0 to listbox1.ListCount - 1
If listbox1.Selected(i) = False Then
checkbox_all.Value = False
End If
Next
End Sub

"Steve" wrote:

xyz = Activesheet.ListBox1. Value
would put the selection into variable xyz for use in your code

"BHatMJ" wrote:

I have a listbox control on a user form in Excel. I want to add code
whenever an item in the listbox is selected/unselected. I assumed I could do
this in the "click" event of the list box. However, clicking on any of the
items in the listbox does not trigger the event. Can anyone help?


--

Dave Peterson



  #6   Report Post  
Old June 20th 07, 08:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default ListBox Click Event

Since you're really only using the checkbox to cause something to happen--not to
indicate a yes/no, on/off, 1/0 situation, how about an alternative?

How about using a couple of commandbuttons instead?

You'll find that the code is easier and as a user, I _think_ I've seen more
buttons do the "select all/Unselect All" processing than checkboxes.

Anyway, I put 3 commandbuttons on a userform and a little listbox.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
.Selected(iCtr) = True
Next iCtr
End With
End Sub
Private Sub CommandButton3_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
.Selected(iCtr) = False
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
For iCtr = 1 To 5
.AddItem "A" & iCtr
Next iCtr
End With

Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Select All"
Me.CommandButton3.Caption = "Unselect All"

End Sub



BHatMJ wrote:

I attempted that but the checkbox and the listbox are dependent on each
other. Every time the check box is clicked it changes the selection status
in the lsitbox and triggers the listbox change event. On the other hand,
when the listbox item is selected, it triggers the click event of the
checkbox. It needs to be the Click event.

Here is the situation (should be simple, right?):

checkbox for "Select All Items" in listbox
listbox items - if all items are NOT selected, checkbox should be false

"Dave Peterson" wrote:

Maybe using ListBox1_Change would work better.



BHatMJ wrote:

The control is on a userform dialog box. It seems as if it should be
straight forward. Below is a code example:

Private Sub listbox1_Click()
'if any items in listbox1 are not selected, set "select all" checkbox to
false
For i = 0 to listbox1.ListCount - 1
If listbox1.Selected(i) = False Then
checkbox_all.Value = False
End If
Next
End Sub

"Steve" wrote:

xyz = Activesheet.ListBox1. Value
would put the selection into variable xyz for use in your code

"BHatMJ" wrote:

I have a listbox control on a user form in Excel. I want to add code
whenever an item in the listbox is selected/unselected. I assumed I could do
this in the "click" event of the list box. However, clicking on any of the
items in the listbox does not trigger the event. Can anyone help?


--

Dave Peterson


--

Dave Peterson
  #7   Report Post  
Old June 21st 07, 09:34 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 26
Default ListBox Click Event

Works for me -- thanks!

"Dave Peterson" wrote:

Since you're really only using the checkbox to cause something to happen--not to
indicate a yes/no, on/off, 1/0 situation, how about an alternative?

How about using a couple of commandbuttons instead?

You'll find that the code is easier and as a user, I _think_ I've seen more
buttons do the "select all/Unselect All" processing than checkboxes.

Anyway, I put 3 commandbuttons on a userform and a little listbox.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
.Selected(iCtr) = True
Next iCtr
End With
End Sub
Private Sub CommandButton3_Click()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
.Selected(iCtr) = False
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
For iCtr = 1 To 5
.AddItem "A" & iCtr
Next iCtr
End With

Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Select All"
Me.CommandButton3.Caption = "Unselect All"

End Sub



BHatMJ wrote:

I attempted that but the checkbox and the listbox are dependent on each
other. Every time the check box is clicked it changes the selection status
in the lsitbox and triggers the listbox change event. On the other hand,
when the listbox item is selected, it triggers the click event of the
checkbox. It needs to be the Click event.

Here is the situation (should be simple, right?):

checkbox for "Select All Items" in listbox
listbox items - if all items are NOT selected, checkbox should be false

"Dave Peterson" wrote:

Maybe using ListBox1_Change would work better.



BHatMJ wrote:

The control is on a userform dialog box. It seems as if it should be
straight forward. Below is a code example:

Private Sub listbox1_Click()
'if any items in listbox1 are not selected, set "select all" checkbox to
false
For i = 0 to listbox1.ListCount - 1
If listbox1.Selected(i) = False Then
checkbox_all.Value = False
End If
Next
End Sub

"Steve" wrote:

xyz = Activesheet.ListBox1. Value
would put the selection into variable xyz for use in your code

"BHatMJ" wrote:

I have a listbox control on a user form in Excel. I want to add code
whenever an item in the listbox is selected/unselected. I assumed I could do
this in the "click" event of the list box. However, clicking on any of the
items in the listbox does not trigger the event. Can anyone help?

--

Dave Peterson


--

Dave Peterson



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Listbox 2 takes the value of Listbox 1 Illya Teideman Excel Discussion (Misc queries) 3 April 10th 07 03:20 PM
On click button event [email protected] Excel Worksheet Functions 1 November 30th 06 05:39 PM
listbox B conditional of input in Listbox A Kim K Excel Discussion (Misc queries) 1 October 31st 06 09:27 PM
Click event for checkbox from Forms toolbar Carolyn Excel Discussion (Misc queries) 6 September 11th 06 08:16 PM
call a function on control click event tkraju via OfficeKB.com Excel Discussion (Misc queries) 7 August 25th 06 07:22 AM


All times are GMT +1. The time now is 09:51 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017