ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Hi,
My code in use is supplied below.
I have a userform where i enter values etc in TextBoxes BUT ComboBox2 has a few possible selections.
Depending on selection made then depends on what happens after the line of code in RED
This is how it show work.
If the selection in ComboBox2 = ORIGINAL 2B then RangerPcbNumber.Show
BUT if any other selection is made then
cell I5 should have N/A entered in it & the formatting should also be applied to it.
Followed then by the sorting A-Z coloumn A
My issue is that i select ORIGINAL 2B in ComboBox2 but the RangerPcbNumber userform doesnt open & the code continues with inserting the values to the worksheet.
It also sorts A-Z & nothing is entered in cell I9
So im now confused as to how to proceed with this
My code in use is supplied below.
I have a userform where i enter values etc in TextBoxes BUT ComboBox2 has a few possible selections.
Depending on selection made then depends on what happens after the line of code in RED
This is how it show work.
If the selection in ComboBox2 = ORIGINAL 2B then RangerPcbNumber.Show
BUT if any other selection is made then
cell I5 should have N/A entered in it & the formatting should also be applied to it.
Followed then by the sorting A-Z coloumn A
My issue is that i select ORIGINAL 2B in ComboBox2 but the RangerPcbNumber userform doesnt open & the code continues with inserting the values to the worksheet.
It also sorts A-Z & nothing is entered in cell I9
So im now confused as to how to proceed with this
Rich (BB code):
Private Sub TransferButton_Click()
With ThisWorkbook.Worksheets("RANGER")
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
ElseIf ComboBox2.Text = "" Then
Cancel = 1
MsgBox "TYPE IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
ComboBox2.SetFocus
End If
If Cancel = 1 Then
Exit Sub
End If
x = 0
For i = 1 To 4
If Me.Controls("OptionButton" & i) = True Then
x = x + 1
Opt = i
End If
Next
If x = 0 Then
MsgBox "MY PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
Exit Sub
End If
Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B5:I5").Borders.LineStyle = xlContinuous
Range("B5:I5").Borders.Weight = xlThin
Range("B5:I5").Interior.ColorIndex = 6
Range("C5:I5").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("G5").Value = TextBox4.Text
.Range("C5").Value = ComboBox1.Text
.Range("H5").Value = ComboBox2.Text
.Range("E5").Value = Me.Controls("OptionButton" & Opt).Caption
End With
Unload RangerFormRemote
ActiveWorkbook.Save
If ComboBox2.Value = "Original 2B" Then
RangerPcbNumber.Show
Else
With .Range("I5")
.Range("I5").Value = "N/A"
.Font.Size = 14
.Font.Name = "Calibri"
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlVAlignCenter
End With
With Sheets("RANGER")
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 5).End(xlUp).Row
.Range("A4:I" & x).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess
End With
Application.ScreenUpdating = True
Range("B6").Select
Range("B5").Select
End If
End With
End Sub