Hi there,

I am just coming to grips with VBA and Userforms and have been researching extensively on the Web for any existing code that I could use, but have been unsuccessful thus far.

I am in the process of developing an Excel 2010 Userform which will be used to record Incidents in Excel 2010.

The name of the workbook is SWIRL-SECINC Instrument.xlsm

The worksheet name where I am storing the data from the Userform is called IncidentData (sheet2). The Userform name is Security_Incident_Form

Each Incident has a unique number, eg: INC-25450, etc. (the unique number is not automatically generated via excel, this comes from an external source)

I have Multipages, combo boxes, etc

Some of the fields are:

Incident Number, Incident Date, Logged by, Address, Incident Type, What happened, Root Cause, Vehicle Information, Closing Comments, etc….  (there will be in excess of 200 fields/colums of data.

The problem is that not all the fields are completed in the initial entry … the userform will be updated at a later stage when all the details have been collected that the rest of the fields are then to be completed and the data saved to the same row that the initial data was entered overwriting ALL the columns of the spreadsheet for that unique Incident number).

I have started to code VBA to transfer the data into a worksheet named IncidentData but am wondering how to code to get it to check if the Incident number exists, and if Yes, then I want VBA to populate all the fields of the Userform to the incidentdata (sheet2) datasheet.

This is the code I currently have that I obtained from the web, and it did work, but for some reason it no longer works, I tried Dim eRow but then gives me errors ... it worked before without the Dim.

I am currently getting an error  on this line "  eRow = IncidentData.Cells(Row.Count, 1).End(x1Up).Offset(1, 0).Row   "

Private Sub CMDB_TransferToDataSheet_Click()
'the CMDB is the ActiveX Button.
       
'erow finds the next empty Row
'Once if it is empty, then it will go to the 1st empty row and add
'in the details from the particular field on the form into the datasheet.
        
 'Example:
 '1,2,3 etc below, are referring to which columns the data has to go
 'if the row doesn't contain any data
       
 eRow = IncidentData.Cells(Row.Count, 1).End(x1Up).Offset(1, 0).Row
 Cells(eRow, 1).Value = TXT_SecurityIncidentNo
 Cells(eRow, 2).Value = CMBX_Status
 Cells(eRow, 3).Value = CMBX_AllocatedTo
  'Cells(erow, 4) ... etc (in excess of 200 columns).Value =

End Sub

The aim of the transfer of data to the IncidentData sheet is, If there is no match for the unique incident number, then it needs to create a new entry on the next empty row of the incidentdata sheet.


Just as additional information, that may be useful ... I am developing the Userform at home on Windows 8 64bit PC, but at work I am currently using Windows XP, which is later going to be upgraded to windows 7 32bit.

Do you require any further information?

I thank you in advance for your time and effort.

Cheers,

1stButterfly