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.

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

Thank You. That brings up the user form when those cells are choosen. I
have another question. My spreadsheet is constructing a finished part that
can have A - I components, so that is why they can answer "Y" to 9 choices
(g18:g26). Each component will have different dimensions, which is what the
User Form asks them to fill out. Currently, the user form is writing the
data they enter to row22, columns 12, 13, & 14. How can I increment this so
that if they enter more than one set of data, it will write to the next row
(i.e. row 23, row 24, etc.). Is this possible?

Thanks, Leslie

"Dave Peterson" wrote:

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

I'm not sure if this is a good fit, but it might give you an idea:

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

With ws
irow = .cells(.rows.count,12).end(xlup).row + 1
.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

It uses column 12 to determine the next cell. You can change that to whatever
column always has data--or you could make sure that me.txthits.value is always
different from "" before you write to that cell. (That way it's always used.)



Leslieac wrote:

Thank You. That brings up the user form when those cells are choosen. I
have another question. My spreadsheet is constructing a finished part that
can have A - I components, so that is why they can answer "Y" to 9 choices
(g18:g26). Each component will have different dimensions, which is what the
User Form asks them to fill out. Currently, the user form is writing the
data they enter to row22, columns 12, 13, & 14. How can I increment this so
that if they enter more than one set of data, it will write to the next row
(i.e. row 23, row 24, etc.). Is this possible?

Thanks, Leslie

"Dave Peterson" wrote:

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


--

Dave Peterson