Clear cell contents THEN close userform

[ipbr21054]

Afternoon.
I have a worksheet called HONDA SHEET which has a drop down list in cell A2
When i make a selection from the list my userform called HondaSoldItems is shown & values etc shown.

Now what i would like to happen is when i press my close button on the form "NOT the red cross" cell A2 contents are to be cleared THEN the userform can continue to be closed.

So far unable to achieve this.

I have tried the following.

This only closes the userform as you would expect.

Private Sub CommandButton1_Click()'close the form (itself)
Unload Me
End Sub

This clears the cell A2 but then as opposed to closing the userform i see an error message.

Private Sub CommandButton1_Click()'close the form (itself)
Range("A2").ClearContents
Unload Me
End Sub

The error message as mentioned above is,

Run time error 400
Form already displayed, Cant show modally.
If i debug the item shown below is shown in yellow.

Private Sub Worksheet_Change(ByVal Target As Range)Dim Frng As Range
Set Frng = Range("F21", Range("F" & Rows.Count).End(xlUp))
If Target.Address(0, 0) = "A2" Then
    With HondaSoldItems
        .Caption = "HONDA SOLD ITEMS TABLE"
        .txtQuantitySold.Text = Application.CountIf(Frng, Target.Value)
        .txtSoldItems.Text = Target.Value
        .CommandButton1.SetFocus
[COLOR=#ff0000]        .Show[/COLOR]
        End With
End If
With ThisWorkbook.Sheets("HONDA SHEET")
If Not Intersect(Target, .Range("A13")) Is Nothing And .Range("A13") <> "" Then
If Len(.Range("A13").Value) <> 17 And Len(.Range("A13").Value) <> 11 Then
                .Range("A13").Interior.ColorIndex = 3
                 MsgBox "Honda Japan Use 11 Character Vin Numbers." & vbNewLine & "" & vbNewLine & "Honda Europe Use 17 Character Vin Numbers." & vbNewLine & "" & vbNewLine & "Please Check & Try Again", vbCritical, "Chassis Number Wrong Character Count"
                .Range("A13").ClearContents
                .Range("A13").Interior.ColorIndex = 2
                .Range("A13").Activate
Else
                Application.EnableEvents = False
                .Rows(21).Insert Shift:=xlDown
                .Range("A21:G21").Borders.Weight = xlThin
                .Range("G21").Value = Date
                .Range("A21").Value = UCase(.Range("A13").Value)
                .Range("B21").Select
                .Range("A13").ClearContents
                .Range("A21").Characters(Start:=10, Length:=1).Font.ColorIndex = 3
                .Range("E7").Font.Color = vbRed
                Application.EnableEvents = True
End If
End If


End With


Target.Interior.ColorIndex = 6 '   *** THIS IS CELL A13 ***
If Not Intersect(Target, Range("F21")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub


End If
    If Target.Address = "$F$21" Then
        Call sheettolist




End If
Application.EnableEvents = True


End Sub

Please advise thanks

 

[DanteAmor]

I show you 2 alternatives:


1) Add this line:

Set Frng = Range("F21", Range("F" & Rows.Count).End(xlUp))
If Target.Address(0, 0) = "A2" Then
  [COLOR=#0000ff] If Target.Value = "" Then Exit Sub[/COLOR]

2) or Add this lines:

Private Sub CommandButton1_Click() 'close the form (itself)
[COLOR=#0000ff]  Application.EnableEvents = False[/COLOR]
  Range("A2").ClearContents
[COLOR=#0000ff]  Application.EnableEvents = True[/COLOR]
  Unload Me
End Sub

 

[ipbr21054]

Thanks very much