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
 

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
Rich (BB 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
Red part select last cell at column I
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

ipbr21054

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

Well-known Member
Joined
Mar 15, 2013
Messages
1,274
Office Version
  1. 2010
Platform
  1. Windows
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

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

Well-known Member
Joined
Mar 15, 2013
Messages
1,274
Office Version
  1. 2010
Platform
  1. Windows
Depending on the answer to #1, was going to suggest maybe this line
VBA Code:
     .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
should be
VBA Code:
     .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).offset(, 8).Select
 
Solution

Forum statistics

Threads
1,148,108
Messages
5,744,876
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