Thread: Userform TextBox that Shows cell Value but can also be Edited

Hey all,

I am trying to figure out how I can make a textbox that will show the value of a cell but also allow you to change the value of that cell by changing the text in the TextBox.

Here is what I have so far.

Private Sub UserForm_Activate()
    'Australia
        If ComboBox1 = "Australia" Then
            TextBox1.Text = Worksheets("Sheet1").Range("A1")
        End If
    'China
        If ComboBox1 = "China" Then
            TextBox1.Text = Worksheets("Sheet1").Range("A2")
        End If
    'India
        If ComboBox1 = "India" Then
            TextBox1.Text = Worksheets("Sheet1").Range("A3")
        End If
End Sub

Right now when you select Australia in the ComboBox it will put the A1 Value in the TextBox.

Likewise when you select China the textbox displays A2, and India shows A3.

For example I want to select Australia in the ComboBox, Have the TextBox display the cellvalue for A1, and allow me to change the text that appears in the textbox to change the value of the cell it is currently showing. in this example A1.

Example. I select China in the ComboBox, the Textbox shows the Value of Cell A2, and I can change the text in the textbox to change the value of cell A2.

Any ideas?

Please let me know if i can clarify anymore.

Thanks,

Mike

If you use something like:

Dim rngText1 as Range
Private Sub UserForm_Activate()
    'Australia
        If ComboBox1 = "Australia" Then
            set rngText1  = Worksheets("Sheet1").Range("A1")
        End If
    'China
        If ComboBox1 = "China" Then
            set rngText1  = Worksheets("Sheet1").Range("A2")
        End If
    'India
        If ComboBox1 = "India" Then
            set rngText1  = Worksheets("Sheet1").Range("A3")
        End If
       Textbox1.Text = set rngText1.Value
End Sub

then in the exit event of the textbox you can use:

rngText1.Value  = Textbox1.text

If you use something like:

Dim rngText1 as Range
Private Sub UserForm_Activate()
    'Australia
        If ComboBox1 = "Australia" Then
            set rngText1  = Worksheets("Sheet1").Range("A1")
        End If
    'China
        If ComboBox1 = "China" Then
            set rngText1  = Worksheets("Sheet1").Range("A2")
        End If
    'India
        If ComboBox1 = "India" Then
            set rngText1  = Worksheets("Sheet1").Range("A3")
        End If
       Textbox1.Text = set rngText1.Value
End Sub

then in the exit event of the textbox you can use:

rngText1.Value  = Textbox1.text

Probably this suffices:

Private Sub UserForm_Activate()
  TextBox1.Text = Worksheets("Sheet1").cells(combobox1.listindex+1,1)
End Sub

Doesn't really address the question though, does it?

Something like this

Private Sub ComboBox1_Change()
Me.TextBox1.Value = Cells(Me.ComboBox1.ListIndex + 1, 1).Value
End Sub


Private Sub TextBox1_AfterUpdate()
Cells(Me.ComboBox1.ListIndex + 1, 1).Value = Me.TextBox1.Value

End Sub