list cells in list box 1

[Daya100]

I have a userform in Excel that loads on the workbook before save event. I want it to list (in a list box) all of the cells that I have specified that are null or empty. Thanks to Skip I know how to evaluate if a cell is empty. But, there are a good 100 cells that I need to look at - and if one is empty then I want it to be added to the list box so the user knows they have to fill it out. I know how to use: UserForm1.ListBox1.AddItem but I don't know the smart way of going about it. I.E. I would do something like: If Cellx is null then add it to the list - for each of the 100 cells. Does that make sense? Help?!?
THANK YOU!!

 

[SkipVought]

Daya,
You can loop thru all the cells in you table looking for Null values...

'assuming that cells(1,1) is in your table...
    For Each cell In Cells(1, 1).CurrentRegion
        If IsEmpty(cell.Value) Then
            Answer = InputBox("Please enter value for " & sDescription, vbOKCancel)
            If Answer = vbCancel Then
                'decide what you want to do if the user bails out
                Exit Sub
            End If
            cell.Value = Answer
        End If
    Next

How does that do? Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

Hey Skip,

I'm confused (sorry - I'm new at this). So...am I creating a table with all of the cells that I need to evaluate? And how would I do that? Thanks!!!

 

[SkipVought]

From what you said, you want the user to enter data in empty cells in your table or in some predefined range of cells. This code loops thru each cell in the range and if its empty, asks the user to input a value. You might want more validation than that.

If you insist on a ListBox then the AddItem would be the field Name and the resultant entry value on your form would have to be sent to the field.

I guess I might be confused, too.. Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

OK - I think I know what to do now - just not how. I am trying to evaluate various cells on various different sheets in the workbook (to see if the user filled them out). So, I think I need to create a collection of all of those cells. Then I can use the For...Each...Next statement to see if they are null and if they are then add it to the list in the list box. Right? Do you know how to do that? And if I add it to the list box then I want it to be text like "Date cell" not F10 or whatever. Do I make any sense? BTW - I really appreciate your help, Skip! I'll send more stars your way...

 

[SkipVought]

Tell me how your data sheets are designed. How will you identify a particular cell in your listbox so that it can be accessed again to fill it. Give me an example. Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

I don't think I understand your question. I have no idea how to identify the cell in the listbox. I wanted to put something like "First Name" in the list box which is actually cell B5 - but I would put the name so they know what they haven't filled out on the form yet.

 

[SkipVought]

What I am driving at is that for each Worksheet, you have Names associated with Cells or Rows of data or Columns of data. It takes different logic to handle single cell names vs Names that reference multiple cells. I assume that your application is using a single cell per name?

If so, what you need to do is assign a name to each cell that corresponds to the name you might see adjacent to the cell. Do you follow? Then we can do this...

1. Select the cell containing the Field Name and the adjacent cell where the data will be stored.
2. Slect Menu Item Insert/Name/Create and check to see that the CheckBox reflects the relative position of the Field Name to the data.
3. Then we can loop thru all the Names as follows...
You have to set up a userform with a listbox, a textbox and a button.
The list box lists all the enpty fields. When you make a selection from the listbox, the cell is selected and the focus changes to the textbox.
The textbox is where you enter the required data. Hit the button to put the data in the selected cell.
Then the item is removed for the listbox.

Dim iListIndex As Integer
Private Sub CommandButton1_Click()
    With UserForm1.TextBox1
        If Not IsEmpty(.Text) Then
            ActiveCell.Value = .Text
        End If
    End With
    UserForm1.ListBox1.RemoveItem iListIndex
End Sub

Private Sub ListBox1_Click()
    With UserForm1.ListBox1
        Application.Goto Reference:=.Text
        UserForm1.TextBox1.Text = ""
        iListIndex = .ListIndex
    End With
    UserForm1.TextBox1.SetFocus
End Sub

Private Sub UserForm_Activate()
    For Each Name1 In ActiveWorkbook.Names
        Application.Goto Reference:=Name1.Name
        If IsEmpty(Selection.Value) Then
            UserForm1.ListBox1.AddItem Name1.Name
        End If
    Next

End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

OK - I've put the code in but when the userform activates I get an error that says Name1 is an invalid reference. What did I not do?
Thanks!
Daya

 

[Daya100]

More specific - how do I refer to the named range?
I have Range("NameofRange&quot and it doesn't work.

 

[SkipVought]

You have set Option Explicit, which is good. It means that you have to Bim every variable. So.... Dim Name1 -- put it up by Dim iListIndex As Integer

Nothing in the code calls YOU to refer to any named range. It just takes the ranges that you have named and looks to see if they are empty. I am not sure what you are asking. Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

I feel like the biggest idiot. I still get the refernce error. I have:
Private Sub UserForm_Activate()

For Each Name1 In ActiveWorkbook.Names
Application.Goto Reference:=Name1.Name
If IsEmpty(Selection.Value) Then
UserForm1.ListBox1.AddItem Name1.Name
End If
Next

End Sub

I tried putting Dim Name1 everywhere and it still gives me the error. help!

 

[SkipVought]

When you go to the sheet that has the range names, what names do you have in the Name Box dropdown? Skip,
metzgsk@voughtaircraft.com