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

 

[Zot]

I saw that you use Application.Goto there. You can use it to get the cursor to go to specified range/cell

Application.Goto Reference:=Worksheets("Sheet1").Range("A200"), Scroll:=True
or simply
Application.Goto Sheets("Sheet1").Range("A200"), True

 

[ipbr21054]

The specified cell is always different so i cant use a target cell like A200
It needs to select the cell in column I depending where the new row was inserted

 

[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 code and change Year limitation to what you want also:

Dim VYear As Long, defY As Variant

defY = Format(DateAdd("YYYY", 0, Now), "YYYY")
With Me.ComboBox3
If .Value = "SUZUKI" Or .Value = "YAMAHA" Or .Value = "KAWASAKI" Then
Resum3:
VYear = Application.InputBox("DONT FORGET TO ADD YEAR", "MOTORCYCLE YEAR MESSAGE", , , , , , 2)
If VYear > 2014 And VYear < defY Then
    Range("I50").Value = VYear
ElseIf VYear = 0 Then
    Exit Sub
Else
    Do Until VYear > 2014 And VYear < defY
    MsgBox "Please enter a year not earlier than 2015 and not later than this year"
    GoTo Resum3
    Loop
End If
End If
End With

 

[Zot]

The specified cell is always different so i cant use a target cell like A200
It needs to select the cell in column I depending where the new row was inserted

Looks like I misunderstood your real need

 

[ipbr21054]

The code with the input field by @maabadi took say 4 attempts to work but then did add the year.

I just need the cell select please & thats all

Thanks

If the existing code enters the new row at say 50 then we select I50

If new row is 77 then select I77

If new row 23456 then we select cell I23456

 

[maabadi]

change Year limitation to what you want also

I tell you to change year limitation at code, but if you want to active I50 and then input year try 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("I50").Activate
End If
End With

Or

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

 

[ipbr21054]

No
look at my example
I am unable TO SPECIFY AN EXACT CELL

 

[maabadi]

What about Edited code at post #7 ?

 

[ipbr21054]

I wish to select the cell in column I on the inserted row.
I do not need input filed.
I just need to select cell I