User Form Pause or stop and start after an on change event.

[Mick Peters]

I am fairly new to VBA but I have some code I have made up Userfrom looking at examples. I have a form into which a user scans Name and equipment. I would like once the equipment is scanned in that the form then offloads the information to the worksheet and blanks all the text boxes on the form. The below code works with the exception that because I change the equipment box from what the user just scanned in to blank the event fires again offloading just blank cells to the worksheet. I would like to turn off the change event once it has fired until the user form is reset then turn it back on again.
As with the Application.EnableEvents = False then Application.EnableEvents = True but they don't seem to work.

Any help would be very much appreciated.

Private Sub TxtEquipment_Change()
Range("A2").Select
ActiveCell.End(xlDown).Select
LastRow = ActiveCell.Row
Cells(LastRow + 1, 1).Value = TxtDate.Text
Cells(LastRow + 1, 2).Value = TxtTime.Text
Cells(LastRow + 1, 4).Value = TxtEquipment.Text
Cells(LastRow + 1, 3).Value = TxtUser.Text
Cells(LastRow + 1, 5).Value = TxtBookedOut.Text
Cells(LastRow + 1, 6).Value = TxtUser.Text & TxtEquipment.Text
Range("A2").Select
TxtDate.Value = Date
TxtTime.Value = Time
TxtBookedOut.Value = Range("F1").Value
Application.EnableEvents = False
TxtEquipment.Text = ""
TxtUser.Text = ""
Application.EnableEvents = True
frmDatainput.TxtUser.SetFocus
End Sub

 

[Trevor G]

Why not just add the code to behind a command button simple click to run the code.

 

[Mick Peters]

Thank you for your input Trevor G. Yes you are correct I can add it to a click event but I am trying to reduce the user interaction to a scanner only and if I can get over this hurdle I think I will be there as a scan of the name will auto tab to the equipment and a scan of the equipment will cycle round to the user having offloaded the information to the workbook. Thank you for your input.

 

[Norie]

Why not check if the textbox has a value before proceeding?

 

[Mick Peters]

Why not check if the textbox has a value before proceeding?

Thank you Norie,

I have tried

Private Sub TxtEquipment_Change()
If TxtUser.Text Is Not Null Then
Range("A2").Select
ActiveCell.End(xlDown).Select
LastRow = ActiveCell.Row
Cells(LastRow + 1, 1).Value = TxtDate.Text
Cells(LastRow + 1, 2).Value = TxtTime.Text
Cells(LastRow + 1, 4).Value = TxtEquipment.Text
Cells(LastRow + 1, 3).Value = TxtUser.Text
Cells(LastRow + 1, 5).Value = TxtBookedOut.Text
Cells(LastRow + 1, 6).Value = TxtUser.Text & TxtEquipment.Text
Range("A2").Select
TxtDate.Value = Date
TxtTime.Value = Time
TxtBookedOut.Value = Range("F1").Value
'TxtBookedOut.Value = "OUT"
Application.EnableEvents = False
TxtEquipment.Text = ""
TxtUser.Text = ""
Application.EnableEvents = True
frmDatainput.TxtUser.SetFocus
End If
End Sub

But I get a type mis match. I have also tried the line If TxtUser.Text Is Not "" Then

Do you have any help on this. As I said I am still fairly new to this.
Thank you again.

 

[netuser]

it should be If TxtUser.Value <> "" Then

Thank you Norie,

I have tried

Private Sub TxtEquipment_Change()
If TxtUser.Text Is Not Null Then
Range("A2").Select
ActiveCell.End(xlDown).Select
LastRow = ActiveCell.Row
Cells(LastRow + 1, 1).Value = TxtDate.Text
Cells(LastRow + 1, 2).Value = TxtTime.Text
Cells(LastRow + 1, 4).Value = TxtEquipment.Text
Cells(LastRow + 1, 3).Value = TxtUser.Text
Cells(LastRow + 1, 5).Value = TxtBookedOut.Text
Cells(LastRow + 1, 6).Value = TxtUser.Text & TxtEquipment.Text
Range("A2").Select
TxtDate.Value = Date
TxtTime.Value = Time
TxtBookedOut.Value = Range("F1").Value
'TxtBookedOut.Value = "OUT"
Application.EnableEvents = False
TxtEquipment.Text = ""
TxtUser.Text = ""
Application.EnableEvents = True
frmDatainput.TxtUser.SetFocus
End If
End Sub

But I get a type mis match. I have also tried the line If TxtUser.Text Is Not "" Then

Do you have any help on this. As I said I am still fairly new to this.
Thank you again.

 

[Mick Peters]

it should be If TxtUser.Value <> "" Then

My Error in posting the code, it is txt.Equipment I am running the change event on, but it work like a charm.

If TxtEquipment.Value <> "" Then

Thank you very much Nori.