How about just showing the userform when they change exactly one cell?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("g18:g26")) Is Nothing Then Exit Sub
If LCase(Target.Value) < "y" Then Exit Sub
UserForm1.Show
End Sub



Leslieac wrote:

I have a column of cells (G18:G26) that the user can choose "Y" or "N"
directly in the cell. I need to have a macro triggered that opens a user
form (it is the same macro for all cells in that range), when they choose
"Y". I need to have the results of the user form printed on a unique line in
on a new worksheet tab. This way if they choose "Y" to more than one cell
(i.e. G18 & G19), I will get 2 sets of output data that don't overwrite each
other.

I have the user form created and working, but not for multiple cells. This
is what I have so far:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Belt Buff")

'specify an empty row in output sheet
iRow = 22

'copy the data to the database
With ws
.Cells(iRow, 12).Value = Me.txtHits.Value
.Cells(iRow, 13).Value = Me.txtTop.Value
.Cells(iRow, 14).Value = Me.txtSides.Value
End With

'clear the data
Me.txtHits.Value = ""
Me.txtTop.Value = ""
Me.txtSides.Value = ""

End Sub

Any help would be appreciated. Thanks.

--

Dave Peterson