If no invoice num,ber assigned to customer then populate listbox

[ipbr21054]

Hi,
Im looking for a simple code that will do the following.

Worksheet is called DATABASE
The code will look in column F ONLY for a cell which has value present.
Once a cell is found a userform listbox will be populated with that customers name which will be taken from column A
Once all cells have been checked the user can select a customer in the populated listbox & be taken to that customer in the worksheet.

Example.

Row 50 has no value in cell F
The name on Row 50 column A "TOM JONES" will then be placed into a userform listbox "USERFORM1 / LISTBOX1"
The code continues to look for empty cell values & do the same as above.
Once completed the listbox is compiled with all the available customers names ready for selection

 

[ipbr21054]

I think ive managed this ok but not sure how to add name to listbox where the RED X IS SHOWN

    Dim myRange As Range
    For Each myCell In Intersect(Range("F6", Range("F" & Rows.Count).End(xlUp)), myRange)
    If IsEmpty(myCell) Then
      With Range("A" & myCell.Row)
      
       XXXXXXXXXX

  Next myCell

 

[ipbr21054]

Unable to work this out so will await help.
Thanks

    Dim myRange As Range
        For Each myCell In Intersect(Range("F6", Range("F" & Rows.Count).End(xlUp)), myRange) 
    If IsEmpty(myCell) Then
      With Range("A" & myCell.Row).ListBox1.AddItem
   Exit Sub
   End With
   End If
   Next myCell

 

[NoSparks]

give this a try

Private Sub CommandButton1_Click()

Dim rng As Range, cel As Range
    
For Each cel In Range("F6", Range("F" & Rows.Count).End(xlUp))
    If IsEmpty(cel) Then
        With Me.ListBox1
            .AddItem
            ' first column of item just added
            .List(.ListCount - 1, 0) = cel.Offset(, -5).Value
        End With
    End If
Next cel

End Sub

and then to be taken to whichever gets selected

Private Sub ListBox1_Click()

Dim i As Long
Dim Customer As String
Dim fndCustomer As Range

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        Customer = ListBox1.List(ListBox1.ListIndex, 0)
        Exit For
    End If
Next i

Set fndCustomer = Sheets("DATABASE").Columns("A").Find(What:=Customer, LookIn:=xlValues, LookAt:=xlWhole)
If Not fndCustomer Is Nothing Then fndCustomer.Select

End Sub

 

[ipbr21054]

Hi,
So my command button on worksheet will open the userform & the command button on the userform will populate the list correct ?

If so can this be done.
Press command button on worksheet & the userform opens & list is then just populated.

Would the code need to be put in userform initialise code ?

Thanks.

 

[NoSparks]

This works for me.

button on sheet code

Sub Button1_Click()
    UserForm1.Show
End Sub

userform code

Private Sub UserForm_Initialize()
    Dim rng As Range, cel As Range
For Each cel In Range("F6", Range("F" & Rows.Count).End(xlUp))
    If IsEmpty(cel) Then
        With Me.ListBox1
            .AddItem
            ' first column of item just added
            .List(.ListCount - 1, 0) = cel.Offset(, -5).Value
        End With
    End If
Next cel
End Sub

Private Sub ListBox1_Click()
    Dim i As Long
    Dim Customer As String
    Dim fndCustomer As Range
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        Customer = ListBox1.List(ListBox1.ListIndex, 0)
        Exit For
    End If
Next i

Set fndCustomer = Sheets("DATABASE").Columns("A").Find(What:=Customer, LookIn:=xlValues, LookAt:=xlWhole)
If Not fndCustomer Is Nothing Then fndCustomer.Select
Unload Me
End Sub

 

[ipbr21054]

Thanks,also worked for me.

One question please,

If no blank cells are found then i need to show a Msgbox advising user.
Currently when i click the command button on the worksheet nothiong happens as nothing to add to listbox.

When i tried to add the line of code to the Initialize event i either sawe a message saying No If or i got into a loop where clicking ok on the msgbox pop up did nothing to close it & had to do ALT CONTROL DELETE & start again.

Thanks

 

[abdelfattah]

@NoSparks
I wanted posting like the same solution as you did it, but I hesitated to do and just watching some body to pot suggestion until you did it, because shows problem and spend much time to fix it and I failed !
I think the same problem is existed in your code . if you try by making last cell is empty for column F or more than one empty cell then will not show in listbox as in CLIENT9,10 for column A should populate them in listbox .

 

[NoSparks]

@abdelfattah
You're right, should determine the last row from the customer column.

@ipbr21054
try this for the button on the sheet

Sub Button1_Click()

    Dim lr As Long
    Dim rng As Range

lr = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("F6:F" & lr)
If Application.WorksheetFunction.CountBlank(rng) = 0 Then
    MsgBox "No Invoices Missing"
Else
    UserForm1.Show
End If

End Sub

and for the userform initialization

Private Sub UserForm_Initialize()
    Dim lr As Long
    Dim rng As Range, cel As Range
    
lr = Range("A" & Rows.Count).End(xlUp).Row
For Each cel In Range("F6:F" & lr)
    If IsEmpty(cel) Then
        With Me.ListBox1
            .AddItem
            ' first column of item just added
            .List(.ListCount - 1, 0) = cel.Offset(, -5).Value
        End With
    End If
Next cel
End Sub

hope that rectifies everything

 

[ipbr21054]

Thanks
I will check once home.

Would you mind looking at my other posted which is also you code but I was wanting to populate another two columns but had many issues.

Maybe your new advice might fix my issues but yet to try.