PDA

View Full Version : Add sequential # to userform once saved



GTT
09-28-2016, 06:35 AM
Hi,

First time VBA attempt and I am struggling to get this one over the line.

I am looking for a solution whereby once the form I have created is "saved" a sequential number is added to the form and the spreadsheet in Column O. I would also like to know how I could then re-call the sequential # within the userform to edit and re-save information without it creating a new number.

Sample file attached.

Any help or advice would be really appreciated. 17198

Kenneth Hobs
09-28-2016, 01:13 PM
Welcome to the forum!

First off, you need to add an NCR Number that is sequential and unique. I don't know if you want a 0 prefixed number or how many places or just a Long number or even Integer. I would use Long since that is what row numbers are.

e.g.

Private Sub UserForm_Initialize()
TextBox1.Value = NextNCRNumber
End Sub


Private Function NextNCRNumber() As Long
NextNCRNumber = WorksheetFunction.Max(Worksheets("Sheet1").Range("O2", _
Worksheets("Sheet1").Range("O" & Rows.Count).End(xlUp))) + 1
End Function


For the Save button, you should probably overwrite data if the NCR Number is the same. IF that is the case, then a MsgBox() would need to allow the user to overwrite then or abort. To save to new row, it looks like you know how to do that. To overwrite, you would check for the NCR Number in a range Find method. If range is found, you can then get the Row number.

Before the Save is addressed, I would suggest that you think about your how to best maintain your project. Tag property is a good method keep your Column names stored in a control. Then you can tie the control's Tag property value to a column and do a find for the column in row 1. Of course you can make a two column array or two arrays to keep your controls and column matching scheme in sync. Or, just brute force strength it out...

GTT
10-03-2016, 04:58 AM
Thanks so much for your response. I have added the code to the Userform and it successfully display's a number "1" in the NCR Number text box. When I hit the save button the record saves but no information is added to the spreadsheet, it remains blank. I also don't understand how to increment this every time a new record is saved and also how to find an existing record to amend. Any help here would be sincerely appreciated. I have enclosed the workbook again.

Thanks17244

Kenneth Hobs
10-03-2016, 06:03 AM
I did not see it adding "blank" data.

I did not add a feature to fill the form based on something like the NCRnumber. Since you are showing Userform1 as vbModal, you could use Selection.Row to get the row number and then fill accordingly.

I changed the control name for Textbox1 to txtNCRnumber.

This shows how to find an NCR number and overwrite that row of data or not via a MsgBox or just add a new row if NCR number does not exist.

'Copy input values to sheet.
Private Sub cmdSaveNCR_Click()
Dim lRow As Long, ws As Worksheet, f As Range

