How to prevent list item selection

I'm having one of those blond moments (no offense to any blonds).  I have a 
simple list box to display information.  I want to immediately unselect any 
item that is selected.  None of the obvious items I've tried worked.  Any 
suggestions? 


0
Robert
12/9/2009 2:40:14 AM
excel.programming 6508 articles. 2 followers. Follow

8 Replies
1213 Views

Similar Articles

[PageSpeed] 14

Set the value to -1



"Robert Flanagan" <nospam@nospam.net> wrote in message 
news:%23wM31jHeKHA.6096@TK2MSFTNGP02.phx.gbl...
> I'm having one of those blond moments (no offense to any blonds).  I have 
> a simple list box to display information.  I want to immediately unselect 
> any item that is selected.  None of the obvious items I've tried worked. 
> Any suggestions?
> 


0
JLGWhiz
12/9/2009 3:45:25 AM
Sorry, ListIndex = -1


"Robert Flanagan" <nospam@nospam.net> wrote in message 
news:%23wM31jHeKHA.6096@TK2MSFTNGP02.phx.gbl...
> I'm having one of those blond moments (no offense to any blonds).  I have 
> a simple list box to display information.  I want to immediately unselect 
> any item that is selected.  None of the obvious items I've tried worked. 
> Any suggestions?
> 


0
JLGWhiz
12/9/2009 3:46:36 AM
You forgot to tell us where you got the ListBox from... the Control ToolBox 
toolbar or the Forms toolbar. Try one of these (whichever is relevant to 
your setup)...

Control ToolBox Toolbar
==========================
ToolBox:  WorkSheets("Sheet1").ListBox1.ListIndex = -1

Forms Toolbar
==========================
WorkSheets("Sheet1").ActiveSheet.Shapes("List Box 2"). _
                       OLEFormat.Object.ListIndex = 0

Change the name of the worksheet and the name of the ListBox to match the 
actual names used in your setup.

-- 
Rick (MVP - Excel)


"Robert Flanagan" <nospam@nospam.net> wrote in message 
news:%23wM31jHeKHA.6096@TK2MSFTNGP02.phx.gbl...
> I'm having one of those blond moments (no offense to any blonds).  I have 
> a simple list box to display information.  I want to immediately unselect 
> any item that is selected.  None of the obvious items I've tried worked. 
> Any suggestions?
> 

0
Rick
12/9/2009 3:57:19 AM
I think you meant:

WorkSheets("Sheet1").Shapes("List Box 2"). _
                     OLEFormat.Object.ListIndex = 0

or

ActiveSheet.Shapes("List Box 2"). _
            OLEFormat.Object.ListIndex = 0

=====
But another way is to go through the Listboxes collection:

Worksheets("Sheet1").ListBoxes("List box 2").ListIndex = 0



Rick Rothstein wrote:
> 
> You forgot to tell us where you got the ListBox from... the Control ToolBox
> toolbar or the Forms toolbar. Try one of these (whichever is relevant to
> your setup)...
> 
> Control ToolBox Toolbar
> ==========================
> ToolBox:  WorkSheets("Sheet1").ListBox1.ListIndex = -1
> 
> Forms Toolbar
> ==========================
> WorkSheets("Sheet1").ActiveSheet.Shapes("List Box 2"). _
>                        OLEFormat.Object.ListIndex = 0
> 
> Change the name of the worksheet and the name of the ListBox to match the
> actual names used in your setup.
> 
> --
> Rick (MVP - Excel)
> 
> "Robert Flanagan" <nospam@nospam.net> wrote in message
> news:%23wM31jHeKHA.6096@TK2MSFTNGP02.phx.gbl...
> > I'm having one of those blond moments (no offense to any blonds).  I have
> > a simple list box to display information.  I want to immediately unselect
> > any item that is selected.  None of the obvious items I've tried worked.
> > Any suggestions?
> >

-- 

Dave Peterson
0
Dave
12/9/2009 1:44:42 PM
Ron and JLG, the list box is on a user form.  I have the list index set 
initially at -1.  But the list box is clickable and the user can select any 
item.  I can trap the mouse up event and change the list index back to -1, 
but the item clicked is still blue.  Repaint doesn't change the item to 
unselected.

The only solution I have found is to set up a Do..Loop and hide and reshow 
the form via the mouse up event and a public variable. The mouse up event 
sets the variable to reshow and to hide the form.  And setting the listindex 
back to -1.  This just doesn't seem like an elegant solution <grin>.

