ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Morning,
I am using the working code below.
Currently when the command button is pressed the code checks to make sure each textbox etc has a value then transfers the values to my worksheet or show the msgbox message.
I would like to remove the check option.
I no longer need to check if all textboxes etc are complete.
On some textboxes now a few can be left empty but im not sure which portion needs to be deleted.
Am i correct to just delete the code in Red ?
I am using the working code below.
Currently when the command button is pressed the code checks to make sure each textbox etc has a value then transfers the values to my worksheet or show the msgbox message.
I would like to remove the check option.
I no longer need to check if all textboxes etc are complete.
On some textboxes now a few can be left empty but im not sure which portion needs to be deleted.
Am i correct to just delete the code in Red ?
Rich (BB code):
Private Sub CommandButton1_Click()
If Len(Me.TextBox2.Value) = 17 Then
Dim i As Integer
Dim x As Long
Dim ControlsArr(1 To 8) As Variant
For i = 1 To 8
If i > 2 Then
With Me.Controls("ComboBox" & i)
If .ListIndex = -1 Then
MsgBox "YOU MUST COMPLETE ALL FIELDS", vbCritical, "MC LIST TRANSFER"
TextBox1.SetFocus
Exit Sub
Else
ControlsArr(i) = .Value
End If
End With
Else
ControlsArr(i) = Me.Controls("TextBox" & i).Value
End If
Next i
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("MC LIST")
.Range("A8").EntireRow.Insert Shift:=xlDown
.Range("A8:I8").Borders.Weight = xlThin
.Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
.Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = -16776961
.Cells(8, 9).Value = GetYear(Mid(.Cells(8, 2).Value, 10, 1))
End With
Range("B8").Select
Range("A8").Select
ActiveWorkbook.Save
Application.ScreenUpdating = True
MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
With ThisWorkbook.Worksheets("MC LIST")
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A7:I" & x).Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess
.Range("B8").Select
.Range("A8").Select
End With
Unload McListForm
Else
MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MC LIST TRANSFER"
TextBox2.SetFocus
End If
End Sub