Change listbox value

**Stingone** · 10-23-2012, 11:15 AM

Hello,

I have created a sheet with a rowsource from A1:L19 in sheet1

What i want to do is change the value's in a userform. I have an example of how i like to see it.

http://www.excelforum.com/excel-prog...x-records.html

But in that case i need to create for every cell a separate textbox. Is there a easier way to manage/change this? I only need to have the buttons edit and change. How can i manage this?
See attached fileChange listbox.xlsm

**jaslake** · 11-05-2012, 03:13 PM

Hi Stingone

This code is in the attached...let me know of issues.

Option Explicit

Private Sub UserForm_Initialize()
    Dim x As Long
    Dim k As Long
    Dim j As Long
    x = Sheets("Blad1").UsedRange.Rows.Count
    ListBox1.List = Sheets("Blad1").UsedRange.Value

    For k = 1 To x
        For j = 3 To 12
            ListBox1.List(k - 1, j - 1) = Format(ListBox1.List(k - 1, j - 1), "0.00")
        Next
    Next
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Dim j As Long
    Dim x As Long

    x = Me.ListBox1.ListIndex + 1
    With Sheets("Blad1")
        For j = 1 To 12
            .Cells(x, j).Value = Me.Controls("TextBox" & j).Text
        Next j
    End With
End Sub

Private Sub ListBox1_Click()
    Dim k As Long
    Dim j As Long
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            k = Me.ListBox1.ListIndex
            '            Debug.Print Me.ListBox1.ListIndex
            For j = 1 To 12
                Me.Controls("TextBox" & j).Text = Me.ListBox1.List(k, j - 1)
            Next j
        End If
    Next Ctrl
End Sub

**Stingone** · 11-05-2012, 03:55 PM

Originally Posted by jaslake

Hi Stingone

This code is in the attached...let me know of issues.

Option Explicit

Private Sub UserForm_Initialize()
    Dim x As Long
    Dim k As Long
    Dim j As Long
    x = Sheets("Blad1").UsedRange.Rows.Count
    ListBox1.List = Sheets("Blad1").UsedRange.Value

    For k = 1 To x
        For j = 3 To 12
            ListBox1.List(k - 1, j - 1) = Format(ListBox1.List(k - 1, j - 1), "0.00")
        Next
    Next
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Dim j As Long
    Dim x As Long

    x = Me.ListBox1.ListIndex + 1
    With Sheets("Blad1")
        For j = 1 To 12
            .Cells(x, j).Value = Me.Controls("TextBox" & j).Text
        Next j
    End With
End Sub

Private Sub ListBox1_Click()
    Dim k As Long
    Dim j As Long
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            k = Me.ListBox1.ListIndex
            '            Debug.Print Me.ListBox1.ListIndex
            For j = 1 To 12
                Me.Controls("TextBox" & j).Text = Me.ListBox1.List(k, j - 1)
            Next j
        End If
    Next Ctrl
End Sub

When testing it gives an error "cant find library in Private Sub UserForm_Initialize()

**Stingone** · 11-05-2012, 04:09 PM

Error solved, problem with web components 11. The form is now showing but the values are not updated in the listbox when clicking on modify. The sheet update is working though

**jaslake** · 11-05-2012, 04:22 PM

Hi Stingone

Yes, the code as written will NOT update the ListBox...only the Sheet. Replace the code with this...it also updates the ListBox

Option Explicit

Private Sub UserForm_Initialize()
    Dim x As Long
    Dim k As Long
    Dim j As Long
    x = Sheets("Blad1").UsedRange.Rows.Count
    ListBox1.List = Sheets("Blad1").UsedRange.Value

    For k = 1 To x    'rows
        For j = 3 To 12    'columns
            ListBox1.List(k - 1, j - 1) = Format(ListBox1.List(k - 1, j - 1), "0.00")
        Next
    Next

    For j = 1 To 12
        Me.Controls("TextBox" & j).Text = ""
    Next j
    
    Me.ListBox1.ListIndex = -1
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Dim j As Long
    Dim x As Long

    x = Me.ListBox1.ListIndex + 1    'row index
    With Sheets("Blad1")
        For j = 1 To 12    'columns
            .Cells(x, j).Value = Me.Controls("TextBox" & j).Text
        Next j
    End With
    Call UserForm_Initialize
