Edit Multi-Column List Box

[MSingh]

Hi,

I have ListBox1 with 7 columns of data & 7 Text Boxes to edit ListBox1 if Checkbox1 is ticked.

How can i:

1. Display ListBox1 values in the 7 TextBoxes if Checkbox1 is ticked?
2. Update Listbox1 with the edited values using command button not textbox change?

Thanking you

Mohamed

[HansV]

The following example uses just two text boxes but you can easily expand it:

Code: Select all

Private Sub CheckBox1_Click()
  If Me.CheckBox1 Then
    If Me.ListBox1.ListIndex = -1 Then
      MsgBox "Please select an item in the list box", vbExclamation
    Else
      Me.TextBox1 = Me.ListBox1.Column(0)
      Me.TextBox2 = Me.ListBox1.Column(1)
    End If
  End If
End Sub

Private Sub CommandButton1_Click()
  If Me.ListBox1.ListIndex = -1 Then
    MsgBox "Please select an item in the list box", vbExclamation
  Else
    Me.ListBox1.Column(0) = Me.TextBox1
    Me.ListBox1.Column(1) = Me.TextBox2
  End If
End Sub

[MSingh]

Hi,

Thank you for the quick reply.

When i click CommandButton1 i get:
Run-time Error '70':
Could not set the Column Property.
Permission Denied.

Thanks again
Mohamed

[HansV]

Is this a single-select list box or a multi-select list box?

[MSingh]

Hi,

It's a single-select ListBox.

Thanks
Mohamed

[HansV]

Could you post a stripped down copy of your workbook?

[rory]

Is the listbox bound to a range using RowSource?

[HansV]

I have received your workbook. As Rory guessed, ListBox1 is bound to a range through its RowSource property. You can't modify an entry in such a list box directly, you must modify the corresponding cells in the range that acts as row source instead.

To prevent the On Change and On Click events of the list box from firing all the time, you can use a module-level variable declared at the top of the userform module:

Code: Select all

Private EventsOff As Boolean

Add a line

Code: Select all

  If EventsOff Then Exit Sub

immediately below

Code: Select all

Private Sub ListBox1_Click()

and also below

Code: Select all

Private Sub ListBox1_Change()

Change the code for the update button to

Code: Select all

Private Sub cmdEdit_Click()
  Dim c As Long
  If Me.ListBox1.ListIndex = -1 Then
    MsgBox "Please select an item in the list box", vbExclamation
  Else
    EventsOff = True
    With Worksheets("FNB_Payees1")
      For c = 1 To 7
        .Cells(Me.ListBox1.ListIndex + 2, c).Value = Me.Controls("TextBox" & c).Value
      Next c
    End With
  End If
  EventsOff = False
End Sub

[HansV]

And here is modified code for your up and down buttons. I made the indenting consistent.

Code: Select all

Private Sub MoveDownButton_Click()
  Dim NumItems As Integer
  Dim i As Integer
  Dim ItemNum As Integer
  Dim TempItem As String
  Dim TempList()

  ' Selected item
  ItemNum = ListBox2.ListIndex
  If ItemNum >= ListBox2.ListCount - 1 Then Exit Sub

  ' Get list
  TempList = ListBox2.List

  ' Exchange items
  For i = 0 To ListBox2.ColumnCount - 1
    TempItem = TempList(ItemNum, i)
    TempList(ItemNum, i) = TempList(ItemNum + 1, i)
    TempList(ItemNum + 1, i) = TempItem
  Next i

  ' Set list
  ListBox2.List = TempList
  ' Change the list index
  ListBox2.Selected(ItemNum + 1) = True
  ListBox2.ListIndex = ItemNum + 1
End Sub

