Macro data running list

Question

Hello,

I am trying to set up a spread sheet that will allow me to input my data in a single row with drop downs and will then keep a running list of all the items. This is for our horse farm and I want to keep a running list of everything we have done for our horses (i.e. vet, farrier, etc.) I have a spreadsheet set up that works with the drop downs and the button compiles the information, but I'm having trouble making it create a new row every time I insert a new item. I want to be able to pick a date, horse, preset action, any notes and then compile everything in a running list. From there I can set it up to filter everything out based on the horse. Thank you for your help.

Answer 1

Hello Karl M,

I am an independent advisor, trying to help other users in the Community with my experience in Microsoft products. Please note that I am a user like you and don't work directly for Microsoft.

Please try the steps below:

1. Set up your spreadsheet: Open Excel and create a new workbook.
2. Create Headers: In the first row, create headers for Date, Horse, Action, Notes, etc.
3. Data Validation for Dropdowns:

• Select the cells where you want dropdowns for Date, Horse, Action, etc.
• Go to Data > Data Validation > Data Validation.
• Choose "List" under Allow, then enter the options you want for each dropdown.
• Repeat this step for each column requiring dropdowns.

4. Insert Button for Adding New Row:

• Go to Developer > Insert > Button.
• Draw a button on your sheet.
• In the Assign Macro dialog box, choose "New" to create a new macro.
• Enter a name (e.g., addNewRow) and click "OK".

5. Write VBA Code to Add New Row:

• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
• Insert a new module from Insert > Module.
• Write a VBA script to insert a new row when the button is clicked. Here's a basic example:

Sub addNewRow() Dim lastRow As Long With ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name lastRow = . Cells(. Rows.Count, "A"). End(xlUp). Row + 1 . Rows(lastRow). Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End With End Sub

1. Save your VBA code and close the VBA editor.
2. Test your button: Click on the button to ensure it adds a new row with the appropriate dropdowns.

These steps should help you create a spreadsheet where you can input data in a single row with dropdowns and keep a running list of all items. Adjustments may be needed based on your specific requirements and spreadsheet layout.

I would appreciate your feedback

Kind Regards, Fatai