VBA Macro Required for Insert Row . . .

[MarthaSue]

I am looking for a VBA Command for the following situation . . . . Is it
possible that when a user chooses 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 . . .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 . .

 

[Barb Reinhardt]

It sounds like you want to use a Worksheet Change event. I'm getting a
headache trying to read through your unformatted request, so can't help
anymore right now.

 

[Matthew Pfluger]

MarthSue,

It is very possible to insert new lines. The example below will insert 3
lines below line 1:

Sub InsertLine()
Dim iStartRow As Integer
Dim iNumOfRowsToInsert As Integer

' Change these numbers to what you need
iStartRow = 2
iNumOfRowsToInsert = 3

Rows(iStartRow & ":" & iStartRow + _
iNumOfRowsToInsert - 1).Insert
End Sub


Modify this code to fit your needs. For example, you mention that sometimes
you need to insert only 1 row. In that case, change iNumOfRowsToInsert to 1.
Play around with this.
Matthew Pfluger

 

[MarthaSue]

How can i make it easier for you barb? . . i'm new to this . . .not sure what
format you are used to . . .

 

[Gord Dibben]

MarthaSue

I don't know about others, but I find it very difficult to read and understand
your posts.

Normal sentence and paragraph structure would be easier to get through.

The ellipses interspersed throughout do not denote the beginnings or ends of a
sentence or paragraph.

It seems they do no more than seperate your thoughts and confuse the heck out of
me.


Gord Dibben MS Excel MVP