Hi,
I have been experiencing this issue when I try to populate a worksheet with the data from a multicolumn listbox on a userform.
I have references which list index is to go to which cell on the worksheet, however if there is no entry in this list index it is giving me this error.
As the listbox could have 1 line or up to 10 lines populated, I think I need to put an error handling on so that if the entry in listbox index is "" then resume next, or something like that. However everything I have tried doesn't work and I keep getting this error.
Here is the code I have to populate the worksheet from the listbox. There are 7 columns and 10 rows.
Can anyone help me on how to handle this error? I am due to submit this assignment tonight and just found this error a few hours ago and not having any luck finding a solution that works.
Any help is greatly appreciated!
I have been experiencing this issue when I try to populate a worksheet with the data from a multicolumn listbox on a userform.
I have references which list index is to go to which cell on the worksheet, however if there is no entry in this list index it is giving me this error.
As the listbox could have 1 line or up to 10 lines populated, I think I need to put an error handling on so that if the entry in listbox index is "" then resume next, or something like that. However everything I have tried doesn't work and I keep getting this error.
Here is the code I have to populate the worksheet from the listbox. There are 7 columns and 10 rows.
Can anyone help me on how to handle this error? I am due to submit this assignment tonight and just found this error a few hours ago and not having any luck finding a solution that works.
Any help is greatly appreciated!
Code:
Private Sub cbPrint_click()
Call Error_Handling_VBA_On_Error_Resume_Next
Dim ws As Worksheet
Set ws = Worksheets("Invoice Copy")
With ws
.Range("B11") = CusName.Value
.Range("B12") = CustomerAddress.Value
.Range("B30") = CustomerMsg.Value
.Range("I11") = InvoiceNo.Value
.Range("I12") = D8.Value
.Range("I13") = Terms.Value
.Range("I14") = DueD8.Value
.Range("I29") = Subttl.Value
.Range("I31") = Taxamt.Value
.Range("I32") = Ttlamt.Value
.Range("B19") = Me.ListBox1.Column(0, 0)
.Range("C19") = Me.ListBox1.Column(1, 0)
.Range("H19") = Me.ListBox1.Column(2, 0)
.Range("I19") = Me.ListBox1.Column(4, 0)
.Range("B20") = Me.ListBox1.Column(0, 1)
.Range("C20") = Me.ListBox1.Column(1, 1)
.Range("H20") = Me.ListBox1.Column(2, 1)
.Range("I20") = Me.ListBox1.Column(4, 1)
.Range("B21") = Me.ListBox1.Column(0, 2)
.Range("C21") = Me.ListBox1.Column(1, 2)
.Range("H21") = Me.ListBox1.Column(2, 2)
.Range("I21") = Me.ListBox1.Column(4, 2)
.Range("B22") = Me.ListBox1.Column(0, 3)
.Range("C22") = Me.ListBox1.Column(1, 3)
.Range("H22") = Me.ListBox1.Column(2, 3)
.Range("I22") = Me.ListBox1.Column(4, 4)
.Range("B23") = Me.ListBox1.Column(0, 4)
.Range("C23") = Me.ListBox1.Column(1, 4)
.Range("H23") = Me.ListBox1.Column(2, 4)
.Range("I23") = Me.ListBox1.Column(4, 4)
.Range("B24") = Me.ListBox1.Column(0, 5)
.Range("C24") = Me.ListBox1.Column(1, 5)
.Range("H24") = Me.ListBox1.Column(2, 5)
.Range("I24") = Me.ListBox1.Column(4, 5)
.Range("B25") = Me.ListBox1.Column(0, 6)
.Range("C25") = Me.ListBox1.Column(1, 6)
.Range("H25") = Me.ListBox1.Column(2, 6)
.Range("I25") = Me.ListBox1.Column(4, 6)
.Range("B26") = Me.ListBox1.Column(0, 7)
.Range("C26") = Me.ListBox1.Column(1, 7)
.Range("H26") = Me.ListBox1.Column(2, 7)
.Range("I26") = Me.ListBox1.Column(4, 7)
.Range("B27") = Me.ListBox1.Column(0, 8)
.Range("C27") = Me.ListBox1.Column(1, 8)
.Range("H27") = Me.ListBox1.Column(2, 8)
.Range("I27") = Me.ListBox1.Column(4, 8)
.Range("B28") = Me.ListBox1.Column(0, 9)
.Range("C28") = Me.ListBox1.Column(1, 9)
.Range("H28") = Me.ListBox1.Column(2, 9)
.Range("I28") = Me.ListBox1.Column(4, 9)
End With
Application.Visible = True
Sheets("Invoice Copy").Activate
Me.Hide
Worksheets("Invoice Copy").PrintPreview
Application.Visible = False
Me.Show
End Sub