Where's the Problem - Waldo?

[gheyman]

This Code worked and all of a sudden it doesnt. I dont recall making any changes that would have caused it not to work. I dont get an error message. Its just no longer listed the selections from ListBox1 in Column L

Private Sub CommandButton2_Click()

    Range("L1:L200").Select
    Selection.ClearContents
'
Dim Msg As String
    Dim i As Integer
    Dim c As Integer
    Dim NextRow As Long
    Dim LastRow As Long

    Worksheets("Tables").Select
    
    Range("L1").Select
'   Determine the next empty row
    NextRow = Application.WorksheetFunction. _
       CountA(Range("L:L")) + 1
'   Transfer the selected items from ListBox
    For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
c = c + 1
Cells(LastRow + c, 12) = ListBox1.List(i, 0)
End If
Next i
'
'    Sheets("Tables").Calculate

End Sub

 

[Marcelo Branco]

Hi,

What the code is doing with NextRow?

First the code gets the NextRow with

NextRow = Application.WorksheetFunction. _
CountA(Range("L:L")) + 1

but adds (copies) the values using LastRow that was never initialized in the code...

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
c = c + 1
Cells(LastRow + c, 12) = ListBox1.List(i, 0)
End If
Next i

It seems that the code is overwriting values in rows 1, 2, 3...in column L

Is this correct?

maybe
Cells(NextRow + c, 12) = ListBox1.List(i, 0)

M.

 

[Marcelo Branco]

I didnt notice that you are clearing column L at the beggining of the code. So i cant see the reason to get the NextRow.

M.

 

[Norie]

Try this.

Option Explicit
 
Private Sub CommandButton2_Click()
Dim wsTbls As Worksheet
Dim Msg As String
Dim i As Integer
Dim c As Integer
Dim NextRow As Long
Dim LastRow As Long
 
    Set wsTbls = Worksheets("Tables")

    wsTbls.Range("L1:L200").ClearContents
    ' Determine the next empty row
    NextRow = Application.WorksheetFunction.CountA(wsTbls.Range("L:L")) + 1
    
    ' Transfer the selected items from ListBox
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            c = c + 1
            wsTbls.Cells(LastRow + c, 12) = ListBox1.List(i, 0)
        End If
    Next i

End Sub

 

[gheyman]

Taking out LastRow does not seem to correct the problem.
Nori's suggestion almost works.

It doesnt put a value in L1 until I exit out of the userform where the CommandButton2 is located. But it is only putting the first selection from the listbox on the Tables tab. There are multiple selections being made in the listbox.

What I really dont get is that this WAS working. Unfortionatley I didnt save a copy!

 

[Norie]

What I changed was to add a worksheet reference to Cells etc.

Without that they would have referred to whatever VBA/Excel considered the active sheet.

Where exactly are the values that have been selected from the listbox meant to go?

Is the first selected item supposed to goto L1, the next to L2 and so on?

So if there are 4 items selected they goto L1:L4?

 

[gheyman]

Yes, They should be listing down Column L

Thanks Once again for the help. I couldnt have gotten this far without all your help over time.

 

[gheyman]

Yes, They should be listing down Column L

Thanks Once again for the help. I couldnt have gotten this far without all your help over time.

 

[Norie]

Is that all the code you have?

I've just tried the posted code, with the changes I made, and it works fine.

 

[gheyman]

I have code for UserForm_Initialization But I turned it off and tried it and still nothing.

I am going to completely delete all the code associated with UserForm5 where the Button is and the ListBox is. Let me try that.