Show Cell Values in User Form- VBA

[bamaisgreat]

I have a workbook with 26 worksheets that could have data in a certain range of cells. What I'm hoping to have is a subroutine in a user form that will look at each sheet in a range of cells V5:Y39 and show a list with no blank rows in a user form. I will have a Button on each sheet that will call the user form.

I also need it to show which sheet and row the data is on.

Any suggestions?

Thanks as always.

 

[DanteAmor]

Create a listbox control in your form

Put the following code in your userform

Private Sub UserForm_Activate()
    Dim sh As Worksheet, lr As Long, i as long
    
    With ListBox1
        .ColumnCount = 6
        For Each sh In Sheets
            lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
            For i = 5 To lr
                If WorksheetFunction.CountA(sh.Range("V" & i & ":Y" & i)) > 0 Then
                    .AddItem sh.Name
                    .List(.ListCount - 1, 1) = i
                    .List(.ListCount - 1, 2) = sh.Range("V" & i).Value
                    .List(.ListCount - 1, 3) = sh.Range("W" & i).Value
                    .List(.ListCount - 1, 4) = sh.Range("X" & i).Value
                    .List(.ListCount - 1, 5) = sh.Range("Y" & i).Value
                End If
            Next
        Next
    End With
End Sub

 

[bamaisgreat]

Thanks Dante.

How can we make this where it doesn't look any further than Row 39. And also I just need it to only look in Sht#1 thru Sht#26

 

[DanteAmor]

Try this

Private Sub UserForm_Activate()
    Dim sh As Worksheet, s As Long
    
    With ListBox1
        .ColumnCount = 6
        For s = 1 To 26
            Set sh = Sheets(s)
            For i = 5 To 39
                If WorksheetFunction.CountA(sh.Range("V" & i & ":Y" & i)) > 0 Then
                    .AddItem sh.Name
                    .List(.ListCount - 1, 1) = i
                    .List(.ListCount - 1, 2) = sh.Range("V" & i).Value
                    .List(.ListCount - 1, 3) = sh.Range("W" & i).Value
                    .List(.ListCount - 1, 4) = sh.Range("X" & i).Value
                    .List(.ListCount - 1, 5) = sh.Range("Y" & i).Value
                End If
            Next
        Next
    End With
End Sub

 

[bamaisgreat]

Could we have it show the sheet number also?

 

[DanteAmor]

Review

Private Sub UserForm_Activate()
    Dim sh As Worksheet, s As Long
    
    With ListBox1
        .ColumnCount = 7
        For s = 1 To Sheets.Count
            Set sh = 26
            For i = 5 To 39
                If WorksheetFunction.CountA(sh.Range("V" & i & ":Y" & i)) > 0 Then
                    .AddItem s
                    .List(.ListCount - 1, 1) = sh.Name
                    .List(.ListCount - 1, 2) = i
                    .List(.ListCount - 1, 3) = sh.Range("V" & i).Value
                    .List(.ListCount - 1, 4) = sh.Range("W" & i).Value
                    .List(.ListCount - 1, 5) = sh.Range("X" & i).Value
                    .List(.ListCount - 1, 6) = sh.Range("Y" & i).Value
                End If
            Next
        Next
    End With
End Sub

 

[bamaisgreat]

Dante, I really appreciate your help with this. I wanted to ask something else on this. Is it possible to have a header and a bottom border for each line? At the top of the values for "V" have Qty. at the top of "W" have Dwg#, X have Requestion and Y have Plate#.

 

[DanteAmor]

Dante, I really appreciate your help with this. I wanted to ask something else on this. Is it possible to have a header

To put a header, you need a sheet, Create the sheet at the end of your sheets and name it "Temp"

and a bottom border for each line

This is not possible

Try:

Private Sub UserForm_Activate()
    Dim sh As Worksheet, s As Long, j As Long
    Dim st As Worksheet
    
    Set st = Sheets("Temp")
    st.Cells.Clear
    st.Range("A1:G1").Value = Array("Index", "Name", "Row", "Qty.", "Dwg#", "Requestion", "Plate#")
    
    j = 2
    With ListBox1
        .ColumnCount = 7
        .ColumnHeads = True
        For s = 1 To 26
            Set sh = Sheets(s)
            If sh.Name <> st.Name Then
                For i = 5 To 39
                    If WorksheetFunction.CountA(sh.Range("V" & i & ":Y" & i)) > 0 Then
                           st.Cells(j, "A").Value = s
                           st.Cells(j, "B").Value = sh.Name
                           st.Cells(j, "C").Value = i
                           st.Cells(j, "D").Value = sh.Range("V" & i).Value
                           st.Cells(j, "E").Value = sh.Range("W" & i).Value
                           st.Cells(j, "F").Value = sh.Range("X" & i).Value
                           st.Cells(j, "G").Value = sh.Range("Y" & i).Value
                           j = j + 1
                    End If
                Next
            End If
        Next
        .RowSource = st.Name & "!A2:G" & st.Range("A" & Rows.Count).End(xlUp).Row
    End With
End Sub

 

[bamaisgreat]

That works great. I have tried doing a little research on widening the column widths in the List Box but I haven't found anything that's straight forward. Do you have any ideas? Is there code that can be added so I can control this?

 

[DanteAmor]

Gladly, check the following.

In the Property ColumnWidths of the listbox you can add the widths:

Eg.

100 pt;100 pt;100 pt;150 pt;200 pt;100 pt;200 pt

Or use this code:

Private Sub UserForm_Activate()
    Dim sh As Worksheet, s As Long, j As Long
    Dim st As Worksheet
    
    Set st = Sheets("Temp")
    st.Cells.Clear
    st.Range("A1:G1").Value = Array("Index", "Name", "Row", "Qty.", "Dwg#", "Requestion", "Plate#")
    
    j = 2
    With ListBox1
        .ColumnCount = 7
        .ColumnHeads = True
        For s = 1 To Sheets.Count '26
            Set sh = Sheets(s)
            If sh.Name <> st.Name Then
                For i = 5 To 39
                    If WorksheetFunction.CountA(sh.Range("V" & i & ":Y" & i)) > 0 Then
                           st.Cells(j, "A").Value = s
                           st.Cells(j, "B").Value = sh.Name
                           st.Cells(j, "C").Value = i
                           st.Cells(j, "D").Value = sh.Range("V" & i).Value
                           st.Cells(j, "E").Value = sh.Range("W" & i).Value
                           st.Cells(j, "F").Value = sh.Range("X" & i).Value
                           st.Cells(j, "G").Value = sh.Range("Y" & i).Value
                           j = j + 1
                    End If
                Next
            End If
        Next
        
        st.Columns("A:G").EntireColumn.AutoFit
        For i = 1 To Columns("G").Column
            ancho = ancho & Int(st.Cells(1, i).Width + 3) & ";"
        Next
        .ColumnWidths = ancho
        
        .RowSource = st.Name & "!A2:G" & st.Range("A" & Rows.Count).End(xlUp).Row


    End With
End Sub