VBA for find values in range in multiple worksheets, copy values to UserForm

[Shiftyshell]

Hi guys,

I need help with following VBA commands (see also file in attachment):

On the "Summary" sheet, I have a "Status of PE´s" button that automatically opens UserForm1. I would need VBA code that would find me in all Excel worksheets (existing and newly created), find the "PE number" - D3 in individual worksheets, find the status of PE - D10 in individual worksheets and copy the data to UserForm1 as a link. The link should work after clicking.


Is it possible with VBA?

I will be extremely grateful for any help, I have been worried about it for a long time.

Thank all for help and support in advance!

[Dave]

Hi Shiftyshell. I'm not quite certain I understand your needs especially the linking part? If you replace your textbox3 and textbox4 on your userform with Listbox1 and Listbox2, the following code will populate the listboxes with your D3 and D10 values from all sheets (except for the summary, data and temporary sheets). If U want to select the D3 value in the listbox and then goto that sheet address (link?) it wouldn't be that hard to do. HTH. Dave

Private Sub CommandButton1_Click()Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
If Sht.Name <> "Temporary" And Sht.Name <> "Data" And _
Sht.Name <> "Summary" Then
UserForm1.ListBox1.AddItem Sht.Range("D" & 3)
UserForm1.ListBox2.AddItem Sht.Range("D" & 10)
End If
Next Sht
UserForm1.Show
End Sub

[Shiftyshell]

Hi Dave,

thank you for your help. It works great.

I would need the PE numbers to be displayed in the left ListBox as links to individual WorkSheet in Excel, or so that after clicking on the PE number in the UserForm, the user can get to the given WorkSheet. Do you think that is possible? In attachment I sending also picture.

Thank you in advance for your support and help.

[Shiftyshell]

I already solved it with simply button in UserForm.

Thanks Dave for your help.

[Dave]

You are welcome. Thanks for posting your outcome. Be safe. Dave

[snb]

You'd better make proper use of VBA.

1. Never use Additem to populate a ListBox or Combobox
2. Use the propertuy 'List' to populate a Combobox/ListBox
3. Use the Initialize Event in a Userform to populate Listboxes/comboboxes

In this Case:


Private Sub UserForm_Initialize()
For Each it In ThisWorkbook.Sheets
If Left(it.Name, 2) = "PE" Then
c00 = c00 & "_" & it.Cells(3, 4)
c01 = c01 & "_" & it.Cells(10, 4)
End If
Next

ListBox1.List = Split(Mid(c00, 2), "_")
ListBox2.List = Split(Mid(c01, 2), "_")
End Sub
See: https://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html