ComboBox to automatically fill Textboxes from range.

[Daroh]

Hi, I have made a userform. I am trying to populate certain textboxes based on a drop down combo box which is filled from a dynamic range. This range can range from 1 person to 20/30. Also, I want to be able to change the data in the textboxes if required.

Private Sub UserForm_Initialize()
    Set Rng = Sheet6.Range("A2", Sheet6.Range("A" & Rows.Count).End(xlUp))
    Me.ComboBox1.RowSource = Rng.Address
End Sub

This is my userform

Any suggestions how I can do this or the most efficent way to do this.

This is my work list.

CPAP Database V1.5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2	MRN	Date Preformed	First_Name	Second_Name	DOB	Gender	Phone	AHI (ORG)	Arousal Index (ORG)	PLM index (ORG)	ESS (ORG)	Date Range (New)	AHI (New)	%Data USED (NEW)	% Days Used 4>hrs (NEW)	Average hrs of use (NEW)	ESS (NEW)	Comment	GP Address	GP Name	Commerical Driver:	Medical Card:
3	123	29/01/2025	John	Peter	1/1/3036	f	123456789	66	55	55	12	29/01/2024	15	55	55	10	14	Good use of machibne	123 The adres s of GP	Dr Name.	No	Yes
Wrk_List

 

[Daroh]

This is what works for, any suggestions to make it better please let me know, still learning VBA.

Private Sub CommandButton1_Click()

Dim ws As Worksheet, ws1 As Worksheet, i As Long
Set ws = ThisWorkbook.Sheets("Wrk_List")
Set ws1 = ThisWorkbook.Sheets("Patient_Data")

If ComboBox1 = "" Then
MsgBox "Please enter MRN from Work List"
        Exit Sub
    End If
' Page one userForm
For i = 1 To ws.UsedRange.Rows.Count
If ComboBox1.Text = ws.Cells(i, 1).Value Then
TextBox1 = ws.Cells(i, 3) ' Patient Name
TextBox2 = ws.Cells(i, 5) ' DOB
TextBox3 = ws.Cells(i, 6)  ' Gender
TextBox17 = ws.Cells(i, 20) ' GP_Name
TextBox18 = ws.Cells(i, 19)  ' Gp_Address
'Page Two userForm - CPAP Details
For p = 1 To ws1.UsedRange.Rows.Count
If ComboBox1.Text = ws.Cells(i, 1).Value Then
TextBox4 = ws1.Cells(p, 8) ' Referral
TextBox5 = ws1.Cells(p, 10) ' Weight
TextBox6 = ws1.Cells(p, 9)  ' Height
TextBox7 = ws1.Cells(p, 11) ' BMI
    End If
Next p
TextBox8 = ws.Cells(i, 11)  ' Ep_Score
TextBox9 = ws.Cells(i, 6)  ' Stop Bang
TextBox10 = ws.Cells(i, 8) ' AHI Old
TextBox11 = ws.Cells(i, 10)  ' PLM Index Old
TextBox15 = ws.Cells(i, 9) ' Arousal Index Old
TextBox16 = ws.Cells(i, 19)  ' Date Of Test
'Page 3 of UserForm - CPAP REVIEW ON THE DAY
TextBox26 = ws.Cells(i, 12) ' Date Range
TextBox25 = ws.Cells(i, 13)  ' AHI NEW
TextBox24 = ws.Cells(i, 14) ' %DAT USED NEW
TextBox23 = ws.Cells(i, 15) '%DAYS USED >4HRS
TextBox27 = ws.Cells(i, 16)  ' AVE Hrs of USE NEW
TextBox28 = ws.Cells(i, 17)  ' EP SCORE NEW
TextBox21 = ws.Cells(i, 18)     ' Comment
            Exit Sub
        Else
    End If
Next i