Populating userforms

Foxy

New Member
Joined
Nov 24, 2005
Messages
1
I have set up a spreadsheet that is populated by 7 Userforms. The spreadsheet has 1 column containing surnames.
One of my userforms aims is to highlight a particular row and bring up those details in another userform.
The problem I'm having is when there are sevral records with the same surname - the userform is populated with the first record on the spreadsheet that it finds with that surname, not the record i have selected.

Does anyone have any ideas what I need to look at in my coding?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Welcome to the Board.

What determines which record is selected? A selection from a ComboBox or ListBox? And what code are you using to find the relevant record?

If your ComboBox/ListBox has a RowSource you can use eg:

ComboBox1.ListIndex + 1

to get the row number in RowSource of the selected item.
 
Upvote 0
The record is selected from a ListBox, and Foxy's used 'UserForm3.ListBox1.ListIndex + 2' to select.

It's baffling because the other 3 UserForms that are populated following selection of a record from UserForm3 work perfectly. Would it help if I attached the whole piece of coding? It is quite lengthy...

pp Foxy (who has gone home)
 
Upvote 0
Okay. The background is: user highlights relevant record from ListBox on UserForm3 & clicks command button to amend details. This takes the user to UserForm5, which is populated with the correct details. There are 2 command buttons on UserForm5 - it is when the user clicks command button 2 (see code) that the error occurs. This command button saves any changes to the details on UserForm5, and should then bring up UserForm6 pre-populated with the surname & forename. It is here that the code is somehow pulling through the first record it comes to with that surname.

Private Sub CommandButton2_Click()
UserForm5.Hide
Sheets("SARS").Activate
Cells(Rows.count, Range("a1").Column).End(xlUp).Activate
Index = UserForm3.ListBox1.ListIndex + 2

Cells(Index, 4) = UserForm5.TextBox7 'Staff Number
Cells(Index, 5) = UserForm5.TextBox8 'DOB
Cells(Index, 2) = StrConv(UserForm5.TextBox11, vbProperCase) 'Forename
Cells(Index, 1) = StrConv(UserForm5.TextBox10, vbProperCase) 'Surname
Cells(Index, 3) = StrConv(UserForm5.TextBox9, vbProperCase) 'Title
Cells(Index, 17) = UserForm5.TextBox12 'Date of SAR
Cells(Index, 21) = UserForm5.TextBox13 'Date valid SAR received
Cells(Index, 19) = UserForm5.TextBox14 'Date Santa01 issued

'ADDRESS
Cells(Index, 6) = StrConv(UserForm5.TextBox15, vbProperCase)
Cells(Index, 7) = StrConv(UserForm5.TextBox16, vbProperCase)
Cells(Index, 8) = StrConv(UserForm5.TextBox17, vbProperCase)
Cells(Index, 9) = UCase(UserForm5.TextBox18)

Cells(Index, 11) = StrConv(UserForm5.TextBox19, vbProperCase) 'Rep Name

'REP ADDRESS
Cells(Index, 12) = StrConv(UserForm5.TextBox20, vbProperCase)
Cells(Index, 13) = StrConv(UserForm5.TextBox21, vbProperCase)
Cells(Index, 14) = StrConv(UserForm5.TextBox22, vbProperCase)
Cells(Index, 15) = UCase(UserForm5.TextBox23)

Cells(Index, 10) = UserForm5.TextBox27 'Telephone No

Cells(Index, 16) = UserForm5.TextBox28 'Rep Telephone No

Sheets("quicklook").Activate
Cells(Rows.count, Range("a1").Column).End(xlUp).Activate
Lastrow = ActiveCell.Row
Index = Lastrow + 1

Cells(Index, 3) = UserForm1.TextBox2 'Staff Number
Cells(Index, 2) = StrConv(UserForm1.TextBox4, vbProperCase) 'Forename
Cells(Index, 1) = StrConv(UserForm1.TextBox5, vbProperCase) 'Surname
Cells(Index, 4) = UserForm1.TextBox8 'Date valid SAR received