Bob

"Robert Flanagan" <nospam@nospam.net> wrote in message 
news:%23wM31jHeKHA.6096@TK2MSFTNGP02.phx.gbl...
> I'm having one of those blond moments (no offense to any blonds).  I have 
> a simple list box to display information.  I want to immediately unselect 
> any item that is selected.  None of the obvious items I've tried worked. 
> Any suggestions?
> 


0
Robert
12/9/2009 1:56:59 PM
Thanks for catching that Dave (I had meant to post it the first way you 
showed).

-- 
Rick (MVP - Excel)


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4B1FA9CA.BF70732@verizonXSPAM.net...
>I think you meant:
>
> WorkSheets("Sheet1").Shapes("List Box 2"). _
>                     OLEFormat.Object.ListIndex = 0
>
> or
>
> ActiveSheet.Shapes("List Box 2"). _
>            OLEFormat.Object.ListIndex = 0
>
> =====
> But another way is to go through the Listboxes collection:
>
> Worksheets("Sheet1").ListBoxes("List box 2").ListIndex = 0
>
>
>
> Rick Rothstein wrote:
>>
>> You forgot to tell us where you got the ListBox from... the Control 
>> ToolBox
>> toolbar or the Forms toolbar. Try one of these (whichever is relevant to
>> your setup)...
>>
>> Control ToolBox Toolbar
>> ==========================
>> ToolBox:  WorkSheets("Sheet1").ListBox1.ListIndex = -1
>>
>> Forms Toolbar
>> ==========================
>> WorkSheets("Sheet1").ActiveSheet.Shapes("List Box 2"). _
>>                        OLEFormat.Object.ListIndex = 0
>>
>> Change the name of the worksheet and the name of the ListBox to match the
>> actual names used in your setup.
>>
>> --
>> Rick (MVP - Excel)
>>
>> "Robert Flanagan" <nospam@nospam.net> wrote in message
>> news:%23wM31jHeKHA.6096@TK2MSFTNGP02.phx.gbl...
>> > I'm having one of those blond moments (no offense to any blonds).  I 
>> > have
>> > a simple list box to display information.  I want to immediately 
>> > unselect
>> > any item that is selected.  None of the obvious items I've tried 
>> > worked.
>> > Any suggestions?
>> >
>
> -- 
>
> Dave Peterson 

0
Rick
12/9/2009 2:48:33 PM
Try this in the mouseup, and/or maybe in the Exit event if you want to cater 
for keyboard use

With Me.ListBox1
    .List = .List
End With

Curiosity, Why?

Regards,
Peter T

"Robert Flanagan" <nospam@nospam.net> wrote in message 
news:%23%230sAeNeKHA.2624@TK2MSFTNGP04.phx.gbl...
> Ron and JLG, the list box is on a user form.  I have the list index set 
> initially at -1.  But the list box is clickable and the user can select 
> any item.  I can trap the mouse up event and change the list index back 
> to -1, but the item clicked is still blue.  Repaint doesn't change the 
> item to unselected.
>
> The only solution I have found is to set up a Do..Loop and hide and reshow 
> the form via the mouse up event and a public variable. The mouse up event 
> sets the variable to reshow and to hide the form.  And setting the 
> listindex back to -1.  This just doesn't seem like an elegant solution 
> <grin>.
>
> Bob
>
> "Robert Flanagan" <nospam@nospam.net> wrote in message 
> news:%23wM31jHeKHA.6096@TK2MSFTNGP02.phx.gbl...
>> I'm having one of those blond moments (no offense to any blonds).  I have 
>> a simple list box to display information.  I want to immediately unselect 
>> any item that is selected.  None of the obvious items I've tried worked. 
>> Any suggestions?
>>
>
> 


0
Peter
12/9/2009 7:44:48 PM
Peter that works by replacing the list with itself.  Therefore it is the 
same as doing a clear and re-entering the list.  I did have to set the 
listindex to -1 however.  If I did not do so, the selection stayed 
highlighted in blue.

One problem with that appraoch is the list scrolls back to the top.  I 
tested the hide and reshow approach and the list stays as scrolled.  This is 
of value so it looks like hide and reshow is the approach.

Bob

