Cell above is selected when i duplicate row

[ipbr21054]

Hi.
I am using the code below to copy an existing row.

This is how it works.
I select a customer in column A
In this example i will select Tom Jones at cell A100
Using a command button on my worksheet my userform is open with all the textboxes etc prefilled & the user can make any changes, like new phone number.
On the userform once the user is happy he clicks the command button of which is the code below & inserts a new row & the cells then on my worksheet are now filled.
What i have noticed is after the values are added to the worksheet & the userform closes the cell A100 that was originally selected now select the row before Thus being A99

Do you see why & please advise an edit so the cell doesnt move from its orinally selected cell

Private Sub CommandButton1_Click()
  Dim cName As String
  Dim n As Long
  
  cName = TextBox8.Text
  If cName = "" Then
    MsgBox "YOU MUST ENTER A CUSTOMERS NAME", vbCritical, "DATABASE USER FORM NAME TRANSFER"
    TextBox8.SetFocus
    Exit Sub
  End If

  With ThisWorkbook.Worksheets("DATABASE")
    n = WorksheetFunction.CountIf(.Range("A:A"), cName & " ???")
    
    cName = cName & IIf(n = 0, " 001", Format(n + 1, " 000"))
    
    .Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("A6:AC6").Borders.LineStyle = xlContinuous
    .Range("A6:AC6").Borders.Weight = xlThin
    .Range("A6:AC6").Interior.ColorIndex = 6
    .Range("A6:AC6").RowHeight = 25
    .Range("$Q$6").HorizontalAlignment = xlCenter
    .Range("X6").Value = "N/A"
    .Range("O6").NumberFormat = "$#,##0.00"
    
    .Range("A6") = cName                ' CUSTOMERS NAME
    .Range("B6") = Me.ComboBox1.Text    ' REGISTRATION NUMBER
    .Range("C6") = Me.ComboBox2.Text    ' BLANK USED
    .Range("D6") = Me.ComboBox3.Text    ' VEHICLE
    .Range("E6") = Me.ComboBox4.Text    ' BUTTONS
    .Range("F6") = Me.ComboBox5.Text    ' ITEM SUPPLIED
    .Range("G6") = Me.ComboBox6.Text    ' TRANSPONDER CHIP
    .Range("H6") = Me.ComboBox7.Text    ' JOB ACTION
    .Range("I6") = Me.ComboBox8.Text    ' PROGRAMMER USED
    .Range("J6") = Me.ComboBox9.Text    ' KEY CODE
    .Range("K6") = Me.ComboBox10.Text   ' BITING
    .Range("L6") = Me.ComboBox11.Text   ' CHASIS NUMBER
    .Range("N6") = Me.ComboBox12.Text   ' VEHICLE YEAR
    .Range("R6") = Me.TextBox1.Text     ' ADDRESS 1st LINE
    .Range("S6") = Me.TextBox2.Text     ' ADDRESS 2nd LINE
    .Range("T6") = Me.TextBox3.Text     ' ADDRESS 3rd LINE
    .Range("U6") = Me.TextBox4.Text     ' ADDRESS 4TH LINE
    .Range("V6") = Me.TextBox5.Text     ' POST CODE
    .Range("W6") = Me.TextBox6.Text     ' CONTACT NUMBER
    .Range("AA6") = Me.ComboBox13.Text  ' SUPPLIER
    .Range("AB6") = Me.TextBox7.Text    ' PART NUMBER
    .Range("AC6") = Me.ComboBox14.Text  ' PAYMENT TYPE
  End With
  Unload DatabaseRepeatCustomer
End Sub

 

[bobsan42]

if the form is modal you can plug 2 lines in the code, which brings up the userform.
or in this code:

Private Sub CommandButton1_Click()
    Dim ac As Range
    Set ac = ActiveCell
    
  Dim cName As String
  Dim n As Long
  
  cName = TextBox8.text
  If cName = "" Then
    MsgBox "YOU MUST ENTER A CUSTOMERS NAME", vbCritical, "DATABASE USER FORM NAME TRANSFER"
    TextBox8.SetFocus
    Exit Sub
  End If

  With ThisWorkbook.Worksheets("DATABASE")
    n = WorksheetFunction.CountIf(.Range("A:A"), cName & " ???")
    
    cName = cName & IIf(n = 0, " 001", Format(n + 1, " 000"))
    
    .Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("A6:AC6").Borders.LineStyle = xlContinuous
    .Range("A6:AC6").Borders.Weight = xlThin
    .Range("A6:AC6").Interior.ColorIndex = 6
    .Range("A6:AC6").RowHeight = 25
    .Range("$Q$6").HorizontalAlignment = xlCenter
    .Range("X6").Value = "N/A"
    .Range("O6").NumberFormat = "$#,##0.00"
    
    .Range("A6") = cName                ' CUSTOMERS NAME
    .Range("B6") = Me.ComboBox1.text    ' REGISTRATION NUMBER
    .Range("C6") = Me.ComboBox2.text    ' BLANK USED
    .Range("D6") = Me.ComboBox3.text    ' VEHICLE
    .Range("E6") = Me.ComboBox4.text    ' BUTTONS
    .Range("F6") = Me.ComboBox5.text    ' ITEM SUPPLIED
    .Range("G6") = Me.ComboBox6.text    ' TRANSPONDER CHIP
    .Range("H6") = Me.ComboBox7.text    ' JOB ACTION
    .Range("I6") = Me.ComboBox8.text    ' PROGRAMMER USED
    .Range("J6") = Me.ComboBox9.text    ' KEY CODE
    .Range("K6") = Me.ComboBox10.text   ' BITING
    .Range("L6") = Me.ComboBox11.text   ' CHASIS NUMBER
    .Range("N6") = Me.ComboBox12.text   ' VEHICLE YEAR
    .Range("R6") = Me.TextBox1.text     ' ADDRESS 1st LINE
    .Range("S6") = Me.TextBox2.text     ' ADDRESS 2nd LINE
    .Range("T6") = Me.TextBox3.text     ' ADDRESS 3rd LINE
    .Range("U6") = Me.TextBox4.text     ' ADDRESS 4TH LINE
    .Range("V6") = Me.TextBox5.text     ' POST CODE
    .Range("W6") = Me.TextBox6.text     ' CONTACT NUMBER
    .Range("AA6") = Me.ComboBox13.text  ' SUPPLIER
    .Range("AB6") = Me.TextBox7.text    ' PART NUMBER
    .Range("AC6") = Me.ComboBox14.text  ' PAYMENT TYPE
  End With
  Unload DatabaseRepeatCustomer
  
  ac.Parent.Activate
  ac.Select
  Set ac = Nothing
End Sub

 

[ipbr21054]

Thanks that did it