Populate Data in the same column under different textbox userform

Cyril Beki

Board Regular
Joined
Sep 18, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
Hello expert,

I need to populate data in the same column using different textbox (Textbox 1 & Textbox2). Textbox 1 or Textbox 2 will be disable and "gray out" according to the combo box(ComboBox1) selection. (Refer Image)
But when i click on the Add Button, the data doesn't populate inside the sheet especially when my Combobox Selection is "Selection" (Refer image). Below is my Code
VBA Code:
Private Sub ComboBox1_Change()
If ComboBox1.Value = "Section" Then
TextBox1.Enabled = True
TextBox2.Enabled = False
TextBox2.BackColor = &H8000000F
TextBox1.BackColor = &H8000000E
Me.TextBox2.text = ""
TextBox1.text = "Please Enter New Section Here"
End If

If ComboBox1.Value = "Machine" Then
TextBox1.Enabled = False
TextBox2.Enabled = True
TextBox1.BackColor = &H8000000F
TextBox2.BackColor = &H8000000E
Me.TextBox1.text = ""
TextBox2.text = "Please Enter New Machine Here"
End If

End Sub

Private Sub Add_Click()
Dim text As Object
Set text = Sheet2.Range("A50000").End(xlUp)

'Add Data
text.Offset(1, 0).Value = Me.ComboBox1.Value
text.Offset(1, 1).Value = Me.TextBox1.Value
text.Offset(1, 1).Value = Me.TextBox2.Value

'Clear form after add
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo Error
 With TextBox1
        If .text = "Please Enter New Section Here" Then
            .ForeColor = &H80000008 '<~~ Black Color
            .text = ""
        End If
    End With
Me.ComboBox1.Value = Me.ListBox1.Column(0)
Me.TextBox1.Value = Me.ListBox1.Column(1)

With TextBox2
        If .text = "Please Enter New Machine Here" Then
            .ForeColor = &H80000008 '<~~ Black Color
            .text = ""
        End If
    End With
Me.ComboBox1.Value = Me.ListBox1.Column(0)
Me.TextBox2.Value = Me.ListBox1.Column(1)
Exit Sub
Error:
End Sub

Private Sub TextBox1_Enter()
If ComboBox1.Value = "Section" Then
    With TextBox1
        If .text = "Please Enter New Section Here" Then
            .ForeColor = &H80000008 '<~~ Black Color
            .text = ""
        End If
    End With
End If
End Sub
Private Sub TextBox2_Enter()
If ComboBox1.Value = "Machine" Then
    With TextBox2
        If .text = "Please Enter New Machine Here" Then
            .ForeColor = &H80000008 '<~~ Black Color
            .text = ""
        End If
    End With
End If
End Sub
Private Sub TextBox1_AfterUpdate()
    With TextBox1
        If .text = "" Then
            .ForeColor = &HFF0000
            .text = "Please Enter New Section Here"
        End If
    End With
End Sub
Private Sub TextBox2_AfterUpdate()
    With TextBox2
        If .text = "" Then
            .ForeColor = &HFF0000
            .text = "Please Enter New Machine Here"
        End If
    End With
End Sub

Private Sub UserForm_Initialize()

'Instructional Text Inside Textbox
    TextBox1.ForeColor = &HFF0000
    TextBox2.ForeColor = &HFF0000 '<~~ Blue Color
    TextBox1.text = "Please Enter New Section Here"
    TextBox2.text = "Please Enter New Machine Here"
    
'Content in normal combobox

With ComboBox1
    .AddItem "Section"
    .AddItem "Machine"
    .AddItem "Module"
    .AddItem "Problem Category"
    .AddItem "Problem"
End With

'Populate Listbox
On Error Resume Next
Me.ListBox1.RowSource = Sheet2.Range("Problem").Address(external:=True)
End Sub
 

Attachments

  • Situation.PNG
    Situation.PNG
    2.5 KB · Views: 8
  • not populate.PNG
    not populate.PNG
    2.6 KB · Views: 8

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Cyril Beki

Board Regular
Joined
Sep 18, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
Hello expert,

