vBA On click

[xtrato]

Hi all Happy new Years to everyone.

So i have this situation , i was able to grab a VBA Code from another thread and combine it. But i need to expand on it, the basic principle is.

When someone clicks on the employee ID ( preferably the HOURS) a pop up with the "RAW INFORMATION" shows... so far i have this code, which i combined.


The following code belongs to the attached file.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim stAddress As String, x As Long, lRow As Long
    If Not Intersect(Target, Range("B2:B10")) Is Nothing Then
        lRow = Sheet2.Range("B:B").Find(what:=Target).Row
        For x = 0 To 6
            stAddress = stAddress & Sheet2.Range("Data").Cells(lRow, x + 1) & Chr(13)
        Next x
       
                With UserForm1
    ' Increase the height by 20 points
    .Height = .Height + 70
   .Width = .Width + 120
End With
       
        With UserForm1
            .Show
            .Left = 180
            .Caption = "Clocks " & Target.Value
            .TextBox1 = stAddress
            .Top = ActiveCell.Top + ActiveCell.Height + .Height
            .TextBox1.Height = .TextBox1.Height + 70 ' Increase height by 100 points
            .TextBox1.Width = .TextBox1.Width + 100 ' Increase width by 100 points

        End With
       

        stAddress = ""
        lRow = 3
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("C2:C100")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Range("B2") = Target.Value
End If
End Sub

Addresses.xlsm
	A	B	C	D	E
1		Name
2	Dolly	Dolly
3		Joe
4		Roxy
5
6		Joe
7
8		Dolly
9		Joe
10
11			Dolly
12
13
14
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2:B9	List	=OFFSET(Data,1,0,ROWS(Data),1)
C2:C3	List	=OFFSET(Data,1,0,ROWS(Data),1)
C11	List	=OFFSET(Data,1,0,ROWS(Data),1)

DATA SHEET:

Addresses.xlsm
	A	B	C	D	E	F	G	H
1		Name	Address 1	Address 2	Post Code	Post Code	Address 1
2		Joe	6 Some street	Leeds	LS2 1AB	xx	6 Some street
3		Dolly	8 Somewhere else street	A Town	LS2 1AB	xx	TEST
4		Roxy	10 Another Road	Another Town	LS2 1AB	x	TEST
5		Fred	6 Some street	Town Fred	LS2 1AB	xxx	TEST
6		Dolly	8 Somewhere else street	A Town	LS2 1AB	xx	TEST
7
Data

So when i do infact click on DOLLY: i get the followin pop up successfully.

and thats great; now , back to my original problem... So the pop up is great, works as intended, now i want to apply that to my sheet , again with the following result:

when someone click son the ID COLUMN A : the pop up shows. ( now based on the vBA above - it needs to modify a cell , so that cell can be hidden) ,

now the popup i want it to display the data that is shown below on a different RAW DATA SHEET:
.

12-23.xlsx
	BA	BB	BC	BD
1	Fullname	break	halfday	CONCA
2	Jose Montrond	30 min	0	Mon-23||10.13
3	Jose Montrond	30 min	0	Tue-24|1636|9.95
4	Jose Montrond	30 min	0	Fri-27|1636|9.17
5	Jose Montrond	30 min	0	Sat-28|1636|9.82
6	Jose Montrond	30 min	0	Sun-29|1636|6.87
7	Shreya Thakur	30 min	0	Tue-24|1636|9.23
8	Shreya Thakur	30 min	0	Fri-27|2414|8.65
9	Shreya Thakur	30 min	0	Sun-29|2414|0.38
10	Shreya Thakur	30 min	0	Sun-29|2414|6.42
11	Shreya Thakur	30 min	0	Mon-23|4236B|9.95
12	Shreya Thakur	30 min	0	Thu-26|702B|9.37
13	Yitzchak Marashli	0	0	Mon-23|702B|9.58
14	Yitzchak Marashli	0	0	Tue-24|1930|8.75
15	Yitzchak Marashli	0	0	Thu-26|3851|7.97
16	Yitzchak Marashli	0	0	Fri-27|3851|9.12
clock

Cell Formulas
Range	Formula
BB2:BB16	BB2	=XLOOKUP(D2,PayrolHRS!$B$1:$B$1111,PayrolHRS!$C$1:$C$1111,0)
BC2:BC16	BC2	=IF(AND(V2=0,AC2<5),0.5,0)
BD2:BD16	BD2	=CONCATENATE(,TEXT(G2,"DDD-DD"),"|",REPLACE(A1, 1, 4, ""),"|",H2," ")
BA7:BA16	BA7	=CONCATENATE(PROPER(E7)," ",PROPER(F7))

