With comments...
Option Explicit
Private Sub CommandButton1_Click()
'cancel button
'gets rid of the userform
Unload Me
End Sub
Private Sub CommandButton2_Click()
'ok button
'declare some variables
'Long's are integers ..., -3, -2, -1, 0, 1, 2, 3, ...
'booleans are True/False
'ranges are cells or groups of cells on a worksheet
'Variant can hold anything--in this case, it's going to hold a number or
' an error.
Dim iCtr As Long
Dim ErrorFound As Boolean
Dim DestCell As Range
Dim myFormula As String
Dim LastRow As Long
Dim FirstRow As Long
Dim Resp As Long
Dim res As Variant
'check to make sure each of the textboxes has something in them
ErrorFound = False
For iCtr = 1 To 7
With Me.Controls("textbox" & iCtr)
If .Value = "" Then
ErrorFound = True
.SetFocus
Exit For
End If
End With
Next iCtr
If ErrorFound Then
'one of the textboxes is empty, so
'do nothing
Else
'the data has to go somewhere. I chose Sheet1.
With Worksheets("sheet1")
'Avoid row 1 (when checking for duplicates--so start with row 2)
FirstRow = 2 'headers in row 1??
'.end(xlup) is like going to A65536 and hitting the End key
'and then hitting the up arrow to find the last used cell in that
'column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'this section tries to build that long formula in the first post
myFormula = ""
For iCtr = 1 To 7
myFormula = myFormula & "*(" & .Range(.Cells(FirstRow, iCtr), _
.Cells(LastRow, iCtr)).Address & "=" & Chr(34) _
& Me.Controls("textbox" & iCtr) & Chr(34) & ")"
Next iCtr
myFormula = Mid(myFormula, 2)
myFormula = "match(1,(" & myFormula & "),0)"
'what cell should get the value from Textbox1.
'find that last used row in column A and come down one row and
'plop it there
Set DestCell = .Cells(LastRow + 1, "A")
res = .Evaluate(myFormula)
If IsError(res) Then
'if that big old formula returns an error, the combination of 7
'entries didn't exist in the worksheet.
'so just call another routine, but tell it where to write the
'values. Since the values could be added if the user has
'duplicated an existing row, it calls a common routine.
Call AddValuesToSheet(DestCell)
Else
'if res is not an error, then it's a duplicate
'find out if the user wants to add it anyway.
Resp = MsgBox(Prompt:="This record already exists in row: " _
& res + FirstRow - 1 & vbLf & _
"Want to add it anyway?", Buttons:=vbYesNo)
If Resp = vbYes Then
'if they clicked the yes button
Call AddValuesToSheet(DestCell)
Else
'do nothing else
End If
End If
End With
End If
End Sub
'this is the common routine
Sub AddValuesToSheet(DestCell As Range)
Dim iCtr As Long
'seven boxes, seven columns, 7 textboxes
For iCtr = 1 To 7
'.offset(x,y) says to go down x, over y
'so .offset(0, ictr-1) says to stay on the same row (0)
'and over 0, 1,2,...,6 columns
DestCell.Offset(0, iCtr - 1).Value _
= Me.Controls("textbox" & iCtr).Value
'and clear the textbox when we're done.
Me.Controls("textbox" & iCtr).Value = ""
Next iCtr
End Sub
Debra Dalgleish has a big list of books for excel at:
http://www.contextures.com/xlbooks.html
John Walkenbach's is a nice one to start with.
10="d")*(E2:E10="e")