Linking Userform Textbox with Userform Combobox Drop Down Value

[PatHat]

Hi Guys,

I would love some help here if you can.

I have a Userform that has a Textbox1 and a Combobox1, with a command button. The Combo Box drop down has a list of items and gets the data from a sheet somewhere else in the workbook.
What I'd like to happen is if the user selects the PERSONAL EFFECTS option from the combo box drop down list and clicks the command button, then Textbox1 on the same Userform gets filled in with text that says "Personal Effects (cover 20% of Contents Sum Insured)"

Here is my code. But when I click on the command button nothing happens on TextBox1. I'm new to VBA so forgive me if this is a silly post.

Private Sub CommandButton1_Click()
If Me.ComboBox1.Value = "PERSONAL EFFECTS" Then
Me.TextBox1.Value = "Personal Effects (cover 20% of Contents Sum Insured)"
End If
End Sub

 

[Trevor G]

Try adding this behind the Combobox On Change Event

Private Sub ComboBox1_Change()
If Me.ComboBox1 = "Personal Effects" Then
    Me.TextBox1 = "Personal Effects (cover 20% of Contents Sum Insured)"
    Else
    Me.TextBox1 = ""
End If
End Sub

 

[PatHat]

Hi Trevor,

Thanks. There is other code that needs to run when the command button is clicked.

Maybe I should show the full code. I've inserted your code (see in bold), but of course it wont work like this. How do I put your Combobox1 sub within my CommandButton sub?

Private Sub CommandButton1_Click()
'All Risks 1 Calculate & Save Button which writes data to the worksheet and retrieves premium
Platinum_PL.Activate
Sheets("All Risks").Select
Application.ScreenUpdating = False
Application.GoTo Reference:="AR_Item1"
Selection.EntireRow.Hidden = False
If Me.ComboBox1 = "Personal Effects" Then
Me.TextBox1 = "Personal Effects"
Else
Me.TextBox1 = ""
Sheets("All Risks").Range("AR_Data").Offset(1, 0).Value = TextBox1
Sheets("All Risks").Range("AR_Data").Offset(1, 1).Value = TextBox2
Sheets("All Risks").Range("AR_Data").Offset(1, 2).Value = TextBox3
Sheets("All Risks").Range("AR_Data").Offset(1, 8).Value = ComboBox1
Me.Txt_AR1_AnnPrem = CStr(ThisWorkbook.Sheets("All Risks").Range("AR1AnnPrem").Value)
Me.Txt_AR1_AnnPrem = Format(ThisWorkbook.Sheets("All Risks").Range("AR1AnnPrem"), "0.00")
Me.Txt_AR1_MonthPrem = CStr(ThisWorkbook.Sheets("All Risks").Range("AR1MonthPrem").Value)
Me.Txt_AR1_MonthPrem = Format(ThisWorkbook.Sheets("All Risks").Range("AR1MonthPrem"), "0.00")
End If
End Sub

 

[Trevor G]

Pat,

Change the IF to and you should remove the end if this way. You code is looking to have something in textbox1 so you don't want to clear it

If Me.ComboBox1 = "Personal Effects" Then Me.TextBox1 = "Personal Effects (cover 20% of Contents Sum Insured)"

If that doesn't work then i'd suggest uploading a link to a copy of the workbook, only showing some sample data (not real) and I can take a look.

 

[PatHat]

Thanks Trevor I'd appreciate if you could take a look please! On the All Risks sheet if you click on the Add More Items button, then its item 1 that I need this code to affect. Much appreciated

I'm not sure how to share it tho. Could I email it to you?

 

[Trevor G]

Pat, is it possible for you to upload a copy into Dropbox, OneDrive or some other area where you can share a link and I can take a look.

The forum doesn't approve of emailing outside the forum.

 

[PatHat]

Righto. Think I've figured it out

Microsoft OneDrive

1drv.ms

 

[Trevor G]

The VBA screen has been password protected.

Sent a private message to you.

 

[PatHat]

The VBA screen has been password protected.

Sent a private message to you.

Sorry about that

 

[Trevor G]

Hi Pat it has an extra space in the dropdown list, once removed it works fine. So technically you'll have to check each list item to take out any extra spaces.

Using Len as a formula will help.

Human error happens sometimes.

Book2
	A	B
1	Personal Effects	16
2	PERSONAL EFFECTS	17
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=LEN(A1)