Set ws = Worksheets("Sheet1")
With ws
lRow = .Cells(Rows.Count, "O").End(xlUp).Offset(1, 0).Row
Set f = .Range("O2", .Range("O" & Rows.Count).End(xlUp))
Set f = f.Find(What:=txtNCRnumber, after:=f.Cells(f.Rows.Count, f.Columns.Count), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not f Is Nothing Then
If MsgBox(txtNCRnumber & " exists, overwrite data?", vbYesNo, "Overwrite?") = vbNo Then
Exit Sub
Else
lRow = f.Row
End If
End If

.Cells(lRow, "A").Value = txtdate.Value
.Cells(lRow, "B").Value = txtJobNumber.Value
.Cells(lRow, "C").Value = txtRaisedBy.Value
.Cells(lRow, "D").Value = txtProjectManager.Value
.Cells(lRow, "E").Value = cboSeverity.Value
.Cells(lRow, "F").Value = txtCause.Value
.Cells(lRow, "G").Value = txtCorrectiveActions.Value
.Cells(lRow, "H").Value = txtPreventativeActions.Value
.Cells(lRow, "I").Value = txtTimeTakenMins.Value
.Cells(lRow, "J").Value = txtMaterialCost.Value
.Cells(lRow, "K").Value = txtTimeToReInspectMins.Value
.Cells(lRow, "L").Value = cboProblemCategory.Value
.Cells(lRow, "M").Value = txtTotalTime.Value
.Cells(lRow, "N").Value = obClosed.Value
.Cells(lRow, "O").Value = txtNCRnumber.Value
End With

Unload Me
UserForm1.Show vbModal
End Sub


Private Sub UserForm_Initialize()
txtNCRnumber.Value = NextNCRNumber
End Sub

GTT
10-03-2016, 06:53 AM
Thank you so much for the time you have taken so far to help me. I have added the code to the work sheet and it now works perfectly to overwrite the fields. The only functionality I am now missing is the ability to search for an existing NCR number to pre-populate the form with the data that already exists within the spreadsheet. I do not understand the point you make here ........
I did not add a feature to fill the form based on something like the NCRnumber. Since you are showing Userform1 as vbModal, you could use Selection.Row to get the row number and then fill accordingly.
Is this the point directly related to my missing feature?17246
Thanks Kenneth. Greg

Kenneth Hobs
10-03-2016, 07:19 AM
Yes.

I would just add a button called maybe Import. A MsgBox() or comment or just experience would tell the user to first select a row/record to Import to the Userform to "edit" data.

The Find() that I showed is then used to get the Row number of the selection. It is then a simple matter to reverse your filling of ranges from Controls to filling of Controls from ranges. e.g.

txtdate.Value = .Cells(lRow, "A").Text Note that I used the Text property of the range rather than the usual Value since this is a date field/column. Your custom format for that range is then used as the string value for that control.

You can pre-format the date column/field or make the code do it.

In your Module to show the userform, be sure to change modal to vbModal. This let's your user interact with the data directly or the userform while the userform is shown. Most would want vbModeless and use the userform to control adding and modifying the actual data. It just depends on how much flexibility you want to give your users and quite frankly, trust.

GTT
10-03-2016, 08:22 AM
Really struggling with this last part. Can't even get the debugger to quieten down. Thanks again for your help but I think I am going to have to try another work around.

Kenneth Hobs
10-03-2016, 09:05 AM
I meant vbModeless rather than vbModal. That let's your user's interact with the userform and the workbook. That is the easiest way. If you want to import by some other means such as finding the NCR number, the find routine that I showed you shows how that is found. Of course there are several other ways to do that as well. e.g. Show another userform with a listbox filled one or more fields/columns and all the rows/records.


Private Sub btnImport_Click()
Dim lRow As Long, ws As Worksheet, f As Range

If MsgBox("Existing userform data will be overwritten. Are you sure that you want to add data from the row selected?", _
vbYesNo, "Add Spreadsheet Data from Row Selected") = vbNo Then Exit Sub
ClearForm

Set ws = Worksheets("Sheet1")
With ws
lRow = Selection.Row

txtdate.Value = .Cells(lRow, "A").Value
txtJobNumber.Value = .Cells(lRow, "B").Value
txtRaisedBy.Value = .Cells(lRow, "C").Value
txtProjectManager.Value = .Cells(lRow, "D").Value
cboSeverity.Value = .Cells(lRow, "E").Value
txtCause.Value = .Cells(lRow, "F").Value
txtCorrectiveActions.Value = .Cells(lRow, "G").Value
txtPreventativeActions.Value = .Cells(lRow, "H").Value
txtTimeTakenMins.Value = .Cells(lRow, "I").Value
txtMaterialCost.Value = .Cells(lRow, "J").Value
txtTimeToReInspectMins.Value = .Cells(lRow, "K").Value
cboProblemCategory.Value = .Cells(lRow, "L").Value
txtTotalTime.Value = .Cells(lRow, "M").Value
obClosed.Value = .Cells(lRow, "N").Value
txtNCRnumber.Value = .Cells(lRow, "O").Value
End With
End Sub


Private Sub btnNewNCRno_Click()
If MsgBox("Existing userform data will be overwritten. Are you sure that you want to add new data?", _
vbYesNo, "Add New NCR Number") = vbYes Then
ClearForm
txtNCRnumber.Value = NextNCRNumber
End If
End Sub


Private Sub ClearForm()
'Clear input controls.
Me.txtdate.Value = ""
Me.txtJobNumber.Value = ""
Me.txtRaisedBy.Value = ""
Me.txtProjectManager.Value = ""
Me.cboSeverity.Value = ""
Me.txtCause.Value = ""
Me.txtCorrectiveActions.Value = ""
Me.txtPreventativeActions.Value = ""
Me.txtTimeTakenMins.Value = ""
Me.txtMaterialCost.Value = ""
Me.txtTimeToReInspectMins.Value = ""
Me.cboProblemCategory.Value = ""
Me.txtTotalTime.Value = ""
Me.obClosed.Value = ""
txtNCRnumber.Value = ""
End Sub


'Copy input values to sheet.
Private Sub cmdSaveNCR_Click()
Dim lRow As Long, ws As Worksheet, f As Range

Set ws = Worksheets("Sheet1")
With ws
lRow = .Cells(Rows.Count, "O").End(xlUp).Offset(1, 0).Row
Set f = .Range("O2", .Range("O" & Rows.Count).End(xlUp))
Set f = f.Find(What:=txtNCRnumber, after:=f.Cells(f.Rows.Count, f.Columns.Count), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not f Is Nothing Then
If MsgBox(txtNCRnumber & " exists, overwrite data?", vbYesNo, "Overwrite?") = vbNo Then
Exit Sub
Else
lRow = f.Row
End If
End If

.Cells(lRow, "A").Value = txtdate.Value
.Cells(lRow, "B").Value = txtJobNumber.Value
.Cells(lRow, "C").Value = txtRaisedBy.Value
.Cells(lRow, "D").Value = txtProjectManager.Value
.Cells(lRow, "E").Value = cboSeverity.Value
.Cells(lRow, "F").Value = txtCause.Value
.Cells(lRow, "G").Value = txtCorrectiveActions.Value
.Cells(lRow, "H").Value = txtPreventativeActions.Value
.Cells(lRow, "I").Value = txtTimeTakenMins.Value
.Cells(lRow, "J").Value = txtMaterialCost.Value
.Cells(lRow, "K").Value = txtTimeToReInspectMins.Value
.Cells(lRow, "L").Value = cboProblemCategory.Value
.Cells(lRow, "M").Value = txtTotalTime.Value
.Cells(lRow, "N").Value = obClosed.Value
.Cells(lRow, "O").Value = txtNCRnumber.Value
End With

Unload Me
UserForm1.Show vbModeless
End Sub

GTT
10-04-2016, 12:42 AM
I cannot begin to thank you enough for helping me. You are a credit to this forum. Regards, Greg

GTT
10-04-2016, 04:09 AM
17257Hmm, It only seems to import the first NCR # 1 - When I try to import other records it only imports NCR # 1.

Can you also confirm how I would change the txtProjectManager in row D into a Command Box entitled "DepartmentSupp". I have changed all of the code to cboDepartmentSupp

Private Sub cboDepartmentSupp _DropButtonClick()
'Populate control.
Me.cboDepartmentSupp.AddItem "A SCAFFOLDING LIMITED"
Me.cboDepartmentSupp.AddItem "A.D&B.J Boote Transport Ltd"
Me.cboDepartmentSupp.AddItem "A.G.M Chauffeur Services"
Me.cboDepartmentSupp.AddItem "Aalco"
Me.cboDepartmentSupp.AddItem "Aardvark Promotions Ltd"
Me.cboDepartmentSupp.AddItem "Abacad Ltd"
Me.cboDepartmentSupp.AddItem "Abacus Valves International Limited"
Me.cboDepartmentSupp.AddItem "Able Engineering Ltd"
Me.cboDepartmentSupp.AddItem "Abran Tools"
Me.cboDepartmentSupp.AddItem "Achilles Information Ltd"
Me.cboDepartmentSupp.AddItem "ADP - Automatic Data Processing Limited"
Me.cboDepartmentSupp.AddItem "ADT Fire and Security PLC"
Me.cboDepartmentSupp.AddItem "Aeroflex Hose & Engineering Ltd"
Me.cboDepartmentSupp.AddItem "Afford Rent-a-Car"
Me.cboDepartmentSupp.AddItem "AGT Thermotechnik GmbH"
Me.cboDepartmentSupp.AddItem "Air Products Ltd"
Me.cboDepartmentSupp.AddItem "Albright IP Limited"
Me.cboDepartmentSupp.AddItem "Alco Valves Group Ltd"
Me.cboDepartmentSupp.AddItem "Alexander Comley Limited"
Me.cboDepartmentSupp.AddItem "Allenco Worldwide Ltd"
Me.cboDepartmentSupp.AddItem "Allied International Ltd"
Me.cboDepartmentSupp.AddItem "Alpha Moisture Systems"
Me.cboDepartmentSupp.AddItem "Alpha Tyres Ltd"
Me.cboDepartmentSupp.AddItem "AMCS Ltd"
Me.cboDepartmentSupp.AddItem "Amtech Group Ltd"
Me.cboDepartmentSupp.AddItem "Anderton Concrete Products Ltd"
Me.cboDepartmentSupp.AddItem "Anixter Ltd"
Me.cboDepartmentSupp.AddItem "Antifriction Components Ltd"
Me.cboDepartmentSupp.AddItem "Apollo Electronics Corp Ltd"
Me.cboDepartmentSupp.AddItem "Apparelmaster UK"
Me.cboDepartmentSupp.AddItem "Arco Limited"
Me.cboDepartmentSupp.AddItem "Asecos Ltd"
Me.cboDepartmentSupp.AddItem "Ashtead Plant Hire Co. Ltd"
Me.cboDepartmentSupp.AddItem "Ashton Seals"
Me.cboDepartmentSupp.AddItem "Autoglass"
Me.cboDepartmentSupp.AddItem "B&E"
Me.cboDepartmentSupp.AddItem "Baker Tilly"
Me.cboDepartmentSupp.AddItem "Beka Associates LTD"
Me.cboDepartmentSupp.AddItem "Benning Power Electronics Ltd"
Me.cboDepartmentSupp.AddItem "Bentley Insurance Service"
Me.cboDepartmentSupp.AddItem "BOC Ltd"
Me.cboDepartmentSupp.AddItem "Bohler-Uddeholm Specialty Metals"
Me.cboDepartmentSupp.AddItem "BPX Electro Mechanical Co Ltd"
Me.cboDepartmentSupp.AddItem "Brady Corp Ltd"
Me.cboDepartmentSupp.AddItem "Brandz Ltd"
Me.cboDepartmentSupp.AddItem "Bristol Street Motors"
Me.cboDepartmentSupp.AddItem "BS & B Safety Systems Ltd"
Me.cboDepartmentSupp.AddItem "BSS UK Limited"
Me.cboDepartmentSupp.AddItem "Buildbase Ltd"
Me.cboDepartmentSupp.AddItem "Burkert Contromatic Ltd"
Me.cboDepartmentSupp.AddItem "Burroughs Stewart Associates Ltd"
Me.cboDepartmentSupp.AddItem "Bywell Spring & Pressings Ltd"
Me.cboDepartmentSupp.AddItem "Cable Services"
Me.cboDepartmentSupp.AddItem "Cable Systems Ltd"
Me.cboDepartmentSupp.AddItem "CADline Ltd"
Me.cboDepartmentSupp.AddItem "Calgaz"
Me.cboDepartmentSupp.AddItem "Cambridge Fluid Systems Ltd"
Me.cboDepartmentSupp.AddItem "Capes Limited"
Me.cboDepartmentSupp.AddItem "Carpenter & Paterson Ltd"
Me.cboDepartmentSupp.AddItem "CBISS Ltd"
Me.cboDepartmentSupp.AddItem "CCS Media Limited"
Me.cboDepartmentSupp.AddItem "Chaffinch Document"
Me.cboDepartmentSupp.AddItem "Chamois Metrology"
Me.cboDepartmentSupp.AddItem "Chase Engineering Ltd"
Me.cboDepartmentSupp.AddItem "Chell Eng Co Ltd"
Me.cboDepartmentSupp.AddItem "City Electrical Factors Ltd"
Me.cboDepartmentSupp.AddItem "Clena Supplies"
Me.cboDepartmentSupp.AddItem "Cleveland Cable Co.Ltd"
Me.cboDepartmentSupp.AddItem "Comtec Cable Accessories Ltd"
Me.cboDepartmentSupp.AddItem "Concordia Int'l Forwarding Ltd"
Me.cboDepartmentSupp.AddItem "Control Instruments Corporation"
Me.cboDepartmentSupp.AddItem "Control Systems Maintenance Ltd"
Me.cboDepartmentSupp.AddItem "CP e (Speciality Components) Ltd"
Me.cboDepartmentSupp.AddItem "Crawford Scientific Ltd"
Me.cboDepartmentSupp.AddItem "Creative Copy n Colour"
Me.cboDepartmentSupp.AddItem "Cromwell Group (Holdings) Ltd"
Me.cboDepartmentSupp.AddItem "Crowcon Detection Instruments Ltd"
Me.cboDepartmentSupp.AddItem "CSA International"
Me.cboDepartmentSupp.AddItem "De Flow Ltd"
Me.cboDepartmentSupp.AddItem "DI UK Ltd (Dresser)"
Me.cboDepartmentSupp.AddItem "DK Group Packaging Limited"
Me.cboDepartmentSupp.AddItem "Dockweiler UK Ltd"
Me.cboDepartmentSupp.AddItem "DONG Energy Sales UK Ltd"
Me.cboDepartmentSupp.AddItem "Double M plant Hire"
Me.cboDepartmentSupp.AddItem "Draeger Safety Ltd"
Me.cboDepartmentSupp.AddItem "Dragon Filtration"
Me.cboDepartmentSupp.AddItem "Eastern Seals Uk Ltd"
Me.cboDepartmentSupp.AddItem "Edmundson Electrical Ltd"
Me.cboDepartmentSupp.AddItem "Effectech Ltd"
Me.cboDepartmentSupp.AddItem "Electrix [International] Ltd"
Me.cboDepartmentSupp.AddItem "Electro-Wind Ltd"
Me.cboDepartmentSupp.AddItem "Elliott Group Limited"
Me.cboDepartmentSupp.AddItem "Elster Metering Ltd"
Me.cboDepartmentSupp.AddItem "Embassy Freight Services (Midlands) Ltd"
Me.cboDepartmentSupp.AddItem "Emerson Process Management LTD"
Me.cboDepartmentSupp.AddItem "EnDet Ltd"
Me.cboDepartmentSupp.AddItem "Envirosoft Ltd"
Me.cboDepartmentSupp.AddItem "ESS Safeforce"
Me.cboDepartmentSupp.AddItem "ETS Cable Components"
Me.cboDepartmentSupp.AddItem "Euro (Fluid) Power Ltd"
Me.cboDepartmentSupp.AddItem "Euro Energy Resources Ltd"
Me.cboDepartmentSupp.AddItem "EUSR - Energy & Utility Skills"
Me.cboDepartmentSupp.AddItem "Ex Heat Industrial Ltd"
Me.cboDepartmentSupp.AddItem "Ex-i Flow Measurement Ltd"
Me.cboDepartmentSupp.AddItem "Express Instrument Hire"
Me.cboDepartmentSupp.AddItem "Extronics Ltd"
Me.cboDepartmentSupp.AddItem "F W B Products Ltd"
Me.cboDepartmentSupp.AddItem "Facet Industrial UK Ltd"
Me.cboDepartmentSupp.AddItem "Farnell Electronics Ltd"
Me.cboDepartmentSupp.AddItem "Fast Gaskets And Parts LTD"
Me.cboDepartmentSupp.AddItem "Federal Express Europe INC"
Me.cboDepartmentSupp.AddItem "Fine Controls (UK) Ltd"
Me.cboDepartmentSupp.AddItem "Flashbay Ltd"
Me.cboDepartmentSupp.AddItem "Fluid Controls Ltd"
Me.cboDepartmentSupp.AddItem "Four-Tec Fabrications Ltd"
Me.cboDepartmentSupp.AddItem "FTI Ltd"
Me.cboDepartmentSupp.AddItem "G & P Batteries Ltd"
Me.cboDepartmentSupp.AddItem "Gardner Denver Thomas GmbH"
Me.cboDepartmentSupp.AddItem "Garic Ltd"
Me.cboDepartmentSupp.AddItem "Gas Control Equipment LTD"
Me.cboDepartmentSupp.AddItem "Gas-Arc Welding Supplies"
Me.cboDepartmentSupp.AddItem "Gazprom Energy (U14)"
Me.cboDepartmentSupp.AddItem "GB Copier Systems Ltd"
Me.cboDepartmentSupp.AddItem "GDS Technologies Ltd"
Me.cboDepartmentSupp.AddItem "GE Inteligent Platforms GmbH"
Me.cboDepartmentSupp.AddItem "GL Industrial Services UK Ltd"
Me.cboDepartmentSupp.AddItem "H&b Sensors"
Me.cboDepartmentSupp.AddItem "H&F Lift Trucks"
Me.cboDepartmentSupp.AddItem "H. E. Butters & Co"
Me.cboDepartmentSupp.AddItem "H. W. Stockley & Son"
Me.cboDepartmentSupp.AddItem "Halstone Mobile Phones"
Me.cboDepartmentSupp.AddItem "Hamilton International Services Ltd"
Me.cboDepartmentSupp.AddItem "Harding Electrical"
Me.cboDepartmentSupp.AddItem "Henry Thacker Limited"
Me.cboDepartmentSupp.AddItem "Hissey's Garage"
Me.cboDepartmentSupp.AddItem "Hobson Health Limited"
Me.cboDepartmentSupp.AddItem "HSS Hire Service"
Me.cboDepartmentSupp.AddItem "Hystackers Ltd"
Me.cboDepartmentSupp.AddItem "I & P Lifting Gear Ltd"
Me.cboDepartmentSupp.AddItem "Ian Smith (Stationery) LTD"
Me.cboDepartmentSupp.AddItem "Impulse Corporation Ltd"
Me.cboDepartmentSupp.AddItem "IMV Inventomatic Victron UK Ltd"
Me.cboDepartmentSupp.AddItem "Industrial Ancillaries Ltd"
Me.cboDepartmentSupp.AddItem "Ingenion Design Ltd"
Me.cboDepartmentSupp.AddItem "Interlink Express Parcels"
Me.cboDepartmentSupp.AddItem "International Plastic Systems Ltd (IPS)"
Me.cboDepartmentSupp.AddItem "Intertec Instrumentation Ltd"
Me.cboDepartmentSupp.AddItem "Intertronics"
Me.cboDepartmentSupp.AddItem "Jactron Ltd"
Me.cboDepartmentSupp.AddItem "James Walker Ltd"
Me.cboDepartmentSupp.AddItem "JCE (Europe) Ltd"
Me.cboDepartmentSupp.AddItem "JCT Analysentechnik GmbH"
Me.cboDepartmentSupp.AddItem "JDE Steelwork Solutions"
Me.cboDepartmentSupp.AddItem "John Godden"
Me.cboDepartmentSupp.AddItem "KC Controls UK LLP"
Me.cboDepartmentSupp.AddItem "Klinger Ltd"
Me.cboDepartmentSupp.AddItem "KMF (Precision Sheet Metal) Ltd"
Me.cboDepartmentSupp.AddItem "KNF Neuberger UK Ltd"
Me.cboDepartmentSupp.AddItem "Knightstor Ltd"
Me.cboDepartmentSupp.AddItem "Krohne Limited"
Me.cboDepartmentSupp.AddItem "LAA UK Ltd"
Me.cboDepartmentSupp.AddItem "Lawrence Davis Associates in Design"
Me.cboDepartmentSupp.AddItem "Lawton Tools"
Me.cboDepartmentSupp.AddItem "Lenox Laser"
Me.cboDepartmentSupp.AddItem "Leser UK Ltd"
Me.cboDepartmentSupp.AddItem "Lockwell Electrical Distributors Ltd"
Me.cboDepartmentSupp.AddItem "London Fan Company"
Me.cboDepartmentSupp.AddItem "London Fluid System Technologies Ltd"
Me.cboDepartmentSupp.AddItem "M E D C"
Me.cboDepartmentSupp.AddItem "M&C Tech Group Germany GmbH"
Me.cboDepartmentSupp.AddItem "Manchester Fluid System Technologies Ltd"
Me.cboDepartmentSupp.AddItem "Mark Prestt Services"
Me.cboDepartmentSupp.AddItem "Matthew's Engineering (Stoke) Ltd"
Me.cboDepartmentSupp.AddItem "Measurement Technology Ltd"
Me.cboDepartmentSupp.AddItem "Merinox Ltd"
Me.cboDepartmentSupp.AddItem "Midsteel Flanges and Fittings Ltd"
Me.cboDepartmentSupp.AddItem "Millstream Associates Ltd"
Me.cboDepartmentSupp.AddItem "Milton Roy Company"
Me.cboDepartmentSupp.AddItem "Modern Engraving Ltd"
Me.cboDepartmentSupp.AddItem "Moisture Control & Measurement Ltd"
Me.cboDepartmentSupp.AddItem "Moore Industries - Europe Inc"
Me.cboDepartmentSupp.AddItem "Moorland Contract Cleaning Ltd"
Me.cboDepartmentSupp.AddItem "MPB Industries Ltd"
Me.cboDepartmentSupp.AddItem "Mr Box Ltd"
Me.cboDepartmentSupp.AddItem "MTS Sensor Technologie GmbH & Co. KG"
Me.cboDepartmentSupp.AddItem "M-Way Vehicle Rentals Ltd"
Me.cboDepartmentSupp.AddItem "National Grid"
Me.cboDepartmentSupp.AddItem "NECS Cleaning & Facilities Management"
Me.cboDepartmentSupp.AddItem "Nixon Hire"
Me.cboDepartmentSupp.AddItem "North Staffs Fire Ltd"
Me.cboDepartmentSupp.AddItem "NQA"
Me.cboDepartmentSupp.AddItem "OEM Automatic Ltd"
Me.cboDepartmentSupp.AddItem "Opta Periph"
Me.cboDepartmentSupp.AddItem "Orbital Drawing Office"
Me.cboDepartmentSupp.AddItem "Orbital Engineering"
Me.cboDepartmentSupp.AddItem "Orbital Infrastructure"
Me.cboDepartmentSupp.AddItem "Orbital Logistics"
Me.cboDepartmentSupp.AddItem "Orbital Manufacturing"
Me.cboDepartmentSupp.AddItem "Orbital Procurement"
Me.cboDepartmentSupp.AddItem "Orbital Project Management"
Me.cboDepartmentSupp.AddItem "Orbital QHSE"
Me.cboDepartmentSupp.AddItem "Orbital Sales"
Me.cboDepartmentSupp.AddItem "Orbital Service"
Me.cboDepartmentSupp.AddItem "Orbital Site Team"
Me.cboDepartmentSupp.AddItem "Orbital Software"
Me.cboDepartmentSupp.AddItem "Pall Europe Ltd"
Me.cboDepartmentSupp.AddItem "Pentair Technical Solutions UK Limited"
Me.cboDepartmentSupp.AddItem "Pepperl & FuchsGB Ltd"
Me.cboDepartmentSupp.AddItem "Pitney Bowes Plc"
Me.cboDepartmentSupp.AddItem "Plus Display Limited"
Me.cboDepartmentSupp.AddItem "Polyflon Technology Ltd"
Me.cboDepartmentSupp.AddItem "Portable Offices (Hire) Ltd"
Me.cboDepartmentSupp.AddItem "Portakabin Ltd"
Me.cboDepartmentSupp.AddItem "Powersolve Electronics Ltd"
Me.cboDepartmentSupp.AddItem "PR Electronics Ltd"
Me.cboDepartmentSupp.AddItem "Premier Electrical Wholesalers Ltd"
Me.cboDepartmentSupp.AddItem "Pressure Tech Ltd"
Me.cboDepartmentSupp.AddItem "Pressure Vacuum Level Ltd"
Me.cboDepartmentSupp.AddItem "Pritchard Tyrite Limited"
Me.cboDepartmentSupp.AddItem "Process Control Equipment Ltd
Me.cboDepartmentSupp.AddItem "Process Valve Solutions Ltd"
Me.cboDepartmentSupp.AddItem "Pro-Tect GRP Enclosures Ltd"
Me.cboDepartmentSupp.AddItem "R S Components Ltd"
Me.cboDepartmentSupp.AddItem "R.Stahl Limited"
Me.cboDepartmentSupp.AddItem "Rayden Engineering Ltd"
Me.cboDepartmentSupp.AddItem "Rebus Training Ld"
Me.cboDepartmentSupp.AddItem "Red Industries Ltd"
Me.cboDepartmentSupp.AddItem "Rhopoint Metrology Limited"
Me.cboDepartmentSupp.AddItem "Robinson Brothers Limited"
Me.cboDepartmentSupp.AddItem "Robinson Wire Cloth Ltd"
Me.cboDepartmentSupp.AddItem "Rotarex Equipment"
Me.cboDepartmentSupp.AddItem "Routeco PLC"
Me.cboDepartmentSupp.AddItem "Rowan House Ltd"
Me.cboDepartmentSupp.AddItem "Rowe Precision & General Engineers Ltd"
Me.cboDepartmentSupp.AddItem "Sabit Ltd"
Me.cboDepartmentSupp.AddItem "Sabre Instrument Valves"
Me.cboDepartmentSupp.AddItem "Sandford Freight (UK) Ltd"
Me.cboDepartmentSupp.AddItem "Sandvik Materials Technology EMEA AB"
Me.cboDepartmentSupp.AddItem "Scattergood & Johnson Ltd"
Me.cboDepartmentSupp.AddItem "Schramm Gmbh"
Me.cboDepartmentSupp.AddItem "Scott Pallets"
Me.cboDepartmentSupp.AddItem "Seetru Limited"
Me.cboDepartmentSupp.AddItem "Semaphore Systems LTD"
Me.cboDepartmentSupp.AddItem "SES-Sterling Ltd"
Me.cboDepartmentSupp.AddItem "SES-Survey Express Services"
Me.cboDepartmentSupp.AddItem "Seton"
Me.cboDepartmentSupp.AddItem "SGS Baseefa Limited"
Me.cboDepartmentSupp.AddItem "SICK (UK) Limited"
Me.cboDepartmentSupp.AddItem "Siemens plc (Sterling ac)"
Me.cboDepartmentSupp.AddItem "Signability (Creative) Ltd"
Me.cboDepartmentSupp.AddItem "Simplex Solutions Ltd"
Me.cboDepartmentSupp.AddItem "SP Services UK Ltd"
Me.cboDepartmentSupp.AddItem "Spectron Gas Control Systems"
Me.cboDepartmentSupp.AddItem "Speedy Asset Services Ltd"
Me.cboDepartmentSupp.AddItem "SPI (Materials) Limited"
Me.cboDepartmentSupp.AddItem "Stainless Fabrications Ltd"
Me.cboDepartmentSupp.AddItem "Status Instruments LTD"
Me.cboDepartmentSupp.AddItem "Steve Foster Crane Hire"
Me.cboDepartmentSupp.AddItem "Stewart-Buchanan Gauges"
Me.cboDepartmentSupp.AddItem "SWALEC"
Me.cboDepartmentSupp.AddItem "Swift Business Solutions Ltd"
Me.cboDepartmentSupp.AddItem "Systech Instruments Limited"
Me.cboDepartmentSupp.AddItem "System Devices UK Ltd"
Me.cboDepartmentSupp.AddItem "Tamo LTD"
Me.cboDepartmentSupp.AddItem "TD Thermal Ltd"
Me.cboDepartmentSupp.AddItem "Technica Ltd"
Me.cboDepartmentSupp.AddItem "Thames Restek UK Limited"
Me.cboDepartmentSupp.AddItem "The Valve Alliance Limited"
Me.cboDepartmentSupp.AddItem "Thermon UK Ltd"
Me.cboDepartmentSupp.AddItem "Thomas & Betts Netherlands BV"
Me.cboDepartmentSupp.AddItem "Thomas Telford Training Ltd"
Me.cboDepartmentSupp.AddItem "Tony Hamer & Sons Ltd"
Me.cboDepartmentSupp.AddItem "Total Access UK Ltd (Training)"
Me.cboDepartmentSupp.AddItem "Travis Perkins Trading Company Limited"
Me.cboDepartmentSupp.AddItem "Trigg Engraving Ltd"
Me.cboDepartmentSupp.AddItem "Tulway Engineering Ltd"
Me.cboDepartmentSupp.AddItem "Ultra Precision Products"
Me.cboDepartmentSupp.AddItem "Veolia ES UK Limited"
Me.cboDepartmentSupp.AddItem "Videk LTD"
Me.cboDepartmentSupp.AddItem "Viking Office Supplies Ltd (Bibby Finance)"
Me.cboDepartmentSupp.AddItem "Vision 33"
Me.cboDepartmentSupp.AddItem "Voice 2 Voice Ltd"
Me.cboDepartmentSupp.AddItem "Vulcan Industrial Fasteners Ltd"
Me.cboDepartmentSupp.AddItem "Watlow Limited"
Me.cboDepartmentSupp.AddItem "Westek Technology Ltd"
Me.cboDepartmentSupp.AddItem "Wika Instruments Ltd"
Me.cboDepartmentSupp.AddItem "Williams Mellard & Sons Limited"
Me.cboDepartmentSupp.AddItem "Withers & Rogers LLP USA"
Me.cboDepartmentSupp.AddItem "Workwear Express Ltd"
Me.cboDepartmentSupp.AddItem "WT Lynn Ltd"
Me.cboDepartmentSupp.AddItem "Yokogawa UK Ltd"
Me.cboDepartmentSupp.AddItem "Zurich Risk Services"
Me.cboDepartmentSupp.AddItem "ZygologySystems Limited"

Kenneth Hobs
10-04-2016, 05:08 AM
For the first, it works for me. As I said, before you click the Import button, click the row that you want to import on Sheet1. To import a different row, click that row and then the Import button.

For the second, I think that you want to replace one control with another? Just delete the one and add the other.

Of course you can add one item at a time if you like. If you have that data in a range, all of it could be added in one line of code. In any case, I would add it in the Userform's Initialize event to fill the control. If the fill data is static, you can simply set the range in the RowSource property for the control. e.g. Sheet2!A1:A20

I don't know what you mean by row D and a Command Box. Maybe column "D" and Command Button control? Of course your additem code would be a Combobox control. Add it from the Toolbox. If not shown, select the View menu and then toolbox. Since you renamed all of the controls, I assume that you know how to add a control, select the control, and then change the name in the Properties.

GTT
10-04-2016, 06:23 AM
I think it's done! Thanks again - you really have been a great help to me.