End Sub

Private Sub ListBox1_Click()
    Dim k As Long
    Dim j As Long
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            k = Me.ListBox1.ListIndex    'row selected
            '            Debug.Print Me.ListBox1.ListIndex
            For j = 1 To 12
                Me.Controls("TextBox" & j).Text = Me.ListBox1.List(k, j - 1)
            Next j
        End If
    Next Ctrl
End Sub

**Stingone** · 11-05-2012, 05:50 PM

Works fine

.. is there also a way that the textboxes can not be updated empty? when i now click on update with all field empty it gives an error

**jaslake** · 11-05-2012, 06:01 PM

Hi Stingone

I'm missing something in your explanation. I can fill all the TextBoxes with blanks and it updates as expected and no error is reported.
Please explain again.

**Stingone** · 11-06-2012, 04:44 PM

Originally Posted by jaslake

Hi Stingone

I'm missing something in your explanation. I can fill all the TextBoxes with blanks and it updates as expected and no error is reported.
Please explain again.

If you open the excel file and the userform disapears when clicking directly on the modify button without selecting a row it gives an 1004 error.

**Stingone** · 12-04-2012, 03:59 PM

Originally Posted by jaslake

Hi Stignone

See if this helps.



Option Explicit

Private Sub MenuButton_Click()
Unload Me
    Menu.Show
End Sub

Private Sub UserForm_Initialize()
    Dim x As Long
    Dim k As Long
    Dim j As Long
If MultiPage1.Value = 0 Then x = Sheets("INPUT BS").Range("D10:M50").Rows.Count
    ListBox1.List = Sheets("INPUT BS").Range("D10:M50").Value
    For k = 1 To x    'rows
        For j = 1 To 10    'columns
            ListBox1.List(k - 1, j - 1) = VBA.Format(ListBox1.List(k - 1, j - 1), "0")
        Next
    Next

    For j = 1 To 10
        Me.Controls("TextBox" & j).Text = ""
    Next j
    
If MultiPage1.Value = 1 Then x = Sheets("INPUT BS").Range("D56:M200").Rows.Count
    ListBox2.List = Sheets("INPUT BS").Range("D56:M200").Value
    For k = 1 To x    'rows
        For j = 1 To 10    'columns
            ListBox2.List(k - 1, j - 1) = VBA.Format(ListBox2.List(k - 1, j - 1), "0")
        Next
    Next

    For j = 1 To 10
        Me.Controls("TextBox" & j).Text = ""
    Next j
    
If MultiPage1.Value = 2 Then x = Sheets("INPUT BS").Range("D210:M257").Rows.Count
    ListBox3.List = Sheets("INPUT BS").Range("D210:M257").Value
    For k = 1 To x    'rows
        For j = 1 To 10    'columns
            ListBox3.List(k - 1, j - 1) = VBA.Format(ListBox3.List(k - 1, j - 1), "0")
        Next
    Next

    For j = 1 To 10
        Me.Controls("TextBox" & j).Text = ""
    Next j
If MultiPage1.Value = 3 Then x = Sheets("INPUT BS").Range("D263:M282").Rows.Count
    ListBox4.List = Sheets("INPUT BS").Range("D263:M282").Value
    For k = 1 To x    'rows
        For j = 1 To 10    'columns
            ListBox4.List(k - 1, j - 1) = VBA.Format(ListBox4.List(k - 1, j - 1), "0")
        Next
    Next

    For j = 1 To 10
        Me.Controls("TextBox" & j).Text = ""
    Next j

