Border around Data Entered in Variable Rows
 

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

Border around Data Entered in Variable Rows
 

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

--
Regards,
Tom Ogilvy


"RGeneral" wrote in message
om...
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

Border around Data Entered in Variable Rows
 

Thanks, Tom! So simple and it works like a charm!

-Ray


"Tom Ogilvy" wrote in message ...
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

--
Regards,
Tom Ogilvy


"RGeneral" wrote in message
om...
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