locked
VBA - ListBox1.ListIndex is always 0, doesnt change when a different index is selected. RRS feed

  • Question

  • Hi All, I have written the following code to Change the Quantity of the selected Item in the ActiveX Listbox on my Worksheet.

    ' CHANGES THE QTY AFTER THE SPINNER IS PRESSED, CHANGES SELECTED ITEM
    Sub SPIN()
    
    Dim C As Range
    Dim REF As String
    Dim ID As Integer
    Dim BC As String
    Dim QTY As Integer
    
    If ListBox1.ListIndex < 0 Then
        MsgBox Prompt:="PLEASE SELECT AN ITEM TO CHANGE THE QUANTITY OF", Buttons:=vbOKOnly + vbInformation, Title:="Fit2Run EPOS - ITEM QUANTITY"
        Exit Sub
    End If
    
    ID = ListBox1.ListIndex
    REF = ListBox1.List(ID, 7)
    BC = ListBox1.List(ID, 0)
    
    With Worksheets("SALE").Range("INUM_RANGE")
                Set C = .Find(REF, LookIn:=xlValues, Lookat:=xlWhole)
                    If Not C Is Nothing Then
                        QTY = Range("Z" & C.ROW).Value
                        
                        ActiveSheet.Spinners("Spinner 13").Value = QTY
                        ActiveSheet.Spinners("Spinner 13").LinkedCell = Range("Z" & C.ROW).Address
                    End If
            End With
    End Sub
    

    It all works fine and changes the value apart from the line - "ID = Listbox1.Listindex" For some reason, no matter what item you select in the list box, it always seams to think that the selected Listindex is 0, The first item.

    Is there something wrong with my code making this happen, or can anyone see a reason / solution to this?

    Thanks for your help,

    Tom.

    PS: I am using Excel 2007 and the Listbox is an ActiveX listbox on a worksheet. The Spinner I am using is a Form Control Spinner also on my Worksheet.

     

    Tuesday, January 31, 2012 10:08 PM

Answers

  • Hi All, Thanks for all you help with this, It just seamed to be one error after the next so I worked around using a spinner and I used two buttons on the ribbon instead.

    This is the code that I used instead.

    Dim UD as Boolean
    ' ADDS TO QTY
    Sub CALL_UP(Control As IRibbonControl)
    
    If Sheets("SALE").ListBox1.ListIndex < 0 Then
        MsgBox prompt:="PLEASE SELECT AN ITEM TO CHANGE THE QUANTITY OF", Buttons:=vbOKOnly + vbInformation, Title:="ITEM QUANTITY"
        Exit Sub
    Else
        UD = True
        Call QTY_AMT
    End If
    End Sub
    ' REMOVES FROM QTY
    Sub CALL_DOWN(Control As IRibbonControl)
    
    If Sheets("SALE").ListBox1.ListIndex < 0 Then
        MsgBox prompt:="PLEASE SELECT AN ITEM TO CHANGE THE QUANTITY OF", Buttons:=vbOKOnly + vbInformation, Title:="ITEM QUANTITY"
        Exit Sub
    Else
        UD = False
        Call QTY_AMT
    End If
    End Sub
    'SETS QTY VALUE
    Sub QTY_AMT()
    
    Dim C As Range
    Dim BC As String
    Dim REF As String
    Dim ID As Integer
    Dim QTY As Integer
    
    ID = Sheets("SALE").ListBox1.ListIndex
    REF = Sheets("SALE").ListBox1.List(ID, 7)
    BC = Sheets("SALE").ListBox1.List(ID, 0)
    
        With Worksheets("SALE").Range("INUM_RANGE")
                Set C = .Find(REF, LookIn:=xlValues, Lookat:=xlWhole)
                    If Not C Is Nothing Then
                        QTY = Range("Z" & C.Row).Value
                                                                    
                        Sheets("SALE").Unprotect
                            
                            If UD = True Then     
                             Range("Z" & C.Row).Value = QTY + 1                            
                            Else
                             Range("Z" & C.Row).Value = QTY - 1
                            End If
                        Sheets("SALE").Protect
                      
                    End If
        End With
    End Sub
    

    Thanks again for your help

    Tom

    • Marked as answer by t0m46 Thursday, February 2, 2012 6:00 PM
    Thursday, February 2, 2012 6:00 PM

All replies

  • Since the Spin procedure is in a standard module, it is not clear what ListBox1 refers to. Try referring to

    Worksheets("Sheet1").ListBox1
    

    throughout, where Sheet1 is the name of the worksheet, or somewhat shorter to

    Sheet1.ListBox1
    

    where Sheet1 is the code name of the sheet, i.e. its (Name) property.


    Regards, Hans Vogelaar
    Tuesday, January 31, 2012 10:28 PM
  • Hi Thanks for your reply, It doesnt seam to make a difference though. I have added a few break points and it appears that when you click on the spinner (Form control or ActiveX I have tried both) before the code is run, the selection in the listbox selection changes to the first one. I thought it may be that the spinner is changing the selection up or down, but no matter what you press it always selects the first item before the code is run.

    Any Ideas?

    Thanks

    Tom

    Wednesday, February 1, 2012 9:59 AM
  • I am guessing that it is the failure to specify sheet name that is one problem. Also you will have a problem with the values of the linked cell changing the previous set value as well as the current setting. I think you need to use an alternative method of writing the values and not used the Spinner linked cell.

    If your code works without the sheet name associated with the ListBox then that suggests that you have the code in the worksheet module and it really should be in a standard module.

    See the following and note all of my comments between the asterisk lines.

    '**********************************************************************
    'Following code should be in a standard module; not a worksheet module
    '**********************************************************************
    Sub SPIN()

        Dim C As Range
        Dim REF As String
        Dim ID As Integer
        Dim BC As String
        Dim QTY As Integer
       
        '******************************************************************
        'Insert the sheet name as follows (Edit "Sheet1" to your sheet name)
        If Sheets("Sheet1").ListBox1.ListIndex < 0 Then
        '********************************************************************
       
            MsgBox Prompt:="PLEASE SELECT AN ITEM TO CHANGE THE QUANTITY OF", _
                    Buttons:=vbOKOnly + vbInformation, Title:="Fit2Run EPOS - ITEM QUANTITY"
            Exit Sub
        End If


        '*********************************************
        'Insert the sheet name as follows (Edit "Sheet1" to your sheet name)
        ID = Sheets("Sheet1").ListBox1.ListIndex
        REF = Sheets("Sheet1").ListBox1.List(ID, 7)
        BC = Sheets("Sheet1").ListBox1.List(ID, 0)
        '********************************************
       
       
        With Worksheets("SALE").Range("INUM_RANGE")
           Set C = .Find(REF, LookIn:=xlValues, Lookat:=xlWhole)

                If Not C Is Nothing Then


                    '***************************************
                    'Insert the sheet name in the following line (Edit "SALE" to correct sheet name)
                    'I am guessing this is one of your problems.
                    QTY = Sheets("SALE").Range("Z" & C.Row).Value
                                  
                    'Specify the sheet name in lieu of ActiveSheet in following lines.
                    'Probably will need to simply write the value to the address and
                    'not reset the linked cell address for the Spinner.
                    ActiveSheet.Spinners("Spinner 13").Value = QTY
                    ActiveSheet.Spinners("Spinner 13").LinkedCell = Range("Z" & C.Row).Address
                    '**********************************************************
                   
                End If
        End With
       
    End Sub


    Regards, OssieMac
    Wednesday, February 1, 2012 12:02 PM
  • Hi I have now Managed to get it working, The pnly problem now is that the Spinner changes value before the code is run, This means that the there is always an extra 1 added or removed from the old selected item.

    Is there a way to run the code, before it changes value?

    Thanks

    Wednesday, February 1, 2012 1:56 PM
  • The pnly problem now is that the Spinner changes value before the code is run, This means that the there is always an extra 1 added or removed from the old selected item.

    Is there a way to run the code, before it changes value?


    That was my comment in my previous post. You could try the code below and remove the linked cell when the ListBox is clicked. The code goes in the Sheet module.


    Also I suggest that if you are still having problems then you post your code again but edit your code and insert the sheet names along with the ranges and refrain from using ActiveSheet and insert actual sheet names. That way when you post your code for others to help with it makes it much easier to determine which sheets you are referring to. I have been guessing while trying to solve this problem and one can never be sure that the solution is correct.

    Also need an explanation of why you are using the spinner and linked cell so that I can determine if there is another way of achieving your desired end result.

    Private Sub ListBox1_Click()
        ActiveSheet.Spinners("Spinner 13").LinkedCell = ""
    End Sub


    Regards, OssieMac
    Wednesday, February 1, 2012 8:05 PM
  • Hi All, Thanks for all you help with this, It just seamed to be one error after the next so I worked around using a spinner and I used two buttons on the ribbon instead.

    This is the code that I used instead.

    Dim UD as Boolean
    ' ADDS TO QTY
    Sub CALL_UP(Control As IRibbonControl)
    
    If Sheets("SALE").ListBox1.ListIndex < 0 Then
        MsgBox prompt:="PLEASE SELECT AN ITEM TO CHANGE THE QUANTITY OF", Buttons:=vbOKOnly + vbInformation, Title:="ITEM QUANTITY"
        Exit Sub
    Else
        UD = True
        Call QTY_AMT
    End If
    End Sub
    ' REMOVES FROM QTY
    Sub CALL_DOWN(Control As IRibbonControl)
    
    If Sheets("SALE").ListBox1.ListIndex < 0 Then
        MsgBox prompt:="PLEASE SELECT AN ITEM TO CHANGE THE QUANTITY OF", Buttons:=vbOKOnly + vbInformation, Title:="ITEM QUANTITY"
        Exit Sub
    Else
        UD = False
        Call QTY_AMT
    End If
    End Sub
    'SETS QTY VALUE
    Sub QTY_AMT()
    
    Dim C As Range
    Dim BC As String
    Dim REF As String
    Dim ID As Integer
    Dim QTY As Integer
    
    ID = Sheets("SALE").ListBox1.ListIndex
    REF = Sheets("SALE").ListBox1.List(ID, 7)
    BC = Sheets("SALE").ListBox1.List(ID, 0)
    
        With Worksheets("SALE").Range("INUM_RANGE")
                Set C = .Find(REF, LookIn:=xlValues, Lookat:=xlWhole)
                    If Not C Is Nothing Then
                        QTY = Range("Z" & C.Row).Value
                                                                    
                        Sheets("SALE").Unprotect
                            
                            If UD = True Then     
                             Range("Z" & C.Row).Value = QTY + 1                            
                            Else
                             Range("Z" & C.Row).Value = QTY - 1
                            End If
                        Sheets("SALE").Protect
                      
                    End If
        End With
    End Sub
    

    Thanks again for your help

    Tom

    • Marked as answer by t0m46 Thursday, February 2, 2012 6:00 PM
    Thursday, February 2, 2012 6:00 PM