Home

How to make a form to fill the first blank row instead of the first row of a table.

%3CLINGO-SUB%20id%3D%22lingo-sub-1117396%22%20slang%3D%22en-US%22%3EHow%20to%20make%20a%20form%20to%20fill%20the%20first%20blank%20row%20instead%20of%20the%20first%20row%20of%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117396%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20Excel%20forms%2C%20when%20selecting%20the%20forms%20options%20and%20entering%20the%20data%2C%20the%20data%20is%20entered%20in%20the%20first%20row%20of%20the%20table.%20I%20need%20the%20form%20to%20enter%20the%20data%20from%20the%20blank%20row%20of%20the%20table%20so%20that%20the%20data%20may%20be%20entered%20in%20the%20same%20table%20after%20multiple%20application%20closures.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1117396%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117480%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20form%20to%20fill%20the%20first%20blank%20row%20instead%20of%20the%20first%20row%20of%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117480%22%20slang%3D%22en-US%22%3EI%20would%20define%20a%20variable%20called%20%22last%20row%22%20which%20gives%20me%20the%20number%20of%20the%20last%20row.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can%20use%20this%20line%20of%20code%20to%20do%20that%3A%3CBR%20%2F%3E%3CBR%20%2F%3ElastRow%20%3D%20Cells(Rows.Count%2C%201).End(xlUp).Row%3CBR%20%2F%3E%3CBR%20%2F%3Enow%20to%20add%20data%20from%20the%20userform%20you%20can%20use%20something%20like%20this%3CBR%20%2F%3E%3CBR%20%2F%3ECells(lastRow%2C%201)%20%3D%20me.nameoftheelementfromyouruserform%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20this%20doesn't%20help%2C%20please%20upload%20the%20excel%20file%20so%20I%20can%20take%20a%20look%20at%20it%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117506%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20form%20to%20fill%20the%20first%20blank%20row%20instead%20of%20the%20first%20row%20of%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%20I%20have%20attached%20the%20worksheet%20of%20the%20form%20and%20attached%20the%20trouble%20that%20I%20am%20having.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117516%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20form%20to%20fill%20the%20first%20blank%20row%20instead%20of%20the%20first%20row%20of%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117516%22%20slang%3D%22en-US%22%3EThe%20vba%20code%20is%20not%20saved%20in%20this%20document%20but%20in%20E%3A%5CWork%20documents%5COneDrive%20-%20Quest%20Pharmaceuticals%20Pvt.%20Ltd%5CWork%20documents%5CPerformance%20appraisal%20and%20Operations%20management%5CDraft%20for%20the%20activities%20and%20task%20list-macro%20enabled.xlsm'!New_data_entry%3CBR%20%2F%3E%3CBR%20%2F%3EFirst%20you%20sould%20include%20the%20code%20into%20your%20actual%20file.%3CBR%20%2F%3E%3CBR%20%2F%3EAlso%20I%20don't%20really%20understand%20the%20requirement%20that%20you%20mention%20in%20the%20file%2C%20sorry%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117529%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20form%20to%20fill%20the%20first%20blank%20row%20instead%20of%20the%20first%20row%20of%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20quick%20response.%20I%20have%20attached%20the%20updated%20file.%20Hope%20you%20understand%20my%20requirement.%20I%20have%20only%20used%20a%20macro%20and%20not%20edited%20the%20code%20of%20the%20macro.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117826%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20form%20to%20fill%20the%20first%20blank%20row%20instead%20of%20the%20first%20row%20of%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117826%22%20slang%3D%22en-US%22%3EWhat%20excel%20version%20are%20you%20using%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117854%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20form%20to%20fill%20the%20first%20blank%20row%20instead%20of%20the%20first%20row%20of%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117854%22%20slang%3D%22en-US%22%3EMS%20Excel%202020%20version%2014.0.4734.1000(32%20bit)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1118771%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20form%20to%20fill%20the%20first%20blank%20row%20instead%20of%20the%20first%20row%20of%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1118771%22%20slang%3D%22en-US%22%3EI%20only%20now%20realised%20that%20you%20are%20using%20the%20excel%20standard%20dataform.%20Unfortunately%2C%20there%20is%20no%20way%20to%20change%20the%20behaviour%20of%20this%20form.%20In%20order%20to%20make%20it%20do%20what%20you%20require%2C%20we%20would%20need%20to%20set%20up%20a%20completely%20new%20inputform%20with%20VBA.%20Of%20course%20this%20could%20be%20done%20but%20it%20requires%20some%20skills%20and%20time...%3C%2FLINGO-BODY%3E
bideha
New Contributor

In Excel forms, when selecting the forms options and entering the data, the data is entered in the first row of the table. I need the form to enter the data from the blank row of the table so that the data may be entered in the same table after multiple application closures.

7 Replies
Highlighted
I would define a variable called "last row" which gives me the number of the last row.

You can use this line of code to do that:

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

now to add data from the userform you can use something like this

Cells(lastRow, 1) = me.nameoftheelementfromyouruserform

If this doesn't help, please upload the excel file so I can take a look at it



Highlighted

@PascalKTeam 

Thank you for your reply. I have attached the worksheet of the form and attached the trouble that I am having. 

Highlighted
The vba code is not saved in this document but in E:\Work documents\OneDrive - Quest Pharmaceuticals Pvt. Ltd\Work documents\Performance appraisal and Operations management\Draft for the activities and task list-macro enabled.xlsm'!New_data_entry

First you sould include the code into your actual file.

Also I don't really understand the requirement that you mention in the file, sorry
Highlighted

@PascalKTeam 

Thank you for your quick response. I have attached the updated file. Hope you understand my requirement. I have only used a macro and not edited the code of the macro.

Highlighted
Highlighted
MS Excel 2020 version 14.0.4734.1000(32 bit)
Highlighted
I only now realised that you are using the excel standard dataform. Unfortunately, there is no way to change the behaviour of this form. In order to make it do what you require, we would need to set up a completely new inputform with VBA. Of course this could be done but it requires some skills and time...
Related Conversations
How can I delete a quick form in teams???
Nigel_Christian in Microsoft Teams on
3 Replies
Apostrophes in my spreadsheet :-(
Brian Donnelly in Microsoft Forms on
3 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
Specific Form
Wanen in Access on
0 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies