A
Andyd74
I am having trouble comparing a cell that contains a number against what
the user enters into a form cell. the code i have works for letters or a
combination of letters and numbers, but not numbers on their own??
I have set the variable as a String and the cells format as text, i
have also tried the cell format as general.
here is my code, i have highlight the bit where it assigns the value
from the sheet to the variable i want.
blnvalidCode = False
blnvalidCode2 = False
blnvalidCode3 = False
For j = 5 To 500
If staff_name = Worksheets("Project Codes & Staff
Names").Cells(j, 6).Value Then
For i = 5 To 500
blnvalidCode2 = True
If proj_code = Worksheets("Project Codes & Staff
Names").Cells(i, 4).Value Then
For k = 5 To 500
blnvalidCode = True
If staff_id = Worksheets("Project Codes & Staff
Names").Cells(k, 2) Then
****this is the part where it puts blank into staff_id and therefore
blnvalidCode3 stays as FALSE. ****
Call data_entry
blnvalidCode3 = True
Exit For
End If
Next k
If blnvalidCode3 = False Then
MsgBox (Worksheets("Project Codes & Staff
Names").Cells(k, 2))
MsgBox "Please enter a valid Staff ID", vbExclamation +
vbOKOnly, "Invalid Staff ID Error"
Worksheets("Record Names").Protect Password:="poker"
End If
Exit For
End If
Next i
If blnvalidCode = False Then
MsgBox "Please enter a valid Project Code",
vbExclamation + vbOKOnly, "Invalid Project Code Error"
Worksheets("Record Names").Protect Password:="poker"
End If
Exit For
End If
Next j
If blnvalidCode2 = False Then
MsgBox "Please enter a valid team member's name!",
vbExclamation + vbOKOnly, "Invalid Team Name Error"
Worksheets("Record Names").Protect Password:="poker"
End If
End If
End Sub
All help would be appreciated
Thanks
Andy
the user enters into a form cell. the code i have works for letters or a
combination of letters and numbers, but not numbers on their own??
I have set the variable as a String and the cells format as text, i
have also tried the cell format as general.
here is my code, i have highlight the bit where it assigns the value
from the sheet to the variable i want.
blnvalidCode = False
blnvalidCode2 = False
blnvalidCode3 = False
For j = 5 To 500
If staff_name = Worksheets("Project Codes & Staff
Names").Cells(j, 6).Value Then
For i = 5 To 500
blnvalidCode2 = True
If proj_code = Worksheets("Project Codes & Staff
Names").Cells(i, 4).Value Then
For k = 5 To 500
blnvalidCode = True
If staff_id = Worksheets("Project Codes & Staff
Names").Cells(k, 2) Then
****this is the part where it puts blank into staff_id and therefore
blnvalidCode3 stays as FALSE. ****
Call data_entry
blnvalidCode3 = True
Exit For
End If
Next k
If blnvalidCode3 = False Then
MsgBox (Worksheets("Project Codes & Staff
Names").Cells(k, 2))
MsgBox "Please enter a valid Staff ID", vbExclamation +
vbOKOnly, "Invalid Staff ID Error"
Worksheets("Record Names").Protect Password:="poker"
End If
Exit For
End If
Next i
If blnvalidCode = False Then
MsgBox "Please enter a valid Project Code",
vbExclamation + vbOKOnly, "Invalid Project Code Error"
Worksheets("Record Names").Protect Password:="poker"
End If
Exit For
End If
Next j
If blnvalidCode2 = False Then
MsgBox "Please enter a valid team member's name!",
vbExclamation + vbOKOnly, "Invalid Team Name Error"
Worksheets("Record Names").Protect Password:="poker"
End If
End If
End Sub
All help would be appreciated
Thanks
Andy