IF Function in VBA

[Mas]

Hi,

I have a userform with a text box
userorm caption is determind by where it is activated. In
sheet1 column g i have category whic is having A,B,C ,D
as values

userform caption will be from this category (A,B,C,D)

i want to check when i enter a number in textbox1 ehther
the number already exixts in sheet1 column A with the
same category as userform caption.

eg. if userform caption is A

and sheet1 column

column A column G

1 A
2 A
3 B
4 B
1 C
2 B
4 D

THE SAME NUMBER IS NOT REPEATED FOR THE SAME CATEGORY
if 1 is aready there in column A and column G is A
number 1 is not accepted for category A again

I have defind column G as category and Column A as
Receipt_No

i was usig a validation in my cell $C$7 =IF
(Category="A",COUNTIF(Receipt_No,$C$7),0)=0
to prevent duplicates.

now how i can get the same validation in a Textbox1 in my
userform?

Thanks..
MAS

 

[Dick Kusleika]

MAS

Try something like this

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim cell As Range

For Each cell In Sheet1.Range("Receipt_No").Cells
If cell.Value = Me.TextBox1.Text And _
cell.Offset(0, 1).Value = Me.Caption Then

MsgBox "Receipt/category of " & cell.Value & "/" & _
Me.Caption & " already exists"
Cancel = True
Exit For
End If
Next cell

End Sub