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


--
Andyd74
------------------------------------------------------------------------
Andyd74's Profile: http://www.excelforum.com/member.php...o&userid=34013
View this thread: http://www.excelforum.com/showthread...hreadid=541587

I think I understand your issue. Try this:

staff_id = CStr(Worksheets("Project Codes & Staff
Names").Cells(k, 2))

--
Hmm...they have the Internet on COMPUTERS now!


"Andyd74" wrote:


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


--
Andyd74
------------------------------------------------------------------------
Andyd74's Profile: http://www.excelforum.com/member.php...o&userid=34013
View this thread: http://www.excelforum.com/showthread...hreadid=541587