Userform TextBox1 value to specific cell on worksheet

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,097
Office Version
  1. 2007
Platform
  1. Windows
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

Rich (BB code):
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
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,928
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
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
 
Solution

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,097
Office Version
  1. 2007
Platform
  1. Windows
Thanks it worked fine.

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,928
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,147,959
Messages
5,744,045
Members
423,843
Latest member
alex2022

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