"Peter T" <peter_t@discussions> wrote in message 
news:OUamPgQeKHA.4112@TK2MSFTNGP06.phx.gbl...
> Try this in the mouseup, and/or maybe in the Exit event if you want to 
> cater for keyboard use
>
> With Me.ListBox1
>    .List = .List
> End With
>
> Curiosity, Why?
>
> Regards,
> Peter T
>
> "Robert Flanagan" <nospam@nospam.net> wrote in message 
> news:%23%230sAeNeKHA.2624@TK2MSFTNGP04.phx.gbl...
>> Ron and JLG, the list box is on a user form.  I have the list index set 
>> initially at -1.  But the list box is clickable and the user can select 
>> any item.  I can trap the mouse up event and change the list index back 
>> to -1, but the item clicked is still blue.  Repaint doesn't change the 
>> item to unselected.
>>
>> The only solution I have found is to set up a Do..Loop and hide and 
>> reshow the form via the mouse up event and a public variable. The mouse 
>> up event sets the variable to reshow and to hide the form.  And setting 
>> the listindex back to -1.  This just doesn't seem like an elegant 
>> solution <grin>.
>>
>> Bob
>>
>> "Robert Flanagan" <nospam@nospam.net> wrote in message 
>> news:%23wM31jHeKHA.6096@TK2MSFTNGP02.phx.gbl...
>>> I'm having one of those blond moments (no offense to any blonds).  I 
>>> have a simple list box to display information.  I want to immediately 
>>> unselect any item that is selected.  None of the obvious items I've 
>>> tried worked. Any suggestions?
>>>
>>
>>
>
> 


0
Robert
12/10/2009 2:42:53 AM
Reply:

Similar Artilces:

preventing date from changing
I have an If condition that sets the date to "today()" true or "today() + 1" if false. My question is how do I prevent the date from changing? If the date is set for today, then tomorrow I don't want to "update". How can this be done? thanks You could copy the cell, and paste as values, before you close the file. (Edit>Paste Special, Values) Or, you could enter today's date in a cell in the row (Ctrl+; ), then refer to that cell in the formula. For example, instead of: =IF(A2="Local",TODAY(),TODAY()+1) enter: =IF(A2="Local...

generate a list from a single row of data
Using Excel 97, I have a spreadsheet of Project Reports, arranged across the sheet as follows : Column A thru I are templates, hidden from the Project Managers Column J I want to have a table of project names that are in the worksheet Column K is blank Column L is the labels/descriptions for rows going across the worksheet Rows 4 - 29 Various Dates/contacts etc. Rows 30--53 Month labels for 2 years of resource forecast data Column M, rows 4-29 blank rows 30-53 Project Manager absence (vacation/training) forecasts Column N is a Summary row of Project Managers forecast time for all projects ...

outlook feature to prevent forgetting attachments
Often people forget to send attachments with emails. Very often they will write something along the lines of "I have attached the minutes from our meeting" and then forget to atcually attach a file. Is there a way therefore that outlook can check the text of an email for words like attach, attached, attachment and query the user before sending if there does not appear to be an attachment? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree"...

Prevent the sales from Tendering when another Item is Scan at POS
Hello, We have serveral items that our Retail does have price assign to them. The cashier will scan the next item. They will not notice the last item they did not enter a price. Now the 2nd item is the price for the 1st item. Then the cashier will tender out the sales without noticing they have an incorrect total for this transaction. Then the store manager have to performed a post void for this transaction, which hold up the customer awaiting to check out. Have anyone else encounter this and know a way to limited sale from being total out of an acceptable sales amount...

Print Selected Items in Excel Order Form
Hi, I’m new to the site and also to Visual Basic. I have found the following code from Dave Peterson on http://www.contextures.com/xlForm03.html, which does more or less what I want apart from one thing, which I can’t figure out how to change. Values from the worksheet list, starting in Column B and moving right, are copied to the order form, into the addresses specified. Instead of copying values from Column B and moving right (as in B1, C1, D1...), I would like to copy to the form only selected cells like B1, D1, H1... Print Marked Items Code Option Base 0 Sub Print...

Discount Lists 05-09-05
Hi All, I'm new for MSCRM. I have a problem to setup discount list. We are distributor and selling hardwares to different resellers at three level which are end user, dealer and master dealer. Each type of customer has two or three different pricing based on the quality they purchase. My problem is that I do not want to create 3 type of discount lists on every product. The old system allowed manager or senior sale to change the price based on needs. But I felt the MSCRM is more complicated in term of handling the product catalog. Is there any input and recommedation? Thank in advance Ben ...

Tasks
Hi, I have a user who has quite approx 300 tasks in his folder, some of these tasks have people listed in the Update List, the users are constantly receiving messages saying that the task has been updated, this is really starting to annoy people. The update list of names is greyed out so cannot be altered, where can this be altered from? Thanks, -- Regards, Darryl Please remove NOSPAM from email address to reply to me. ...

prevent user from deleting a tab prevent running macro from menu
how can one prevent a user from 1. deleting a specific tab in a workbook? 2. prevent a user from running a macro from the tools menu (where user can only run a macro from a command button)? thanks! #1. Protect the workbook's structure. In xl2003 menus: tools|protection|protect workbook|check structure #2. Make the sub private: Sub Testme() becomes Private Sub testme() This will stop the user from seeing that subroutine--but if they know the name, they can still type it and run it. joemeshuggah wrote: > > how can one prevent a user from...

Stock count of serial numbered items
I have recurring issues with serial-numbered items and stock count entry. For instance, in my stock count I have a serial-numbered item with a captured quantity of 1. My counted quantity is zero. Therefore, I should have a variance of -1. I go into the Stock Count Serial Number Entry window and change the Count Status to Not Found for my one serial number. When I click on OK, I get "The variance or counted quantity for this serial number does not equal the variance or counted quantity for the item - Continue, Override, Cancel". If I click on Override, I get "The calc...

Updating a form after adding to a list?
Okay, I've got a form for filling in data. One of the fields in the form refers to a table of colors. I've restricted the field so the user can ONLY select a value from that field and I have a number of colors already entered into that table. However, there may be new colors later, so I have command button right on the form that open a small 'colors' form where the user can add a color. The colors form works without a problem, but when the user closes that form, the new color is not immediately available in the new form. I need to close the main form and then reopen it. ...

Contact list question
I now have my contacts listed online with a Windows Live ID. But, I notice the display in WLM is different ... depending on if I am logged into Windows Live ID or not. When I am not logged in, I see all my Groups (or Categories) but when I am logged in, I see none. The total contacts is the same. Is there any way to duplicate the Group/Categories when online? Tom You are looking at two independent contact lists. Any changes made while logged in will not be reflected when not logged in and vice versa. You would have to manually recreate the groups while logged in. ...

get a list of sequence number that meet 3 criteria 12-07-09
How do you get a list of sequence numbers of 5 that meet 3 criteria? There are 1-59. I want to get a list of sequence numbers that consists of 5 number being called. Below are 3 criteria which consist of 24 to 28 group and each group has 1-3 numbers. Only one number is call from each group and no group will have two number call in the same sequence. There should be 5 number called. For example: 16 35 37 42 51 criteria 1 criteria 2 criteria 3 group 1 59 group 1 50 group 1 50 group 2 58 group 2 40 group 2 40 51 group 3 49 group 3 51 group 3 30 41 52 gr...

Excel Lists
A few months ago client requested I create an "Excel Database" by which she meant an Excel list. Recently she sent it back for help because the insert row (the one with the asterisk) at the bottom disappeared. For the life of me I cannot figure out what the problem is. I have not been successful in getting it to reappear either. Anyone have any advice? You need xl2003+. Select your range data|List parksgirl@gmail.com wrote: > > A few months ago client requested I create an "Excel Database" by > which she meant an Excel list. Recently she sent it back for...

How to programmatically set a focus on the selected cell?
Hi, Using Excel automation, how to set a focus (rectangle around the cell) , by specifying Column No and Row No.? Thanks, Jack Cells(rownumber,columnnumber).select "Jack" wrote: > Hi, > Using Excel automation, how to set a focus (rectangle around the cell) > , by specifying Column No and Row No.? > Thanks, > Jack > > > Providing you have a properly declared worksheet object then... WS.Cells(Rw, Col).Select Rw is the row number and Col is the Column number and both should be a Long. Also, you do not need to Select in order to &q...

Vlookup from a drop down list
In Column A I have a drop down list from A17:A46 In Column B I have a formula =IF(A17="","",VLOOKUP(A17,'Hidden Pg for lookup Price'!A1:C29,2,FALSE)) this formula works for just the 1st row but if I copy it down it accelerates the rows =IF(A18="","",VLOOKUP(A18,'Hidden Pg for lookup Price'!A2:C30,2,FALSE)) I tried to put $'s around the $A$1:$C$29 but it then seems to freeze it up and will only show me the formula in the cell and not give me a result? Any ideas? Thank you!!!! <I tried to put $'s around the $A$1:$C$29...

List box value returns the row number of the list box range row
Hi, I was hoping someone could help me figure out what I am doing wrong. I have a list box set up on a worksheet and I want the user to select a value from the list box, and then have that value entered into a cell when a button is clicked. When I run/step through the macro, it takes a value and places it into the appropriate cell. The problem is that the value which is placed in the cell is not the value from the listbox, but the row number of the value that was chosen. Using the below row numbers and values as an example; if I select value 7 from the list box, the val...

Re: preventing distribution lists from printing
I work for a large corporation and I often get outlook mail with ver long distribution lists. Is there any way to prohibit these lenght list from printing so that I only get the message? Thank - buddy ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse.ms/message548641.htm ...

Prevent calculation of fields in split view
Hi all! I have following problem: Most of my forms are displayed in split view, within some of them i am doing different calculations, displaying the calculated values in text boxes. Since some of those calculations are quite time-consuming, i dont want Access to calc. the values for _each_ record in the split-view (which it is doing right now). I want it to just calc the values for the record currently viewed, is there any way to do this? best regards, --Michael ...

I'm looking for a To Do List template to use with Outlook.
I need to keep track of my tasks on a day to day basis and I'm looking for a template that I can use or modify to help me. An Excel spreadsheet will probably provide the best usage because it will allow me to sort and change my data around. Here is a standard To Do List that Microsoft offers as an Excel template: http://office.microsoft.com/en-us/templates/TC010185861033.aspx -- John Mansfield http://cellmatrix.net "Sharon J." wrote: > I need to keep track of my tasks on a day to day basis and I'm looking for a > template that I can use or modify to help ...

Macro needed to Paste Values and prevent Macro operation #2
Sub Macro1() ' ' Dim myBk As Workbook Set myBk = Workbooks.Open(Application.GetOpenFilename(, , "Select the File")) Range("To_Database").Copy ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp)(1).Offset(2, 1) myBk.Close False End Sub In the above macro I need to prevent macros operating in the External File when it opens. I also need the Named Range ("To_Database") from the External File to be copied into the file from which this macro operates as PasteValues. At the moment the Formula in the Named Range is coming across, so that I'm ...