I need to populate data in the same column using different textbox (Textbox 1 & Textbox2). Textbox 1 or Textbox 2 will be disable and "gray out" according to the combo box(ComboBox1) selection. (Refer Image)
But when i click on the Add Button, the data doesn't populate inside the sheet especially when my Combobox Selection is "Selection" (Refer image). Below is my Code
VBA Code:
Private Sub ComboBox1_Change()
If ComboBox1.Value = "Section" Then
TextBox1.Enabled = True
TextBox2.Enabled = False
TextBox2.BackColor = &H8000000F
TextBox1.BackColor = &H8000000E
Me.TextBox2.text = ""
TextBox1.text = "Please Enter New Section Here"
End If

If ComboBox1.Value = "Machine" Then
TextBox1.Enabled = False
TextBox2.Enabled = True
TextBox1.BackColor = &H8000000F
TextBox2.BackColor = &H8000000E
Me.TextBox1.text = ""
TextBox2.text = "Please Enter New Machine Here"
End If

End Sub

Private Sub Add_Click()
Dim text As Object
Set text = Sheet2.Range("A50000").End(xlUp)

'Add Data
text.Offset(1, 0).Value = Me.ComboBox1.Value
text.Offset(1, 1).Value = Me.TextBox1.Value
text.Offset(1, 1).Value = Me.TextBox2.Value

'Clear form after add
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo Error
 With TextBox1
        If .text = "Please Enter New Section Here" Then
            .ForeColor = &H80000008 '<~~ Black Color
            .text = ""
        End If
    End With
Me.ComboBox1.Value = Me.ListBox1.Column(0)
Me.TextBox1.Value = Me.ListBox1.Column(1)

With TextBox2
        If .text = "Please Enter New Machine Here" Then
            .ForeColor = &H80000008 '<~~ Black Color
            .text = ""
        End If
    End With
Me.ComboBox1.Value = Me.ListBox1.Column(0)
Me.TextBox2.Value = Me.ListBox1.Column(1)
Exit Sub
Error:
End Sub

Private Sub TextBox1_Enter()
If ComboBox1.Value = "Section" Then
    With TextBox1
        If .text = "Please Enter New Section Here" Then
            .ForeColor = &H80000008 '<~~ Black Color
            .text = ""
        End If
    End With
End If
End Sub
Private Sub TextBox2_Enter()
If ComboBox1.Value = "Machine" Then
    With TextBox2
        If .text = "Please Enter New Machine Here" Then
            .ForeColor = &H80000008 '<~~ Black Color
            .text = ""
        End If
    End With
End If
End Sub
Private Sub TextBox1_AfterUpdate()
    With TextBox1
        If .text = "" Then
            .ForeColor = &HFF0000
            .text = "Please Enter New Section Here"
        End If
    End With
End Sub
Private Sub TextBox2_AfterUpdate()
    With TextBox2
        If .text = "" Then
            .ForeColor = &HFF0000
            .text = "Please Enter New Machine Here"
        End If
    End With
End Sub

Private Sub UserForm_Initialize()

'Instructional Text Inside Textbox
    TextBox1.ForeColor = &HFF0000
    TextBox2.ForeColor = &HFF0000 '<~~ Blue Color
    TextBox1.text = "Please Enter New Section Here"
    TextBox2.text = "Please Enter New Machine Here"
   
'Content in normal combobox

With ComboBox1
    .AddItem "Section"
    .AddItem "Machine"
    .AddItem "Module"
    .AddItem "Problem Category"
    .AddItem "Problem"
End With

'Populate Listbox
On Error Resume Next
Me.ListBox1.RowSource = Sheet2.Range("Problem").Address(external:=True)
End Sub
Another problem is that when i double click the listbox, the data shown in textbox is not sorted well (Refer Image).
Based on the image, textbox 2 should be blank, and only textbox 1 is populated.
 

Attachments

  • Not sorted.PNG
    Not sorted.PNG
    19.6 KB · Views: 7

Forum statistics

Threads
1,147,475
Messages
5,741,347
Members
423,656
Latest member
Medrok2021

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
Top