A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

ListBox Click Event



 
 
Thread Tools Display Modes
  #1  
Old June 20th 07, 03:54 PM posted to microsoft.public.excel.misc
BHatMJ
external usenet poster
 
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?
Ads
  #2  
Old June 20th 07, 04:28 PM posted to microsoft.public.excel.misc
Steve
external usenet poster
 
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  
Old June 20th 07, 04:39 PM posted to microsoft.public.excel.misc
BHatMJ
external usenet poster
 
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  
Old June 20th 07, 05:11 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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  
Old June 20th 07, 05:22 PM posted to microsoft.public.excel.misc
BHatMJ
external usenet poster
 
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  
Old June 20th 07, 08:12 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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  
Old June 21st 07, 09:34 PM posted to microsoft.public.excel.misc
BHatMJ
external usenet poster
 
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
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 04:39 PM
listbox B conditional of input in Listbox A Kim K Excel Discussion (Misc queries) 1 October 31st 06 08: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 11:30 PM.


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