Now - this data is orgnized in columns - not in rows as the orignal code ; so i want to instead, display all the in BD That pertains to the Employee in BA - on this example - there would be 5 LINES that should show on the popup

so below is a screenshot of what "ULTIMATELLY" i would like when they click a name :

Any help would be Greatly APPRECIATED!!

 

[Logit]

You'll need to post your workbook to a download site then post the link here.

 

[xtrato]

ok , here are both files :

Easyupload.io - Upload Files and Share Them Easily

Uplaod files easily and share them with your friends and colleagues. File upload solution for everyone. Upload big files and get a link to share effortlesly.

easyupload.io

ive removed excess data

So from the PRL File , id want all data matched in column A for user ID - to column N all times , as shown in the picture above, Using the VBA from the addresses file,

 

[Logit]

Hoping I correctly understood your stated goal. Here is a macro that displays the data in a LABEL located on UserForm1.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim TargetRow As Long
    Dim CellValue As String ' Ensure that CellValue is treated as a String
    Dim ws As Worksheet
    Dim MultiLineText As String
    Dim Items() As String
    Dim Item As Variant
    Dim i As Integer
    
    ' Set the correct worksheet reference
    Set ws = ThisWorkbook.Sheets("Main")
    
    ' Check if the selected cell is in column A (adjust as needed)
    If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
        ' Ensure only a single cell is selected
        If Target.Cells.Count = 1 Then
            ' Get the row number of the selected cell
            TargetRow = Target.Row
            
            ' Get the value from column H (ensuring it's treated as text)
            CellValue = CStr(ws.Cells(TargetRow, "H").Value)
            
            ' Trim the value to remove extra spaces
            CellValue = Trim(CellValue)
            
            ' Initialize an empty string to store the multi-line text
            MultiLineText = ""
            
            ' Check if there is any value in column H
            If Len(CellValue) > 0 Then
                ' Split the data by spaces into an array
                Items = Split(CellValue, " ")
                
                ' Loop through each item in the array and create a formatted line
                For i = LBound(Items) To UBound(Items)
                    ' Add each item followed by a line break
                    MultiLineText = MultiLineText & Items(i) & vbCrLf
                Next i
                
              
                ' Initialize and show the UserForm
                With UserForm1
                    ' Ensure WordWrap is enabled to support multiple lines
                    .Label1.WordWrap = True ' Enable WordWrap for the label to display multi-line text
                    
                    ' Set the Label1 AutoSize to False to control size manually
                    .Label1.AutoSize = False
                    .Label1.Width = 200 ' Set the label width to a fixed value (adjust as needed)
                    .Label1.Caption = MultiLineText ' Set the label text to the formatted multi-line content
                    
                    ' Set the label size based on the content length
                    .Label1.Height = (Len(MultiLineText) / 10) * 15 ' Adjust this multiplier as needed to ensure enough space
                    
                    
                    .Show ' Show the UserForm
                End With
            Else
                MsgBox "No data found in column H of Main for the selected row.", vbInformation
            End If
        End If
    End If
End Sub

The UserForm properties were set as: ShowModal = False

The Label1 properties were set as: WordWrap = True ..... AutoSize = False

Review the macro and edit the column designations to match your needs there.
This macro was placed in the MAIN worksheet module located in the workbook PRL.xlsb

You can download the revised workbook here : Internxt Drive – Private & Secure Cloud Storage

 

[xtrato]

alright thanks , i think we are on to something, So in Column H - has its own Formula that (Xlookup) that looks up the Total hours of the employee so that one we dont need it to be modifed .

what i want is when you click on EMP ID (COL A from "main" Sheet) is to show the RAW Data from "Data" sheet in column "N" .. so for exp jose would have 5 lines of data displayed

as such:

so to explain more-less ; like doing an xlookup and show all results in the Userform Popup of that selected cell:

=XLOOKUP(A5,data!A:A,data!N:N)

 

[xtrato]

so , of course, we learn as we go .. its like doing a filter then:

=FILTER(data!N1:N12,data!B1:B12=main!A5,"")

but place this result inside that text pop up box when clicked on the cell A5