software

I have a column of cells (G18:G26) that the user can choose quot;Yquot; or quot;Nquot;
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
quot;Yquot;. 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 quot;Yquot; to more than one cell
(i.e. G18 amp; 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(quot;Belt Buffquot;)

'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 = quot;quot;
Me.txtTop.Value = quot;quot;
Me.txtSides.Value = quot;quot;

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 gt; 1 Then Exit Sub
If Intersect(Target, Me.Range(quot;g18:g26quot;)) Is Nothing Then Exit Sub
If LCase(Target.Value) lt;gt; quot;yquot; Then Exit Sub
UserForm1.Show
End Sub
Leslieac wrote:
gt;
gt; I have a column of cells (G18:G26) that the user can choose quot;Yquot; or quot;Nquot;
gt; directly in the cell. I need to have a macro triggered that opens a user
gt; form (it is the same macro for all cells in that range), when they choose
gt; quot;Yquot;. I need to have the results of the user form printed on a unique line in
gt; on a new worksheet tab. This way if they choose quot;Yquot; to more than one cell
gt; (i.e. G18 amp; G19), I will get 2 sets of output data that don't overwrite each
gt; other.
gt;
gt; I have the user form created and working, but not for multiple cells. This
gt; is what I have so far:
gt;
gt; Private Sub cmdAdd_Click()
gt; Dim iRow As Long
gt; Dim ws As Worksheet
gt; Set ws = Worksheets(quot;Belt Buffquot;)
gt;
gt; 'specify an empty row in output sheet
gt; iRow = 22
gt;
gt; 'copy the data to the database
gt; With ws
gt; .Cells(iRow, 12).Value = Me.txtHits.Value
gt; .Cells(iRow, 13).Value = Me.txtTop.Value
gt; .Cells(iRow, 14).Value = Me.txtSides.Value
gt; End With
gt;
gt; 'clear the data
gt; Me.txtHits.Value = quot;quot;
gt; Me.txtTop.Value = quot;quot;
gt; Me.txtSides.Value = quot;quot;
gt;
gt; End Sub
gt;
gt; 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 quot;Yquot; 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, amp; 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

quot;Dave Petersonquot; wrote:

gt; How about just showing the userform when they change exactly one cell?
gt;
gt; Option Explicit
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; If Intersect(Target, Me.Range(quot;g18:g26quot;)) Is Nothing Then Exit Sub
gt; If LCase(Target.Value) lt;gt; quot;yquot; Then Exit Sub
gt; UserForm1.Show
gt; End Sub
gt;
gt;
gt;
gt; Leslieac wrote:
gt; gt;
gt; gt; I have a column of cells (G18:G26) that the user can choose quot;Yquot; or quot;Nquot;
gt; gt; directly in the cell. I need to have a macro triggered that opens a user
gt; gt; form (it is the same macro for all cells in that range), when they choose
gt; gt; quot;Yquot;. I need to have the results of the user form printed on a unique line in
gt; gt; on a new worksheet tab. This way if they choose quot;Yquot; to more than one cell
gt; gt; (i.e. G18 amp; G19), I will get 2 sets of output data that don't overwrite each
gt; gt; other.
gt; gt;
gt; gt; I have the user form created and working, but not for multiple cells. This
gt; gt; is what I have so far:
gt; gt;
gt; gt; Private Sub cmdAdd_Click()
gt; gt; Dim iRow As Long
gt; gt; Dim ws As Worksheet
gt; gt; Set ws = Worksheets(quot;Belt Buffquot;)
gt; gt;
gt; gt; 'specify an empty row in output sheet
gt; gt; iRow = 22
gt; gt;
gt; gt; 'copy the data to the database
gt; gt; With ws
gt; gt; .Cells(iRow, 12).Value = Me.txtHits.Value
gt; gt; .Cells(iRow, 13).Value = Me.txtTop.Value
gt; gt; .Cells(iRow, 14).Value = Me.txtSides.Value
gt; gt; End With
gt; gt;
gt; gt; 'clear the data
gt; gt; Me.txtHits.Value = quot;quot;
gt; gt; Me.txtTop.Value = quot;quot;
gt; gt; Me.txtSides.Value = quot;quot;
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; Any help would be appreciated. Thanks.
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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(quot;Belt Buffquot;)

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 = quot;quot;
Me.txtTop.Value = quot;quot;
Me.txtSides.Value = quot;quot;

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 quot;quot; before you write to that cell. (That way it's always used.)
Leslieac wrote:
gt;
gt; Thank You. That brings up the user form when those cells are choosen. I
gt; have another question. My spreadsheet is constructing a finished part that
gt; can have A - I components, so that is why they can answer quot;Yquot; to 9 choices
gt; (g18:g26). Each component will have different dimensions, which is what the
gt; User Form asks them to fill out. Currently, the user form is writing the
gt; data they enter to row22, columns 12, 13, amp; 14. How can I increment this so
gt; that if they enter more than one set of data, it will write to the next row
gt; (i.e. row 23, row 24, etc.). Is this possible?
gt;
gt; Thanks, Leslie
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; How about just showing the userform when they change exactly one cell?
gt; gt;
gt; gt; Option Explicit
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; gt; If Intersect(Target, Me.Range(quot;g18:g26quot;)) Is Nothing Then Exit Sub
gt; gt; If LCase(Target.Value) lt;gt; quot;yquot; Then Exit Sub
gt; gt; UserForm1.Show
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt;
gt; gt; Leslieac wrote:
gt; gt; gt;
gt; gt; gt; I have a column of cells (G18:G26) that the user can choose quot;Yquot; or quot;Nquot;
gt; gt; gt; directly in the cell. I need to have a macro triggered that opens a user
gt; gt; gt; form (it is the same macro for all cells in that range), when they choose
gt; gt; gt; quot;Yquot;. I need to have the results of the user form printed on a unique line in
gt; gt; gt; on a new worksheet tab. This way if they choose quot;Yquot; to more than one cell
gt; gt; gt; (i.e. G18 amp; G19), I will get 2 sets of output data that don't overwrite each
gt; gt; gt; other.
gt; gt; gt;
gt; gt; gt; I have the user form created and working, but not for multiple cells. This
gt; gt; gt; is what I have so far:
gt; gt; gt;
gt; gt; gt; Private Sub cmdAdd_Click()
gt; gt; gt; Dim iRow As Long
gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; Set ws = Worksheets(quot;Belt Buffquot;)
gt; gt; gt;
gt; gt; gt; 'specify an empty row in output sheet
gt; gt; gt; iRow = 22
gt; gt; gt;
gt; gt; gt; 'copy the data to the database
gt; gt; gt; With ws
gt; gt; gt; .Cells(iRow, 12).Value = Me.txtHits.Value
gt; gt; gt; .Cells(iRow, 13).Value = Me.txtTop.Value
gt; gt; gt; .Cells(iRow, 14).Value = Me.txtSides.Value
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; 'clear the data
gt; gt; gt; Me.txtHits.Value = quot;quot;
gt; gt; gt; Me.txtTop.Value = quot;quot;
gt; gt; gt; Me.txtSides.Value = quot;quot;
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Any help would be appreciated. Thanks.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson