List Box Trouble Run Time Error

[jwalma]

I am trying to move string from a list box when I have them Selected and a Check box is true; when these two are true the click of the command buttion should add them to the defined sheet. Please review my code and tell me what I am not defining?

Please Login or Register  to view this content.

New to VBA and looking for any help to complete this

Thank you,

[Leith Ross]

Hello jwalma,

Welcome to the Forum!

It would help to see your workbook. That will reduce the number of questions and get you an answer sooner.

To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Picture

[jwalma]

I have posted my Excel workbook also i have not activated the Userform yet so the only way to activate it is with vba open and starting from there. Which i am building the forms first and then the command button page to open them this is one of 4 forms i am going to use.

[Leith Ross]

Hello jwalma,

Thanks for posting the workbook. That saved asking a lot of questions about your layout and how you had the ListBox setup. Here is the macro code. The macro has been added to the attached workbook.

Please Login or Register  to view this content.

[jwalma]

That is what I was trying to do but I am having a hard time with Dim statements on what they do or how they work could you explain that as well great to know that the code is possible but would like a better understanding also.

Thank you very much for the help on this.

[jwalma]

I have another Twist to this and I have changed the code and it works to a point with the list box. The issue is if you pick a different type from the defect list it does not work do I need to add another DIM to this and add another if statement? and would I name the DIM as BRKS? As I have four sheets that I will be taking information from and adding it to possibly the 9 sheets that have the 2 letter codes.

[CODE]


Private Sub ADDBTN_Click()

Dim i As Integer
Dim n As Long
Dim Rng As Range

Set Rng = Worksheets("CH").Cells(Rows.Count, "A").End(xlUp)
Set Rng = Worksheets("BX").Cells(Rows.Count, "A").End(xlUp)
Set Rng = Worksheets("FT").Cells(Rows.Count, "A").End(xlUp)
Set Rng = Worksheets("IM").Cells(Rows.Count, "A").End(xlUp)
Set Rng = Worksheets("GO").Cells(Rows.Count, "A").End(xlUp)
Set Rng = Worksheets("AR").Cells(Rows.Count, "A").End(xlUp)
Set Rng = Worksheets("CL").Cells(Rows.Count, "A").End(xlUp)
Set Rng = Worksheets("OH").Cells(Rows.Count, "A").End(xlUp)
Set Rng = Worksheets("TK").Cells(Rows.Count, "A").End(xlUp)
Set Rng = Rng.Resize(1, ListBox1.ColumnCount)

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Set Rng = Rng.Offset(1, 0)
For n = 1 To ListBox1.ColumnCount
Rng.Item(1, n).Value = ListBox1.List(i, n - 1)
Next n
End If
Next i

End Sub
[CODE]