Excel form ( or other) to list creditors for bankruptcy
I am looking for an Excel templete ( or other) to list creditors for bankruptcy. ...

Excel file to create a Distribution List
Is it possible to use an Excel file to create a Distribution List in Outlook? I am using 2002 SP3 for both Outlook and Excel. The Excel file has data in/for the following fields: customer number, Last Name, First Name, Phone Number, Email Address. Further, does it help, hinder or not matter at all that the "file" for the List has already been created? In other words, I have a created a list with title only ("John Smith's Group"), but the group itself has no data or entries in it. Thank you all in advance for the incredible assistance I receive from th...

Dropdown list of values in Report Filter dialog
There are a number of fields (department and category are prime examples) which, when you add them as a filter on the Report Filter dialog, will show a dropdown list of values from which to choose. I would love to have that functionality on some fields which I have added to custom reports. Fields such as Reason Code, Account Type, or Store Name (and other such fields where the plain-English value is stored in a separate table) are often used on my custom reports, and the users are constantly mistyping the value. If they could choose the value from a predefined list, it would make life ...

Automatically insert list entry
Hi, I'm simply creating a list of ancronyms and their definitions. I want to be able to enter an ancronym at the top of the excel document and have it be automatillcy inserted into the list in the correct alphabetical order. Any ideas on an easy solution, Thanks -- oliverj ------------------------------------------------------------------------ oliverj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25721 View this thread: http://www.excelforum.com/showthread.php?threadid=391310 Enter it anywhere in the list, but then select your range (both columns...

Prevent Forwarding #2
Hi. I need to know how to make an email message unable to be forwarded to a third party. For example if I sent an email to John, John couldn't then forward that message to someone else. Thanks!! Shannon <anonymous@discussions.microsoft.com> wrote: > Hi. I need to know how to make an email message unable to > be forwarded to a third party. For example if I sent an > email to John, John couldn't then forward that message to > someone else. Can't be done, in general. -- Brian Tillman You'd need to use a service such as those listed at http://www.slipst...