Border around Data Entered in Variable Rows

R

RGeneral

Hello everyone-

I apologize for posting this problem twice (I posted it over in
excel.misc as well), but this seemed a more appropriate place to ask
this question.

I have a UserForm that, when filled out, places the data entered at
the end of a database. I would like to format the row to include a
border around the data (i.e., "box" it) so that it matches the other
sections in the database. I have 6 fields that are entered into the
row. My problem comes in defining the range to select to border.
Since the row where it inserts the data is variable, but the columns
remain the same (A:F), my range must also be variable. How do I tell
Excel to place a border around the entered data each time AND insert a
row between the new data and the bottom of the border? This is the
macro I have so far (yes, I had help with this one too):

Private Sub CommandButton1_Click()

Dim lngRow As Long
lngRow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 3
Cells(lngRow, 1).Value = TextBox1.Value

Cells(lngRow, 1).Select
Cells (lngRow, 1).Offset(0, 1).Value = TextBox2.Value
Cells (lngRow, 1).Offset(0, 2).Value = TextBox3.Value
Cells (lngRow, 1).Offset(0, 3).Value = TextBox4.Value
Cells (lngRow, 1).Offset(0, 4).Value = TextBox5.Value
Cells (lngRow, 1).Offset(0, 5).Value = TextBox6.Value
Cells (lngRow, 1).Offset(0, 6).Value = TextBox7.Value

Unload UserForm1

End Sub

Thanks for any help offered...

-Ray
 
T

Tom Ogilvy

Private Sub CommandButton1_Click()

Dim lngRow As Long
lngRow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 3
Cells(lngRow, 1).Value = TextBox1.Value

Cells(lngRow, 1).Select
Cells (lngRow, 1).Offset(0, 1).Value = TextBox2.Value
Cells (lngRow, 1).Offset(0, 2).Value = TextBox3.Value
Cells (lngRow, 1).Offset(0, 3).Value = TextBox4.Value
Cells (lngRow, 1).Offset(0, 4).Value = TextBox5.Value
Cells (lngRow, 1).Offset(0, 5).Value = TextBox6.Value
Cells (lngRow, 1).Offset(0, 6).Value = TextBox7.Value
selection.Resize(2,6).BorderAround Weight:=xlMedium,
ColorIndex:=xlAutomatic

Unload UserForm1

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top