Select specific cell after closing Msgbox

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,103
Office Version
  1. 2007
Platform
  1. Windows
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

Rich (BB code):
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,873
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,103
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Replace this part of Code:
VBA 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:
VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,873
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 16, 2010
Messages
4,103
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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:
VBA Code:
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
VBA Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,103
Office Version
  1. 2007
Platform
  1. Windows
No
look at my example
I am unable TO SPECIFY AN EXACT CELL
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What about Edited code at post #7 ?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,103
Office Version
  1. 2007
Platform
  1. Windows
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
 

Forum statistics

Threads
1,148,108
Messages
5,744,878
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top