Making command button insert data into next cell without activating cell

bantrybay

New Member
Joined
Jan 14, 2020
Messages
13
Office Version
  1. 2010
Platform
  1. Windows
Hi folks,

I am like a lot of other posters on here an absolute novice when it comes to Excel and VBA's, Macro's etc.... but i find myself in a position where i am required to make a easy system for tracking orders. The shown table will be copied hundreds of times in 1 excel sheet and after the table is marked done we will manually cut it out of the sheet and paste it into another folder for archiving purposes so the cell numbers will change. What i am trying to do is make it so that when the other person clicks on the DONE button it inputs the date and time automatically in the cell under the DONE button. But again as the cell numbers will be different in each table i can't assign a cell number for it, is there a way for this to be done. The same is also required for the Delivery and Collect buttons i have on the table. My plan for this would be whichever one is clicked shows up in the cell to the right of the buttons. I hope i explained myself in a somewhat clear manner but if not please let me know. And apologies if this has been shown before, i couldn't find it.

Any help with this would be greatly apprecaited
BB
 

Attachments

  • table1.png
    table1.png
    9.6 KB · Views: 46

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
quick cheat,

1. Enter a reference hidden under your button, Im using the string "Anchorrefpoint") as it is very unlikely it will be found anywhere in a worksheet. then use find and address to get the cell reference of the anchor:
2. set the address to a string variable
3. use offset to get the correct cell and use Now enter the date and time

VBA Code:
Dim x As String
x = Sheets("sheet1").Range("A1:ZZ10000").Find("Anchorrefpoint").Address
Range(x).Offset(1, 0).Value = Now

Hope that helps.

Tom
 
Upvote 0
Thank you Tom, i moved the done button and typed in Anchorrefpoint into the cell under it and put the done button back over the cell but i can't find and address this cell. if it helps the cell is N8. I merged N and O to become 1 cell due to the size of the button.
 
Upvote 0
Sorry, I forgot to mention youll need to change the sheet reference too:
VBA Code:
Change:
x = Sheets("sheet1")
to 
x = Sheets("YOUR SHEET NAME HERE")
 
Upvote 0
Haha yep just realised that after i posted the message so i have that working where i select the done button and the date and time appear underneath, now my issue is the copied tables that i pasted underneath. when i click on the done button it only goes into the first table again each time so is there a way to resolve this. Sorry for the questions, im just stumped by all the different formulas.
 
Upvote 0
Ah oh dear I didn't fully clock the part where you said the Hundreds of tables will end up in one worksheet!

there is a fix but its not very good. you could simply include the line:

Range(x).clear
meaning the anchorrefpoint is cleared down and next time the code is run it jumps to the next form.

To be honest, your process probably needs a little tweak to get it to where you want. If I was consolidating those down, I would have one form in a workbook, when the data is complete, the Done button writes the datestamp and I would then have a macro triggered with an array picking up all the data in the form. That would be easy as the form is a standard template. The array would then write to a Consolidated book which would contain all the forms in a database layout
 
Upvote 0
Again thank you Tom, i tried the Range(x).clear but it ran into some issue so left it out for the time being. What you said about the consolidating is what i was thinking of to be the endgame. My plan was to create a system where i clicked on a create button and the table i showed above would appear in the worksheet. and then if i clicked create again another table would appear under that one and so on and so on. It would be filled in and then when the task was completed the done button was clicked and the table was cut from the worksheet, sent to another worksheet where it would be archived and then the rows that the said table were in would delete themselves so that the next table underneath moved up to keep the sheet uniform. I think it might be a bit of a stretch for the moment but something to aim for. Thanks again for your assistance, i really appreciate it.
Regards,
James
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top