Inserting a Row from a UserForm

R

RGeneral

Hi guys-

I'm kind of a self-taught VBA programmer and I can usually blunder
around and get a macro to work, but I'm having trouble with a rather
difficult one right now.

I've created a UserForm that has 6 fields the user has to fill in for
a database. Once the user hits the "OK" button, I want the macro to
look for the last entry in the database that is identical to what the
user filled in Field 1 of the UserForm and insert a row beneath it and
fill in the cells with the data from the UserForm. So, i.e:

Apple
Apple
Orange
Orange
Orange
Banana
Grape
Grape

If I type "Banana" in Field 1 on the Userform, I would like it to
insert a row beneath Banana and then fill in the cells with the other
information entered in the other 5 fields.

Any help on this would be greatly appreciated!

Thanks,
-Ray
 
M

Michael Malinsky

Try the following code. It assumes your searching column A for the data
entered in the first text box.

Private Sub CommandButton1_Click()

Dim rng As Range
Set rng = _
Columns(1).Find(What:=TextBox1.Value, After:=Cells(Rows.Count, "A"),
_
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not rng Is Nothing Then
rng.Select
rng.Offset(1, 0).EntireRow.Insert
rng.Offset(1, 0).Value = TextBox1.Value
rng.Offset(1, 1).Value = TextBox2.Value
rng.Offset(1, 2).Value = TextBox3.Value
rng.Offset(1, 3).Value = TextBox4.Value
rng.Offset(1, 4).Value = TextBox5.Value
rng.Offset(1, 5).Value = TextBox6.Value
Else
MsgBox TextBox1.Value & " not found."
End If

Unload UserForm1

End Sub

HTH
--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
R

RGeneral

Michael Malinsky said:
Try the following code. It assumes your searching column A for the data
entered in the first text box.

Private Sub CommandButton1_Click()

Dim rng As Range
Set rng = _
Columns(1).Find(What:=TextBox1.Value, After:=Cells(Rows.Count, "A"),
_
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not rng Is Nothing Then
rng.Select
rng.Offset(1, 0).EntireRow.Insert
rng.Offset(1, 0).Value = TextBox1.Value
rng.Offset(1, 1).Value = TextBox2.Value
rng.Offset(1, 2).Value = TextBox3.Value
rng.Offset(1, 3).Value = TextBox4.Value
rng.Offset(1, 4).Value = TextBox5.Value
rng.Offset(1, 5).Value = TextBox6.Value
Else
MsgBox TextBox1.Value & " not found."
End If

Unload UserForm1

End Sub

HTH
--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
Click to expand...

Thanks Michael! Works perfectly! I have one more question, though (I
apologize for not putting this in the earlier post)... The different
sections of my database (i.e., apple, orange, etc.) each have an
all-around border. When I insert a row, it messes up the formatting
of the border. I know the macro to reset the border to how I want it,
but how do I define the Range of the selection since it's a changing
value? i.e., the Range won't always be A27:G27, the row numbers will
change, but the columns will still be A:G. Thanks again for any help
offered!

-Ray
 
D

Dave Peterson

I'm not Michael, but one way is to find the last used cell in a column (say
column A), then use that as your range:

dim myRngToOutline as range
with me 'or worksheets("sheet1")???
set myRngToOutline = .range("a1:g" & .cells(.rows.count,"A").end(xlup).row)
end with

A1:G(LastUsedRowInColumnA)
 
Top