Populate userform radio (option) buttons based on data in worksheet

[d.sanchez]

Hi everybody.

I have a worksheet with row entries of client profile information. In order to protect the data from user error, a Userform is used to edit the client's contact information. By clicking on a button, "Edit Client", an Input box is loaded asking the user what client ID# they wish to edit. This number input is put into a textbox on the userform, and all the textboxes and radio buttons on the userform are populated by referencing the Row# that the Client ID# appears on in the worksheet.

Currently, all the textboxes are populating perfectly. However, the radio button selections are not populating correctly. When the userform initializes, the radio buttons will not populate. BUT... BUT... I have a command to re-initialize the userform if the Client ID# is changed on the userform, and if that is triggered the radio buttons populate correctly.

I have been scratching my head why and I can't see what the problem is. So, I am reaching out to see if anybody else has had this problem, or can offer a solution.

Anybody have any insight?

Code:

Sub EditClientForm()

Dim ws As Worksheet

'/ set the worksheet as the sheet that contains the target database
Set ws = Sheet4

'/ Activate the worksheet so the user can see what client they wish to select when the Inputbox appears
ws.Activate

ClientSelection = InputBox("What Client do you want to edit?" & Chr(10) & "(1, 2, 3, etc)", "Select a client to edit", "Enter CLIENT ID # here")

If ClientSelection = "" Then Exit Sub

'/ The following puts the user's input into a textbook on the userform, 
'/ which will in turn target a line entry in the worksheet, and thereby control the population of the textbooks and radio buttons

EditClient.tbClientNumber.Value = ClientSelection  

EditClient.Show       '/ This initializes the userform

End Sub




Private Sub UserForm_Initialize()

	'/ THIS POPULATES THE USERFORM WITH THE EXISTING CLIENT INFO, AS APPEARS IN THE WORKSHEET

	'/ DECLARATIONS:
Dim ws As Worksheet
	'/ set the worksheet as the sheet that contains the target database
Set ws = Sheet4

ClientNumber = tbClientNumber.Value

	'/ Search the sheet (Column A) to find the row that contains the target client number
Dim SearchRange As Range
Dim FindRow As Range
Set SearchRange = ws.Range("A1", ws.Range("A65536").End(xlUp))
Set FindRow = SearchRange.Find(ClientNumber, LookIn:=xlValues, lookat:=xlWhole)
EnterRow = FindRow.Row

	'/ Enter the information from the Worksheet into the Userform

tbCompanyName = ws.Range("B" & EnterRow).Value

	'/ Prefix Options
If ws.Cells(EnterRow, 3).Value = "Mr." Then
    btnMr.Value = True
    Else
    btnMr.Value = False
End If
If ws.Cells(EnterRow, 3).Value = "Mrs." Then
    btnMrs.Value = True
    Else
    btnMrs.Value = False
End If
If ws.Cells(EnterRow, 3).Value = "Ms." Then
    btnMs.Value = True
    Else
    btnMs.Value = False
End If
If ws.Cells(EnterRow, 3).Value = "Dr." Then
    btnDr.Value = True
    Else
    btnDr.Value = False
End If


	'/ Property Owner, Company Rep's Name
tbFirstName = ws.Range("D" & EnterRow).Value
tbLastName = ws.Range("E" & EnterRow).Value

	'/ Domestic Address
tbStreetAddress1 = ws.Range("F" & EnterRow).Value
tbCityTown1 = ws.Range("G" & EnterRow).Value
tbProvince1 = ws.Range("H" & EnterRow).Value
tbPostalCode1 = ws.Range("I" & EnterRow).Value
tbHomePhone1 = ws.Range("J" & EnterRow).Value
tbWorkPhone1 = ws.Range("K" & EnterRow).Value
tbCellPhone1 = ws.Range("L" & EnterRow).Value
tbEmail1 = ws.Range("M" & EnterRow).Value

End Sub

[AlphaFrog]

I'm assuming the Prefix buttons are the option buttons in question?

You would only have to set one option button to true. The others will automatically set to false as is the nature of grouped option buttons.

Try something like this.

The LCase function makes it case insensitive e.g., Mr. = MR.

If you're missing a period, it's not a match e.g., Mr <> Mr.

    '/ Prefix Options
    Select Case LCase(ws.Cells(EnterRow, 3).Value)
        Case LCase("Mr."): btnMr.Value = True
        Case LCase("Mrs."): btnMrs.Value = True
        Case LCase("Ms."): btnMs.Value = True
        Case LCase("Dr."): btnDr.Value = True
    End Select

[d.sanchez]

Ah, brilliant. Select Case, a much better approach! Thank you for the insight.

I will try this in the morning and update the thread with a result and comment.

Much appreciated!

[AlphaFrog]

This is a little more robust. The period is now optional e.g., Mr = Mr.

    '/ Prefix Options
    Select Case Trim(LCase(Replace(ws.Cells(EnterRow, 3).Value, ".", "")))
        Case "mr": btnMr.Value = True
        Case "mrs": btnMrs.Value = True
        Case "ms": btnMs.Value = True
        Case "dr": btnDr.Value = True
    End Select

[d.sanchez]

Worked perfectly. I will apply this technique to other userforms as well. Thank you very much!!