Hello,
Is there anyway to modify this code and instead the entry goes in the last entry.
What I need is to insert a new in row 11 and my new entry will in row 11. So everytime that I make a new entry it will insert a new row from row 11.
I want the new entry on the top that start in row 11 instead at the end.
Hope this is possible. Thank you.
Please Login or Register to view this content.
When you used the macro recorder selecting Row 11, right clicked and selected insert, what was the result?
Experience trumps academics every day of the week and twice on Sunday.
After fixing your recorded macro, you probably ended up with something like this.
Please Login or Register to view this content.
Hi
Actually the button in my worksheet is a command button for userform. Should it be Private Sub cmdAdd?
Thanks
Replace these two lines
With thesePlease Login or Register to view this content.
Where you have "LastRow" in your code, replace that with "NewRow" or just 11 since it will be the same all the time.Please Login or Register to view this content.
Instead of just showing a few lines of your macro in your post, you should show it all.
The reason I used the "With......End With" is to show you a better way to structure your code. It also is less typing.
If you show the complete code, we can change it for you if you want.
Hi Jolivanes,
Here you go! Instead the new data is entering in the last entry, instead I want my new entry be in row11. So, I guess a new row need to be inserted for the new entry.
Thank you for your help.
Code for the Button in the worksheet.
Please Login or Register to view this content.
Code for the Userform
Please Login or Register to view this content.
You don't need any of that as your row to receive the data is going to be static as Row11.
Try this
Please Login or Register to view this content.
Or you can save yourself another 50 keystrokes if you replace this
With thisPlease Login or Register to view this content.
Please Login or Register to view this content.
Hi Jolivanes,
Ok... Thanks it did what I wish for. Seemed any codes works for me. When I click the "add in" button in the userform, can you make possible to clear the entry and leave the textbox1 (date) stays?
The reason I wanted to do my entry in row 11 is because my original code was working for a while until I reached to row 3000 which now I am in row over 3000s and it made very slow. It takes about 3-5 minutes before the data goes inside the worksheet. So I am hoping if my entry will start in row 11 then it will go faster. I haven't try your new code yet because I don't have my original workbook with me. I'll let you know Tuesday.
I like your new version though, short and simple and it work perfect. It did actually save me a lot of key strokes.
Thank you and let you know, if the issue still persist.
Hi
I need a very big request... please. Can you add a password (1234 password) to unlock the entire cells and protect the cells after I or the other users clicked the Add in command button?
Also, clear entry after clicking the Add in command button?
I forgot to mention that some of the cells are protected.
Thank you in advance!
Please Login or Register to view this content.
Last edited by RJ1969; 02-12-2023 at 10:12 AM.
Add a line at the start and one at the end like so
You can customize your settings if you want to. Have a read here if that's what you want.Please Login or Register to view this content.
https://www.automateexcel.com/vba/un...ct-worksheets/
You should end up with something like this:
Re: "It takes about 3-5 minutes before the data goes inside the worksheet."Please Login or Register to view this content.
There is something very wrong. With that amount of data, things should be immediate.
I won't re-invent the wheel because there are lots of articles available that address this problem.
Here is one article but feel free to google and you'll come across an article where you recognize what you have in your code.
https://www.thespreadsheetguru.com/b...code-execution
Hi
Ok… thank you.
Where do i place this code ?
Worksheets("Sheet1").Protect "Same passworh here between the double quotes as above" '<----- Change as required
Should put it between End With and End Sub?
or before End With?
Thank a lot.
My bad. Forgot to put it in the last example but it shows in the first example in Post #11.
Just put the unprotect line right after the "Private Sub cmdAddin_Click()" line and the protect line just before "End Sub" just like you mentioned.
Hi Jolivanes
I have to removed this .Offset(, 1).Resize(, 3).ClearContents '<----- Clear B11, C11 and D11 from the code. It deletes the entry in the row 11 everytime I do an entry.
What I meant, is I want the clear the entry in the userform so I can start a new entry in the userform. I guess to add "unload.me"? I am not sure, cuz I am not really good in vba creation.
Other than this, it's perfect.
Thank you
Please Login or Register to view this content.
Re: "the entry in the userform"
Explain in detail what that means.
Hi
After the entry in the userform then I click the “addin” button and after that userform should blank so i can start a new entry.
thanks
Re: userform should blank
You can't have entries on a userform. You can on a textbox.
Take some time to explain in detail what you want done. I think I know what you mean but we've been going around in circles because of, in my mind, poor explanations.
I assume you mean the TextBoxes. It is up to you to mention that. A userform can have a multitude of text boxes so we need to know which ones.
Hi,
sorry, my English is not that very good.
Yes it is the textbox from the userform.
I attached the sample file.
Thanks.
Last edited by RJ1969; 02-13-2023 at 10:24 AM.
So you want "TextBox1", "TextBox2" and "TextBox4" cleared of data?
Hi
Alll the textboxes and the combobox?
Thanks
At this time I only have one combobox but i may add one more later on.
But i will worry about it next time.
Thank tou
Clear them right after copying data into the cells.
Please Login or Register to view this content.
Yay! Thank you so much. It worked perfect.....
Good to hear it works as wished and thanks for letting us know.
Good Luck
Hi Jolivanes
Can you fix something here for me, please.
Row 10 has my title, theme color on the cells and a filter settings. So when I click the add-in button whatever settings I had in Row10 it dragged it down to row 12. This is the only issue I have encounter but other than this, all is ok.
Thank you again.
Please Login or Register to view this content.
My preference is to insert a row below your formatted row (row 10), manually clear all formats so it is a clear row of cells and set the height to 0
Now where the code refers to row 11, change that to 12 so Range A11 becomes Range A12
I should have been more specific. Was in too much of a hurry rushing out.
Highlight Cells in Row11 from Column A to the last used Column.
Right Click while your mouse is on one of the hightlighted Cells in Row 11
Select "Insert"
Now manually clear all formats in that empty Row which should be Row11
Now select the entire Row11 by left clicking on the 11 with your mouse
While keeping the mousepointer on the 11, right click and select RowHeight
In the little window, change the current RowHeight number to 0 (zero)
Click on OK
In your code, change the 11 to 12 so it'll be like so
Insert a line to make the inserted row the same height as the row below it so it'll look like thisPlease Login or Register to view this content.
Try that and report back.Please Login or Register to view this content.
Hi Jolivanes,
Got it! Took me a while to figure it out but I did it.
Thank you. You're the best.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks