Help with properly executing a form's action

[rharri1972]

I am not really sure this title explains very well as to what I need.

I have a user form with a listbox1 that is populated with several different customers. I also have a listbox 2 at the bottom of the form that will display the order history for whichever customer I click on in listbox1. This happens "on click".

For each individual customer I have a worksheet and the worksheet is named after the customers ID number.

As I enter an order for a customer, I click a command button on the order form... the data is identified by the customers ID number and then the data is sent to the corresponding worksheet.

As it is now, it works perfectly but ONLY IF there is data in the worksheet. If I do not have any data (or orders) for the customer, I get an error message.

It is completely fine for when I click on a customer, if there are no orders, for listbox2 to just show blank instead of getting an error message.

If I get an order and it will be the first order for this customer and I am getting this error then I will not be able to click the "INSERT" button to enter this order. The "INSERT" button is on the same user form under listbox2.

Here is the code i have now:

Private Sub ListBox1_Click()
'highlighting customer to see quote/order history in listbox2

Application.EnableEvents = False
Sheets(ListBox1.List(ListBox1.ListIndex)).Activate
Application.EnableEvents = True

'list box2 show values
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row '<----- pulling data from active sheet
With ListBox2
.ColumnCount = 3
.ColumnWidths = "70;80;80" '<----- Change to desired column widths
.List = ActiveSheet.Range("A1").CurrentRegion.Value

End With

End Sub

Any help will be greatly appreciated!!

 

[dmt32]

Hi,
untested but see if this update to your code resolves the issue

Private Sub ListBox1_Click()
    'highlighting customer to see quote/order history in listbox2
    Dim ws          As Worksheet
    
    Set ws = ThisWorkbook.Worksheets(Me.ListBox1.List(Me.ListBox1.ListIndex))
    
    If Application.CountA(ws.UsedRange) > 0 Then
    
        With Me.ListBox2
            .ColumnCount = 3
            .ColumnWidths = "70;80;80"
            .List = ws.Range("A1").CurrentRegion.Value
        End With
    End If
    
End Sub

Dave

 

[rharri1972]

Hi,
untested but see if this update to your code resolves the issue

Private Sub ListBox1_Click()
    'highlighting customer to see quote/order history in listbox2
    Dim ws          As Worksheet
   
    Set ws = ThisWorkbook.Worksheets(Me.ListBox1.List(Me.ListBox1.ListIndex))
   
    If Application.CountA(ws.UsedRange) > 0 Then
   
        With Me.ListBox2
            .ColumnCount = 3
            .ColumnWidths = "70;80;80"
            .List = ws.Range("A1").CurrentRegion.Value
        End With
    End If
   
End Sub

Dave

Hey Dave thank you for your help. However, the information in List box2 does not change. If i click on a customer that already has "test" data filled in its sheet then listbox2 shows correctly. If i click on a customer that has no data in its sheet, the other customers information stays in the listbox. Can we change this up to show the actual information for each customer? If the customers sheet is blank the listbox should be blank. I hope this make sense.

Thanks!

 

[dmt32]

Hi,
try this update & see if resolves

Private Sub ListBox1_Click()
    'highlighting customer to see quote/order history in listbox2
    Dim ws          As Worksheet
    
    Set ws = ThisWorkbook.Worksheets(Me.ListBox1.List(Me.ListBox1.ListIndex))
    
    With Me.ListBox2
        .Clear
        
        If Application.CountA(ws.UsedRange) > 0 Then
            .ColumnCount = 3
            .ColumnWidths = "70;80;80"
            .List = ws.Range("A1").CurrentRegion.Value
        End If
        
    End With
    
End Sub

Dave

 

[rharri1972]

Hi,
try this update & see if resolves

Private Sub ListBox1_Click()
    'highlighting customer to see quote/order history in listbox2
    Dim ws          As Worksheet
   
    Set ws = ThisWorkbook.Worksheets(Me.ListBox1.List(Me.ListBox1.ListIndex))
   
    With Me.ListBox2
        .Clear
       
        If Application.CountA(ws.UsedRange) > 0 Then
            .ColumnCount = 3
            .ColumnWidths = "70;80;80"
            .List = ws.Range("A1").CurrentRegion.Value
        End If
       
    End With
   
End Sub

Dave

Dave...it works great! Thank you! I ALMOST stumbled upon this except i used with me.listbox2.value.clear.... difference between "greenhorn" and experience! Thanks for your help!!

 

[dmt32]

Dave...it works great!

welcome - my 1st post just an oversight - glad all resolved & appreciate your feedback
Good luck with your project

Dave

 

[rharri1972]

welcome - my 1st post just an oversight - glad all resolved & appreciate your feedback
Good luck with your project

Dave

thanks... I am very close....well close to completing...I dont know if it is against policy but if not...do you mind if i call on you for help when i need it?

 

[dmt32]

thanks... I am very close....well close to completing...I dont know if it is against policy but if not...do you mind if i call on you for help when i need it?

we are all volunteers - when you need assistance, just post the question

- do though, be clear what the issue is & help needed to resolve, post the code (between code tags)

- if needed, share copy of your worksheet with (dummy data) using MrExcel Addin XL2BB - Excel Range to BBCode

- for more complex projects, share a copy of the workbook by placing it in a file sharing site like DropBox & provide a link to it.

- Please remember if you also post the same question on another excel help site (cross post), to let this site know by providing a link to it.

you will find that there are plenty here to assist you.

Dave