VBA Command - Not Familiar With Program

[MarthaSue]

I am looking for a VBA Command for the following situation . . . .

it possible that when a user choses an action from that list that rows can be
inserted and new data entered . . If User chooses "Sample" . .from the list
in Row 1 . . . can i create a function that will insert one row when Sample
is chosen (Insert Row 2) . . but if User chooses Inspect . . .from the list
in Row 1 . . .can the "function" insert three row (Insert Row 2, 3, & 4) . .
.Currently Row 1 contains several columns of data describing a particular
location and the work that has been currently completed on the site . .one
location can be used once or can be used multiple times. . . . each location
is unique . .none the same . . .i want to be able to not have to write all
the info. over and over again . . . Row 2 would contain several columns of
information for a new location . . .i would like to create a function that
would allow the user to enter more data on location in Row 1 if need be .
.dependant on the work that might have to be completed . . .Sample, Inspect,
Mix . . . Is this possible . . please let me know if you require more
information . . Once again i appreciate your time . .

Please let me know if you require more information .. . i am a rookie when
it come to this sort of thing . . . thanks for your time . . . .

 

[Dave D-C]

To insert rows 2, 3, & 4:
Rows(2).Resize(3).Insert

 

[Otto Moehrbach]

Martha
There is no "VBA command" to do what you want. It's just a matter of
writing a VBA macro that includes the instructions to do what you want.
You say you have "that list". What list? Where is it? In what form is the
list? Is it a Data Validation list?
You say "when a user choses an action from that list". What does he do when
he "chooses an action from that list"? Exactly what does he do? Does he
click on an item in the "list"?
You say "If User chooses "Sample" . .from the list in Row 1". Do you mean
that the "list" consists of the entries in Row 1? Like headers perhaps?
To insert a number (any number) of rows as a function of the selection made
is easy to do. But the devil is in the details. Please post back with more
detail. HTH Otto

 

[MarthaSue]

The list is a Data Validation list . . it is on the current workbook. . .
..The user would click on an action item from the list . . .The list does not
consists of entries from Row 1, the list is part of Row 1, it would be in
Column L . . .please let me know if you require more information . . .

 

[MarthaSue]

Again I would appreciate any help . . .

I am looking for a VBA Command for the following situation . . . .


Please let me know if you require more information .. . i am a rookie when
it come to this sort of thing . . . thanks for your time . . . .

 

[MarthaSue]

I am looking for a VBA Command for the following situation . . . . it
possible that when a user choses an action from that list that rows can be
inserted and new data entered . . If User chooses "Sample" . .from the list
in Row 1 . . . can i create a function that will insert one row when Sample
is chosen (Insert Row 2) . . but if User chooses Inspect . . .from the list
in Row 1 . . .can the "function" insert three row (Insert Row 2, 3, & 4) . .
..Currently Row 1 contains several columns of data describing a particular
location and the work that has been currently completed on the site . . .one
location can be used once or can be used multiple times. . . . each location
is unique . .none the same . . .i want to be able to not have to write all
the info. over and over again . . . Row 2 would contain several columns of
information for a new location . . .i would like to create a function that
would allow the user to enter more data on location in Row 1 if need be .
..dependant on the work that might have to be completed . . .Sample, Inspect,
Mix . . . Is this possible . . please let me know if you require more
information . . Once again i appreciate your time . . Please let me know if
you require more information .. . i am a rookie when it come to this sort of
thing . . . thanks for your time . . . . The list is a Data Validation list .
.. it is on the current workbook. . . .The user would click on an action item
from the list . . .The list does not consists of entries from Row 1, the
list is part of Row 1, it would be in Column L . . .please let me know if you
require more information . . .

 

[Otto Moehrbach]

Martha
This little macro will do what you want. I assumed that "Sample" and
"Inspect" were all the choices you had in the A1 Data Validation list. If
this is not so, this will not work as you want. Post back if that is the
case.
Note that this macro is a sheet macro for the sheet in which you have that
A1 Data Validation list. As such, this macro must be placed in the sheet
module of that sheet. To access that module, right-click on the sheet tab,
select View Code, and paste this macro into that module. "X" out of that
module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value = "Sample" Then
Rows(2).Insert
Else
Rows(2).Resize(3).Insert
End If
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

 

[MarthaSue]

Thanks Otto for the reply . . my "Notify me of replies" doesn't seem to be
working . . .there are about 20 choices in the Data Validation List, not just
the two . . .each choice . . will cause a different event . . should i use
an event macro . . i was reading up on it . . .Columns 1-7 in Row A1 will
contain information that will have to repeat into the next row that is being
inserted . . .each choice can cause a different amount of rows to be
inserted, some choices will require no rows . . . Basically i don't want the
user to have to type in the repeating information (to save time) . . can i
still use the one you gave me and modify it a little . . . i would also like
to note that there is data after the column containing the validation list .
.. .the remaining columns in the row contain dates and formulas based on the
action item choosen as well . . the user enters the date the task was
completed in the next column after the validation list . . and then the
remaining columns are set with formulas to change accordingly (basically due
dates for actions that need to be completed based on the action that is
choosen) . . .can i send you a copy . . it's so hard to explain . . would
make more sense if you could just see it . . .thanks again . . i truly
appreciate all the time you given me . . .

 

[MarthaSue]

Thanks Otto for the reply . . my "Notify me of replies" doesn't seem to be
working . . .there are about 20 choices in the Data Validation List, not just
the two . . .each choice . . will cause a different event . . should i use
an event macro . . i was reading up on it . . .Columns 1-7 in Row A1 will
contain information that will have to repeat into the next row that is being
inserted . . .each choice can cause a different amount of rows to be
inserted, some choices will require no rows . . . Basically i don't want the
user to have to type in the repeating information (to save time) . . can i
still use the one you gave me and modify it a little . . . i would also like
to note that there is data after the column containing the validation list .
.. .the remaining columns in the row contain dates and formulas based on the
action item choosen as well . . the user enters the date the task was
completed in the next column after the validation list . . and then the
remaining columns are set with formulas to change accordingly (basically due
dates for actions that need to be completed based on the action that is
choosen) . . .can i send you a copy . . it's so hard to explain . . would
make more sense if you could just see it . . .thanks again . . i truly
appreciate all the time you given me . .

 

[MarthaSue]

Reposting . . .still require more information . . .would appreciate any help
.. . .
.. . .there are about 20 choices in the Data Validation List, not just

 

[Otto Moehrbach]

Martha
I've been playing musical chairs with Internet services and have lost
track of your problem. I gave you some code for a drop-down list of 2
items. You responded that you had more than 2 items. The code below is an
example of how you could handle that situation. Post back if you need more.
If this doesn't look like what you need, perhaps it would be best if you
sent me a sample of your file. If you wish to do so my email address is
(e-mail address removed). Remove "extra" from this address. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NumRows As Long
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Target.Value
Case "Sample": NumRows = 1
Case "Inspect": NumRows = 3
Case "Item3": NumRows = 4
Case "Item4": NumRows = 5
'Etc, Etc, Etc
End Select
Application.EnableEvents = False
Rows(2).Resize(NumRows).Insert
Target.ClearContents
Application.EnableEvents = True
End If
End Sub