Dim SURNAME As Long
Call UserForm6initialise(SURNAME)

End Sub

Sub UserForm6initialise(Index As Long)
Index = UserForm3.ListBox1.ListIndex + 2

Sheets("SARS").Activate
UserForm6.TextBox1 = Cells(Index, 4)
UserForm6.TextBox2 = Cells(Index, 2)
UserForm6.TextBox3 = Cells(Index, 1)
UserForm6.TextBox4 = Cells(Index, 3)
UserForm6.ComboBox1 = Cells(Index, 24)
UserForm6.TextBox5 = Cells(Index, 25)
UserForm6.TextBox6 = Cells(Index, 26)
UserForm6.ComboBox4 = Cells(Index, 28)
UserForm6.TextBox10 = Cells(Index, 29)
UserForm6.TextBox19 = Cells(Index, 30)
UserForm6.ComboBox5 = Cells(Index, 32)
UserForm6.TextBox11 = Cells(Index, 33)
UserForm6.TextBox20 = Cells(Index, 34)
UserForm6.ComboBox6 = Cells(Index, 36)
UserForm6.TextBox12 = Cells(Index, 37)
UserForm6.TextBox21 = Cells(Index, 38)

UserForm6.Show

End Sub

Apologies if this coding looks odd - neither Foxy nor I have had any VB training, so are winging it a bit...
 
Upvote 0
A few questions I'm afraid.

1. Where is SURNAME assigned a value?

2. Why is it being passed as an argument (Index) to UserForm6initialise when it is reassigned in the first line?

3. Is there any code in the UserForm6_Initialize or UserForm6_Activate event procedures?
 
Upvote 0
1. To be honest, we put the SURNAME bit in here because it worked for us in UserForm3 when calling UserForm4 (see code below):

Private Sub CommandButton1_Click()
UserForm4.TextBox47.value = FormatDateTime(Date, vbShortDate)
Sheets("SARs").Activate
Dim SURNAME As Long
SURNAME = UserForm3.ListBox1.ListIndex + 1
If MsgBox("Confirm" & " " & Cells(SURNAME + 1, 1) & " " & Cells(SURNAME + 1, 4), vbQuestion + vbYesNo) = vbYes Then
'MsgBox ("Confirm cleared on date...") 'not finished

Call UserForm4initialise(SURNAME)
End If

End Sub

I expect this proves my point about winging it!

2. No idea. Would it help if we changed it to:
Dim Index As Long
Call UserForm6initialise(Index)

End Sub

Sub UserForm6initialise(Index As Long)
Index = UserForm3.ListBox1.ListIndex + 2

?

3. This is the code attached to UserForm6 - command button to save details.

Private Sub CommandButton1_Click()
UserForm6.Hide
Sheets("SARS").Activate
Cells(Rows.count, Range("a1").Column).End(xlUp).Activate
Index = UserForm3.ListBox1.ListIndex + 2

Cells(Index, 24) = UserForm6.ComboBox1
Cells(Index, 25) = UserForm6.TextBox5
Cells(Index, 26) = UserForm6.TextBox6
Cells(Index, 28) = UserForm6.ComboBox4
Cells(Index, 29) = UserForm6.TextBox10
Cells(Index, 30) = UserForm6.TextBox19
Cells(Index, 32) = UserForm6.ComboBox5
Cells(Index, 33) = UserForm6.TextBox11
Cells(Index, 34) = UserForm6.TextBox20
Cells(Index, 36) = UserForm6.ComboBox6
Cells(Index, 37) = UserForm6.TextBox12
Cells(Index, 38) = UserForm6.TextBox21

End Sub
 
Upvote 0
It's not immediately apparent to me why your code isn't working. The only thing I can think of is that there is something in the Activate event procedure that is overriding your UserForm6initialise code.
 
Upvote 0

Forum statistics

Threads
1,207,090
Messages
6,076,520
Members
446,211
Latest member
b306750

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top