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.

 

[bamaisgreat]

Almost Perfect ! For some reason it is adding an index column. Can we somehow not show it?

 

[DanteAmor]

Almost Perfect ! For some reason it is adding an index column. Can we somehow not show it?

You requested the sheet number in post #5

Could we have it show the sheet number also?

 

[bamaisgreat]

Yes you are right. It shows sheet in column B. For some reason the Index column started showing up after I added the code for setting the Column Widths.

 

[DanteAmor]

That's right, I made the correction, now you have sheet number, sheet name and row number.

 

[bamaisgreat]

Oh ok. I think I confused myself on this one because my sht number value is the same as my sht name. I think I can figure out how to modify the code to just show one of them. Thanks again!

 

[bamaisgreat]

Dante, is there a setting in the Userform Properties that will let me make changes on a sheet while the userform is showing?

 

[My Aswer Is This]

You need to open your UserForm like this:
UserForm1.Show Modeless

Modeless will allow you to work on sheet with Userform Visible

Modify UserForm name as needed.

 

[bamaisgreat]

When I changed it to modeless the form comes up blank.

 

[My Aswer Is This]

Not sure why that would happen.
What is the name of your Userform?

 

[bamaisgreat]

Sub PrintUserformShow24()
UserForm10.Show Modeless
End Sub

Private Sub UserForm()
    Dim sh As Worksheet, s As Long, j As Long
    Dim st As Worksheet
    
    Set st = Sheets("Temp")
    st.Cells.Clear
    st.Range("A1:F1").Value = Array("Sht#", "Row", "Qty.", "Material", "Requestion", "Notes / Comments")
    
    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("U" & i & ":Y" & i)) > 0 Then
                           st.Cells(j, "A").Value = s
                           'st.Cells(j, "B").Value = sh.Name
                           st.Cells(j, "B").Value = i
                           st.Cells(j, "C").Value = sh.Range("U" & i).Value
                           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("Y" & i).Value
                           j = j + 1
                    End If
                Next
            End If
        Next
        
        st.Columns("A:F").EntireColumn.AutoFit
        For i = 1 To Columns("F").Column
            ancho = ancho & Int(st.Cells(1, i).Width + 3) & ";"
        Next
        .ColumnWidths = ancho
        
        .RowSource = st.Name & "!A2:F" & st.Range("A" & Rows.Count).End(xlUp).Row
    End With
End Sub