ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,832
- Office Version
- 2007
- Platform
- 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
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: