• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding data from a userform to a dynamic row of cells

H

Hugo huijer

Member
Hi Chandoo,

I have a pretty specific issue, which I can't work out cause I'm still pretty new to VB. I have attached my sheet for reference.

I have set up 3 user forms. One for entering regular data in a sheet, one for building a budget in a sheet, and another one for building a variation budget for a sheet. The last 2 are pretty much alike, as they do mostly the same. They enter data in rows, and with each new data input a new row should be created for that data. For the first of these forms I got it to work. The code inserts a new row every time data gets inputted, and this data is filled in this row.

I want the 3rd userform to do the same, but here lies my issue. The 2nd user form has a fixed starting point, so it's easy to let the code insert a row in the proper place. The 3rd user form doesn't have this, as the cells move whenever data is entered in the 2nd form. This is why the code of the 3rd form needs something that lets it insert a row in the right spot.

How should I code this, and can you help me with this issue?

I'm not English so this explanation might be vague. I hope you understand it. Feel free to ask more questions!
 

Attachments

  • examplechandoo.xlsm
    54.2 KB · Views: 12
Hi Hugo ,

See if this is OK. I have not made any significant change.

Remember , when you create a userform , it is good to set the TabIndex property so that pressing the TAB key enables the user to go from one data entry box to the other in the correct order.

Narayan
 

Attachments

  • examplechandoo.xlsm
    47.4 KB · Views: 26
Thank you very much. This seems to work just fine!

This brings me to the next problem though, which I hope is just a minor one. When I directly copy your code to my sheet (it should work, because everything is named the same), I get an error when trying to let the form add the data to the sheet. (run-time error '1004': Unable to get the Match property of the WorksheetFunction class)

I don't know what it means and was hoping that you could point me in the right direction.

Thanks heaps!
 
BTW, when I click 'Debug' after I get this error, the line below is highlighted:

Code: 
 startrow = Application.WorksheetFunction.Match("Variation Budget", Range("B:B"), 0) + 2
 
Nevermind. I read on a forum that it had something to do with the Match value not being found. so I checked, and there it was... It was looking for "Variation Budget" whereas the sheet tells "Variation budget". :p

Thanks a bunch for your help! It's greatly appreciated.
 
You must log in or register to reply here.
Back
Top