Userform TextBox1 value to specific cell on worksheet

[ipbr21054]

Evening,

My code is shown below.
I made an edit to the original working code but now not sure how to get the value in TextBox1 to the cell in column G
Can you advise please.

ComboBox5 is a YES / NO selection.
If YES is selected then TextBox1 is shown "not visible as default"
The user would then enter a value.

When CommandButton1 is run i would like the value of TextBox1 to be entered in the inserted row in column G

There will not always be a value in TextBox1 so i cant add it to the controls array code
I added the line of code in Red but it didnt work & didnt show an error msg either

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 6
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT ALL OPTIONS", 48, "X300 IMMO LIST TRANSFER"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
   
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)
   
    With ThisWorkbook.Worksheets("X300 PRO 3 LIST")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:F4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(4, i + 1) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
              TextBox1.Value = .Cells(4, 7)
         End Select
    Next i
    End With
   
    Application.ScreenUpdating = False
    With Sheets("X300 PRO 3 LIST")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A3:F" & x).Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    Sheets("X300 PRO 3 LIST").Range("A4").Select
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Unload X300ImmoListForm
End Sub

 

[DanteAmor]

Try this:

Private Sub CommandButton1_Click()
   Dim i As Integer
   Dim ControlsArr As Variant, ctrl As Variant
   Dim x As Long
   For i = 1 To 6
      With Me.Controls("ComboBox" & i)
           If .ListIndex = -1 Then
               MsgBox "MUST SELECT ALL OPTIONS", 48, "X300 IMMO LIST TRANSFER"
               .SetFocus
               Exit Sub
           End If
       End With
   Next i
  
   ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)
  
   With ThisWorkbook.Worksheets("X300 PRO 3 LIST")
       .Range("A4").EntireRow.Insert Shift:=xlDown
       .Range("A4:G4").Borders.Weight = xlThin
       .Cells(4, "G") = TextBox1.Value
       For i = 0 To UBound(ControlsArr)
        Select Case i
           Case 1, 2, 4
              .Cells(4, i + 1) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
           Case Else
              .Cells(4, i + 1) = ControlsArr(i)
              ControlsArr(i).Text = ""
        End Select
       Next i
   End With
  
   Application.ScreenUpdating = False
   With Sheets("X300 PRO 3 LIST")
       If .AutoFilterMode Then .AutoFilterMode = False
       x = .Cells(.Rows.Count, 1).End(xlUp).Row
       .Range("A3:F" & x).Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess
   End With
   ActiveWorkbook.Save
   Application.ScreenUpdating = True
   Sheets("X300 PRO 3 LIST").Range("A4").Select
   MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
   Unload X300ImmoListForm
End Sub

 

[ipbr21054]

Thanks it worked fine.

I knew it was something simple but after a few failed attempts i just couldnt see the answer

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.