wrong outcome

[kelvin_9]

hello all buddies, whats problem with my code below here, how come it returns column I-M with a wrong sequence? (column A-H is correct & prefect)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DATE[/TD]
[TD]CELL PHONE[/TD]
[TD]FAX[/TD]
[TD]REGION[/TD]
[TD]EMAIL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]expect[/TD]
[TD]15-July-2005[/TD]
[TD]1234[/TD]
[TD]5678[/TD]
[TD]NY[/TD]
[TD]abc@hotmail.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]the outcome with below code[/TD]
[TD]NY[/TD]
[TD][/TD]
[TD]15-July-2005[/TD]
[TD]1234[/TD]
[TD]5678[/TD]
[TD]abc@hotmail.com[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thanks


Option Explicit
'Private variables
Dim cNum As Integer
Dim X As Integer


Dim nextrow As Range
'error handler
On Error GoTo errHandler:
'set the next row in the database
Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'check for values in the first 4 controls
For X = 1 To 4
If Me.Controls("Reg" & X).Value = "" Then
MsgBox "You must add all data"
Exit Sub
End If
Next
'check for duplicate payroll numbers
If WorksheetFunction.CountIf(Sheet2.Range("D:D"), Me.reg4.Value) > 0 Then
MsgBox "This staff member already exists"
Exit Sub
End If
'number of controls to loop through
cNum = 21
'add the data to the database
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'clear the controls
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Next
'sort the database
'Sortit
'error block
On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"

 

[Fluff]

Maybe some of your controls are in the wrong order.
For instance is the date entered into Reg9, or Reg12?

 

[kelvin_9]

Maybe some of your controls are in the wrong order.
For instance is the date entered into Reg9, or Reg12?

BRILLIANT & GENIUS Fluff
i'm sorry i was messed up the sequence of those textboxes

another issue about column header in a list box, i hope you can save me again please
1) i set "true" for columnheads in list box properties
2) i type "header" in row source, set my path(c1:h1) in name-box
3) i search my staff and display in listbox but outcome with error2147467259

it works prefect until i try this, how can i fix this then?

thanks again

 

[Fluff]

You can only use the column headers if you populate the listbox via RowSource.
However that severely limits what you can do with the listbox

 

[kelvin_9]

You can only use the column headers if you populate the listbox via RowSource.
However that severely limits what you can do with the listbox

which means I can use either show all in once and select the one I want in the list box or no header?

 

[Fluff]

That's right.
However you can always use labels above the listbox to simulate a header row

 

[kelvin_9]

That's right.
However you can always use labels above the listbox to simulate a header row

Got it, thanks so much

off topic, do you have any idea / understand on my other topic?
https://www.mrexcel.com/forum/excel-questions/1096802-sequence-macro-text-box.html

 

[Fluff]

You're welcome & thanks for the feedback

 

[kelvin_9]

You're welcome & thanks for the feedback

i have encounteranother error #91 , what's going wrong?

 

[Fluff]

I've absolutely no idea.
Remove you error handler & see what line of code fails