If MultiPage1.Value = 4 Then x = Sheets("INPUT BS").Range("D287:M502").Rows.Count
    ListBox5.List = Sheets("INPUT BS").Range("D287:M502").Value
    For k = 1 To x    'rows
        For j = 1 To 10    'columns
            ListBox5.List(k - 1, j - 1) = VBA.Format(ListBox5.List(k - 1, j - 1), "0")
        Next
    Next

    For j = 1 To 10
        Me.Controls("TextBox" & j).Text = ""
    Next j
    
End Sub


Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Dim j As Long
    Dim x As Long

    If Me.ListBox1.ListIndex = -1 Then
        MsgBox "No Data Selected"
        Exit Sub
    End If
    
    If Me.TextBox1.Value = "" Then Exit Sub

    x = Me.ListBox1.ListIndex + 1    'row index
    With Sheets("INPUT BS")
        For j = 4 To 13    'columns Start in Column 3 <-----------
            .Cells(x, j).Value = Me.Controls("TextBox" & j - 2).Text '<--Start in TextBox1
        Next j
    End With
    Call UserForm_Initialize
End Sub
Private Sub CommandButton3_Click()
    Unload Me
End Sub

Private Sub CommandButton4_Click()
    Dim j As Long
    Dim x As Long

    If Me.ListBox2.ListIndex = -1 Then
        MsgBox "No Data Selected"
        Exit Sub
    End If
    
    If Me.TextBox13.Value = "" Then Exit Sub

    x = Me.ListBox2.ListIndex + 1    'row index
    With Sheets("INPUT BS")
           For j = 4 To 13  'columns Start in Column 3 <-----------
            .Cells(x, j).Value = Me.Controls("TextBox" & j + 10).Text '<--Start in TextBox13
        Next j
    End With
    Call UserForm_Initialize
End Sub
Private Sub CommandButton5_Click()
    Unload Me
End Sub

Private Sub CommandButton6_Click()
    Dim j As Long
    Dim x As Long

    If Me.ListBox3.ListIndex = -1 Then
        MsgBox "No Data Selected"
        Exit Sub
    End If
    
    If Me.TextBox25.Value = "" Then Exit Sub

    x = Me.ListBox3.ListIndex + 1    'row index
    With Sheets("INPUT BS")
        For j = 4 To 13  'columns Start in Column 3 <-----------
            .Cells(x, j).Value = Me.Controls("TextBox" & j + 22).Text '<--Start in TextBox25
        Next j
    End With
    Call UserForm_Initialize
End Sub
Private Sub CommandButton7_Click()
    Unload Me
End Sub

Private Sub CommandButton8_Click()
    Dim j As Long
    Dim x As Long

    If Me.ListBox4.ListIndex = -1 Then
        MsgBox "No Data Selected"
        Exit Sub
    End If
    
    If Me.TextBox37.Value = "" Then Exit Sub

    x = Me.ListBox4.ListIndex + 1    'row index
    With Sheets("INPUT BS")
        For j = 4 To 13  'columns Start in Column 3 <-----------
            .Cells(x, j).Value = Me.Controls("TextBox" & j + 34).Text '<--Start in TextBox37
        Next j
    End With
    Call UserForm_Initialize
End Sub
Private Sub CommandButton9_Click()
    Unload Me
End Sub

Private Sub CommandButton10_Click()
    Dim j As Long
    Dim x As Long

    If Me.ListBox5.ListIndex = -1 Then
        MsgBox "No Data Selected"
        Exit Sub
    End If
    
    If Me.TextBox49.Value = "" Then Exit Sub

    x = Me.ListBox5.ListIndex + 1    'row index
    With Sheets("INPUT BS")
        For j = 4 To 13  'columns Start in Column 3 <-----------
            .Cells(x, j).Value = Me.Controls("TextBox" & j + 46).Text '<--Start in TextBox49
        Next j
    End With
    Call UserForm_Initialize
