Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I auto number rows only when data is entered in the same r | Excel Discussion (Misc queries) | |||
Adding rows once data is entered | Excel Discussion (Misc queries) | |||
Add rows when data is entered | Excel Discussion (Misc queries) | |||
Print only rows with data entered in an excel template | Excel Discussion (Misc queries) | |||
Border Formatting Disappearing when data entered into cell | Excel Discussion (Misc queries) |