Select specific cell after closing Msgbox

[ipbr21054]

Morning,
I am using the code shown below.

I complete the userform then using command button 1 the values are sent to my worksheet & sorted A-Z
For Suzuki Yamaha Kawasaki i then see a Msgbox appear advising me "dont forget to add year"
After i click ok on the Msgbox i would like the cell in column I to be selected.

Example.

The values are sent to worksheet & sorted.
The customer in question was inserted at Row 50
I see the Msgbox & when i click ok i then expect the cell at Row 50 in column I to be selected

Many thanks

Private Sub CommandButton1_Click()
      If OptionButton1.Value = True And OptionButton7.Value = False And OptionButton8.Value = False _
      And OptionButton9.Value = False And OptionButton10.Value = False And OptionButton11.Value = False Then
     
        MsgBox "You Must Select A Lead Type", vbCritical, "Lead Type Selection Error Message"
 Else
  If Len(Me.TextBox2.Value) = 17 Then
    Dim i As Long, x As Long
    Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
    Application.ScreenUpdating = False
    For i = 1 To 8
      ControlsArr(i) = Controls(IIf(i > 2, "ComboBox", "TextBox") & i).Value
    Next i
    
    With ThisWorkbook.Worksheets("MCLIST")
      .Range("A8").EntireRow.Insert Shift:=xlDown
      .Range("A8:K8").Borders.Weight = xlThin
      .Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
      
      If ComboBox3.Value = "HONDA" Then
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbRed
      .Cells(8, 9).Font.Color = vbRed
      Else
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbBlack
      .Cells(8, 9).Font.Color = vbBlack
      End If
      
      If OptionButton1.Value Then .Cells(8, 10).Value = "YES"
      If OptionButton2.Value Then .Cells(8, 10).Value = "NO"
      If OptionButton2.Value Then .Cells(8, 11).Value = "N/A"
      If OptionButton7.Value Then .Cells(8, 11).Value = "BUNDLE"
      If OptionButton8.Value Then .Cells(8, 11).Value = "GREY"
      If OptionButton9.Value Then .Cells(8, 11).Value = "RED"
      If OptionButton10.Value Then .Cells(8, 11).Value = "BLACK"
      If OptionButton11.Value Then .Cells(8, 11).Value = "CLEAR"
      
      If ComboBox3.Value = "HONDA" Then
      ns = Array("X", "Y", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", _
                 "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S")
      For i = 0 To UBound(ns)
        If Mid(Range("B8").Value, 10, 1) = ns(i) Then
          Range("I8").Value = "" & 2000 + i
          Exit For
        End If
      Next
      End If
      
      Application.EnableEvents = False
      If .AutoFilterMode Then .AutoFilterMode = False
      x = .Cells(.Rows.Count, 1).End(xlUp).Row
      .Range("A7:K" & x).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlGuess
      .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
      Application.Goto Selection, True
    End With
    ActiveWorkbook.Save
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Unload McListForm
  Else
    MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MCLIST TRANSFER"
    TextBox2.SetFocus
  End If
  End If
      If Me.ComboBox3.Value = "SUZUKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "YAMAHA" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "KAWASAKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    End If
End Sub

 

[maabadi]

Replace this part of Code:

If Me.ComboBox3.Value = "SUZUKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "YAMAHA" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "KAWASAKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    End If

With this

With Me.ComboBox3
If .Value = "SUZUKI" Or .Value = "YAMAHA" Or .Value = "KAWASAKI" Then
    MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    Range("I" & Range("I" & Rows.Count).End(xlup).Row + 1).Activate
End If
End With

Red part select last cell at column I

 

[ipbr21054]

That selects the cell I but it needs to select it for the inserted row

Look at my example above.

If the new row is inserted at say row 81 then we select the cell I on row 81

 

[NoSparks]

question 1) With the code you show in post 1, what is selected at the conclusion of the sub ?

question 2) Your code searches column A for TextBox1 after sorting the data, is that the line that should have the column I cell selected ?

 

[ipbr21054]

Unable to check question 1 as out at present.

question 2 is correct so after I click ok on the message box I would like to have the cell in column I selected.
This would then be the same row as the cell selected in column A

 

[NoSparks]

Depending on the answer to #1, was going to suggest maybe this line

     .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select

should be

     .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).offset(, 8).Select

 

[ipbr21054]

Many thanks that worked fine