VBA TextBox IF Then Else

[rameezl17]

Hi All,

I have been trying to figure this out for the past hour and I don't understand what I am doing wrong. I have a userform with a textbox. This textbox is grabbing a value from an excel spreadsheet only when a cell in an excel sheet says yes. Otherwise if the cell says no I need the user to input a number. I have this code in now. The problem is that when the cell says "No", and I type into the textbox, the textbox automatically deletes what i type in...Im assuming this is because of the "". Any help would be greatly appreciated, thank you!

Private Sub TextBox1_AfterUpdate()
If Worksheets("Sec. 8").Range("J15").Value = "Yes" And Worksheets("Sec. 8").Range("J16").Value = "Yes" Then
TextBox1.Value = Worksheets("Sec. 8").Range("F17")
ElseIf Worksheets("Sec. 8").Range("J15").Value = "Yes" Then
TextBox1.Value = Worksheets("Sec. 8").Range("F15")
ElseIf Worksheets("Sec. 8").Range("J16").Value = "Yes" Then
TextBox1.Value = Worksheets("Sec. 8").Range("F16")
ElseIf Worksheets("Sec. 8").Range("J15").Value = "No" Then
TextBox1.Value = ""
ElseIf Worksheets("Sec. 8").Range("J16").Value = "No" Then
TextBox1.Value = ""
End If

 

[mikerickson]

You need to put that code in the Intialize event. Unless the userform changes the values of J15 or J16, you want to set the value of the textbox at the start and then disable user entry when appropriate

Private Sub Userform_Initialize()

    TextBox1.Enabled = False
    If Worksheets("Sec. 8").Range("J15").Value = "Yes" And Worksheets("Sec. 8").Range("J16").Value = "Yes" Then
        TextBox1.Value = Worksheets("Sec. 8").Range("F17")
    ElseIf Worksheets("Sec. 8").Range("J15").Value = "Yes" Then
        TextBox1.Value = Worksheets("Sec. 8").Range("F15")
    ElseIf Worksheets("Sec. 8").Range("J16").Value = "Yes" Then
        TextBox1.Value = Worksheets("Sec. 8").Range("F16")
    Else
        TextBox1.Enabled = True
        TextBox1.Value = ""
    End If
End Sub

 

[rameezl17]

I got it thank you!

 

[Rick Rothstein]

You need to put that code in the Intialize event. Unless the userform changes the values of J15 or J16, you want to set the value of the textbox at the start and then disable user entry when appropriate

[table="width: 500"]
[tr]
	[td]Private Sub Userform_Initialize()
    TextBox1.Enabled = False
    If Worksheets("Sec. 8").Range("J15").Value = "Yes" And Worksheets("Sec. 8").Range("J16").Value = "Yes" Then
        TextBox1.Value = Worksheets("Sec. 8").Range("F17")
    ElseIf Worksheets("Sec. 8").Range("J15").Value = "Yes" Then
        TextBox1.Value = Worksheets("Sec. 8").Range("F15")
    ElseIf Worksheets("Sec. 8").Range("J16").Value = "Yes" Then
        TextBox1.Value = Worksheets("Sec. 8").Range("F16")
    Else
        TextBox1.Enabled = True
        TextBox1.Value = ""
    End If
End Sub[/td]
[/tr]
[/table]

In addition to Mike's comment, you find it interesting that the above code can be condensed somewhat... the following code will do the same thing as the above code does.

[table="width: 500"]
[tr]
	[td]Private Sub Userform_Initialize()
  Dim Ofst As Long
  Ofst = ['Sec. 8'!J15="Yes"] + 2 * ['Sec. 8'!J16="Yes"]
  Sheets("Sec. 8").TextBox1.Enabled = Not CBool(Ofst)
  Sheets("Sec. 8").TextBox1.Value = Evaluate("IF(" & Ofst & ",OFFSET('Sec. 8'!F14," & -Ofst & ",0),"""")")
End Sub[/td]
[/tr]
[/table]