Private Sub MoveUpButton_Click()
  Dim NumItems As Integer
  Dim i As Integer
  Dim ItemNum As Integer
  Dim TempItem As String
  Dim TempList()
  Dim lngIndex As Long

  ' Selected item
  ItemNum = ListBox2.ListIndex
  If ItemNum <= 0 Then Exit Sub

  ' Get list
  TempList = ListBox2.List

  ' Exchange items
  For i = 0 To ListBox2.ColumnCount - 1
    TempItem = TempList(ItemNum, i)
    TempList(ItemNum, i) = TempList(ItemNum - 1, i)
    TempList(ItemNum - 1, i) = TempItem
  Next i

  ' Set list
  ListBox2.List = TempList
  ' Change the list index
  ListBox2.Selected(ItemNum - 1) = True
  ListBox2.ListIndex = ItemNum - 1
End Sub

[MSingh]

Hi,

Thank you very much.
Tested the code & it works perfectly!

Thanks again
Mohamed

[Spy_Bry]

Hi,

im having the same problem.

can i have sample of this sheet?.. pls.

thanks.

[HansV]

This thread is more than a year old. What problem do you want to solve?

[Spy_Bry]

Oh! I didnt notice.

I cant follow on the code in this discussion. I need a code that will update data in the listbox with multiple column.
Example i have textbox1 and textbox2, then mylistbox contains several columns. I want textbox1 and textbox2 to update info on column 1 and column two..

Plus., it will really help if theres a command button "ADD" if i want to add entered data on textbox instead of update.
As much as possible., adding new data will automatically check if no duplicate and if there is, not allowed.

Thank u so much

[HansV]

I don't have the workbook that was discussed in this thread any more. I'll see if I can come up with an example later on.

[Spy_Bry]

thanks a lot!.

and one more thing,

Code: Select all

dim x as Interger
x = 1
Do Until Sheets("StoreInfo").Cells(x, 2) = Me.Txtdescription.Text Or Sheets("StoreInfo").Cells(x, 2) = ""
            x = x + 1
        Loop
        
        If Sheets("StoreInfo").Cells(x, 2) <> "" Then
            'update master file of disers info
            Sheets("StoreInfo").Cells(x, 1) = Me.cboIDNo.Value

code above find the value of the textbox (Me.Txtdescription.Text) in a sheet (storeinfo) and then update if found. but if the value that match on the sheet is more than one, it doesnt update all

i want this code for updating of all order status if received or not base on certain criteria such as Sales order number, date range, etc..

[HansV]

I have attached a sample workbook.

EditList.xls

It contains a userform with a list box, two text boxes and some command buttons.

S00019.png

[Spy_Bry]

thanks again mr hanz. :)

[micklep]

Sorry to bring this post up... AGAIN but:

I am working on a sheet where a multi select list box is generated from a row source on a tab named settings. The user can select multiple items and then it will print them. as it is printing them i need it to mark a column on the settings page as printed. Everytime i tell it to go to add the text in it de-selects all items in the list box.

AAARRRGGGHHH HELP! Code is:

Code: Select all

Private Sub PrintBtn_Click()
 Dim n As Integer
 Dim Active As String
 Dim PrintCount As Integer
 Dim Validation As String

 PrintCount = 0
    
    With Me.InvoiceList
        For n = 0 To .ListCount - 1
            If .Selected(n) = True Then
            Active = .List(n, 1)
            PrintCount = PrintCount + 1
            
         Sheets("Settings").Select
    Cells.Find(What:=Active, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
activecell.offset(0,1).value = "Yes"           

  **** SNIP - PRINT CODE IS HERE   ****          
      
              End If
        Next n
    End With
    
    
    
Validation = MsgBox(PrintCount & " Invoice(s) Printed", vbInformation, "Complete")
 DeSelectAllBtn_Click
 
End Sub

Please help :(

[HansV]

Welcome to Eileen's Lounge!

The next to last line of the code is DeSelectAllBtn_Click. From the name, I'd guess that this deselects all items in the list box. So if you don't want to deselect the items in the list box, remove the line

DeSelectAllBtn_Click

[micklep]

Close but no cigar I'm afraid. The deselect procedure does deselect all but that runs after it has done what it needs to do.
Thanks anyway though