ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Morning,
I am using the code below stuck on two things if you could advise please.
In column E and G the text will always be 8C KEY
The code below works apart from transfering any value to the cell in column E and G of which stays blank
Im not sure if i need to just delete the OptionButton1 and TextBox4 "shown in red" just put that there to see if it worked but i didnt.
I was trying to add it into the code "shown in blue" but got confused there.
Can you advise please
I am using the code below stuck on two things if you could advise please.
In column E and G the text will always be 8C KEY
The code below works apart from transfering any value to the cell in column E and G of which stays blank
Im not sure if i need to just delete the OptionButton1 and TextBox4 "shown in red" just put that there to see if it worked but i didnt.
I was trying to add it into the code "shown in blue" but got confused there.
Can you advise please
Rich (BB code):
Private Sub CloseForm_Click()
Unload RangerFormKey
End Sub
Private Sub TransferButton_Click()
Dim i As Long
Dim x As Long
Dim Ctrl As Control
Dim lastrow As Long
Cancel = 0
If TextBox1.Text = "" Then
Cancel = 1
MsgBox "CUSTOMER'S NAME FIELD IS EMPTY", vbCritical, "RANGER FIELD EMPTY MESSAGE"
TextBox1.SetFocus
ElseIf TextBox2.Text = "" Then
Cancel = 1
MsgBox "VIN FIELD IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
TextBox2.SetFocus
ElseIf ComboBox1.Text = "" Then
Cancel = 1
MsgBox "YEAR IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
ComboBox1.SetFocus
End If
Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B5:H5").Borders.LineStyle = xlContinuous
Range("B5:H5").Borders.Weight = xlThin
Range("B5:H5").Interior.ColorIndex = 6
Range("C5:H5").HorizontalAlignment = xlCenter
Sheets("RANGER").Range("B5").Select
Cancel = 0
If Cancel = 1 Then
Exit Sub
End If
With ThisWorkbook.Worksheets("RANGER")
.Range("B5").Value = TextBox1.Text
.Range("D5").Value = TextBox2.Text
.Range("F5").Value = TextBox3.Text
.Range("E5").Value = TextBox4.Text
.Range("G5").Value = TextBox4.Text
.Range("C5").Value = ComboBox1.Text
.Range("H5").Value = ComboBox2.Text
End With
With Sheets("RANGER")
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 5).End(xlUp).Row
.Range("A4:H" & x).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess
End With
Unload RangerFormKey
Unload RangerOpeningForm
ActiveWorkbook.Save
MsgBox "DATABASE HAS BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
Application.ScreenUpdating = True
Range("B6").Select
Range("B5").Select
End Sub
Private Sub TextBox1_Change()
TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
TextBox2 = UCase(TextBox2)
End Sub
Private Sub TextBox3_Change()
TextBox3 = UCase(TextBox3)
End Sub
Private Sub TextBox4_Change()
TextBox4 = UCase(TextBox4)
End Sub
Private Sub ComboBox1_Change()
ComboBox1 = UCase(ComboBox1)
End Sub
Private Sub ComboBox2_Change()
ComboBox2 = UCase(ComboBox2)
End Sub
Private Sub OptionButton1_Change()
If OptionButton1.Value = True Then
TextBox4.Visible = False
TextBox4.Value = "8C KEY"
End If
End Sub
Private Sub UserForm_Initialize()
OptionButton1.Visible = False
TextBox4.Visible = False
End Sub