Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Several Rows at a time and Asking for User Input
Hello all,
Have a workbook with 2 sheets--one named "Fields" containing formatted columns, rows, etc., and another called "Data" containing the data. I would like the following functionality when adding a new record in Data (one record consists of 4 rows): 1. Copy the group of 4 formatted rows in the Fields sheet (rows 13 through 16), 2. Activate the Data sheet, 3. Have the user click on a cell in column A above where he or she wants the 4 rows to be inserted, 4. Insert the 4 rows (adding rows if placed between existing records so as to not overwrite data), 5. Ask the user for the value of the cells in column A (A-cells in the 4 rows will all be the same value), 6. Ask the user for the value of the cells in column B (all 4 B-cells will also be the same), and 7. Have Excel fill in the cells with the values. Also, when inserting the new record (4 rows), a user may happen to click on any of the rows or cells in column A of the same record, so the new record would have to be placed below the last used row of the record. Hope this makes sense; any help would be greatly appreciated. Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Several Rows at a time and Asking for User Input
Lots of Thanks JLGWhiz--will give this a try later on...
JLGWhiz wrote: This is all untried, untested, but is provided to to give you an idea of the approach to the solution. 1. Copy the group of 4 formatted rows in the Fields sheet (rows 13 through 16), Worksheets("Fields).Range("A13:A16).EntireRow.Copy 2. Activate the Data sheet, Worksheets("Data").Activate 3. Have the user click on a cell in column A above where he or she wants the 4 rows to be inserted, LastRow = Cells(Rows.Count,1).End(xlUp).Row Response = MsgBox("The last row containing data is " & LastRow & ". Do you want to add the records in the next available rows?", vbYesNo, "Destination Option" If Response = vbYes Then Cells(LastRow + 1,1).Activate ActiveSheet.Paste Else InsOpt = Application.InputBox("Enter a cell in A1 format - example: "A15" - where you want to insert the records.", "Select Insetion Point", Type:=8) End If 4. Insert the 4 rows (adding rows if placed between existing records so as to not overwrite data), If InsOpt < Cancel Or InsOPt < "" Then Range(Cells(Range(InsOpt).Offset(1,0),1), Cells(Range(InsOpt).Offset(4,0),1).Select Selection.EntireRow.Insert Range(InsOpt).Offset(1,0).Activate ActiveSheet.Paste End If 5. Ask the user for the value of the cells in column A (A-cells in the 4 rows will all be the same value), aVal = InputBox("Enter the value for column A of the inserted records.", "Col A Values" 6. Ask the user for the value of the cells in column B (all 4 B-cells will also be the same), and bVal = InputBox("Enter the value for column B of the inserted records.", "Col B values" 7. Have Excel fill in the cells with the values. Range(Cells(Range(InsOpt).Offset(1,0),1), Cells(Range(InsOpt).Offset(4,0),1) = aVal Range(Cells(Range(InsOpt).Offset(1,0),2), Cells(Range(InsOpt).Offset(4,0),2) = bVal Also, when inserting the new record (4 rows), a user may happen to click on any of the rows or cells in column A of the same record, so the new record would have to be placed below the last used row of the record. I'm not sure what the last paragraph means. "Arnold" wrote: Hello all, Have a workbook with 2 sheets--one named "Fields" containing formatted columns, rows, etc., and another called "Data" containing the data. I would like the following functionality when adding a new record in Data (one record consists of 4 rows): 1. Copy the group of 4 formatted rows in the Fields sheet (rows 13 through 16), 2. Activate the Data sheet, 3. Have the user click on a cell in column A above where he or she wants the 4 rows to be inserted, 4. Insert the 4 rows (adding rows if placed between existing records so as to not overwrite data), 5. Ask the user for the value of the cells in column A (A-cells in the 4 rows will all be the same value), 6. Ask the user for the value of the cells in column B (all 4 B-cells will also be the same), and 7. Have Excel fill in the cells with the values. Also, when inserting the new record (4 rows), a user may happen to click on any of the rows or cells in column A of the same record, so the new record would have to be placed below the last used row of the record. Hope this makes sense; any help would be greatly appreciated. Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show a number of rows based upon user input | Excel Discussion (Misc queries) | |||
User time input | Excel Worksheet Functions | |||
Trying to select a specific range based on the time value of user form input | New Users to Excel | |||
user input for date and time | Excel Programming | |||
adding row headings to multiple pages - how do i input the rows? | New Users to Excel |