End Sub

Private Sub ListBox1_Click()
    Dim k As Long
    Dim j As Long
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            k = Me.ListBox1.ListIndex    'row selected
            '            Debug.Print Me.ListBox1.ListIndex
            For j = 1 To 10
                Me.Controls("TextBox" & j).Text = Me.ListBox1.List(k, j - 1)
            Next j
        End If
    Next Ctrl

    If Me.TextBox1.Value = "" Then
        MsgBox "Can't Select this Row"
        Exit Sub
    End If
End Sub

Private Sub ListBox2_Click()
    Dim k As Long
    Dim j As Long
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            k = Me.ListBox2.ListIndex    'row selected
            '            Debug.Print Me.ListBox2.ListIndex
               For j = 1 To 10
                Me.Controls("TextBox" & j + 12).Text = Me.ListBox2.List(k, j - 1) '<--Start in TextBox 13
            Next j
        End If
    Next Ctrl

    If Me.TextBox13.Value = "" Then
        MsgBox "Can't Select this Row"
        Exit Sub
    End If
End Sub

Private Sub Listbox3_Click()
    Dim k As Long
    Dim j As Long
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            k = Me.ListBox3.ListIndex    'row selected
            '            Debug.Print Me.Listbox3.ListIndex
             For j = 1 To 10
                Me.Controls("TextBox" & j + 24).Text = Me.ListBox2.List(k, j - 1) '<--Start in TextBox 25
            Next j
        End If
    Next Ctrl

    If Me.TextBox25.Value = "" Then
        MsgBox "Can't Select this Row"
        Exit Sub
    End If
End Sub

Private Sub Listbox4_Click()
    Dim k As Long
    Dim j As Long
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            k = Me.ListBox4.ListIndex    'row selected
            '            Debug.Print Me.Listbox4.ListIndex
            For j = 1 To 10
                 Me.Controls("TextBox" & j + 36).Text = Me.ListBox2.List(k, j - 1) '<--Start in TextBox 37
            Next j
        End If
    Next Ctrl

    If Me.TextBox37.Value = "" Then
        MsgBox "Can't Select this Row"
        Exit Sub
    End If
End Sub

Private Sub Listbox5_Click()
    Dim k As Long
    Dim j As Long
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            k = Me.ListBox5.ListIndex    'row selected
            '            Debug.Print Me.Listbox5.ListIndex
            For j = 1 To 10
                 Me.Controls("TextBox" & j + 48).Text = Me.ListBox2.List(k, j - 1) '<--Start in TextBox 48
            Next j
        End If
    Next Ctrl

    If Me.TextBox49.Value = "" Then
        MsgBox "Can't Select this Row"
        Exit Sub
    End If
End Sub

This should work using your example. But again getting error 381

**jaslake** · 12-04-2012, 04:20 PM

Hi Stignone

In the File I uploaded there is no UpDate Button...there is a Modify Button. So, are you saying "Nothing happens when you click the Modify Button" in the Sample File I posted?

**Stingone** · 12-04-2012, 04:38 PM

Hi John,

No your file is working Ok. but see the last code i have provided. That is my complete model. But getting the error 381. I used your code as example

**jaslake** · 12-04-2012, 04:50 PM

Hi Stignone

I can debug code on my file with my code but I've no way of knowing how to debug your code on your file without seeing your file and your code. Think about it.

Be happy to help...if I can.

**Stingone** · 12-04-2012, 05:12 PM

Originally Posted by jaslake

Hi Stignone

I can debug code on my file with my code but I've no way of knowing how to debug your code on your file without seeing your file and your code. Think about it.

Be happy to help...if I can.

Attached the original file. In worksheet INPUT BS i have the DATA. Red marked i not want to change only display in listbox. Green marked needs to be changed.

If you now run the file you will get the error 381.

Really hope you can help me out.

Original File.xlsm

Change listbox value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Re: Change listbox value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions