Auto create a command button when click another command button in a specific cell and assign code to the newly created command button

  • Hi All,

    Much appreciate, if anyone could help me to auto create a command button when click another command button in a specific cell and assign code to the newly created command button.

    I have very less knowledge on excel VBA. The first command button (let's say Command1) is on a user form. When Command1 is clicked a new command button (Command2) should be created on a specific cell in a worksheet (Sheet1). When Command1 is clicked macro should be auto assigned to Command2.

    Please help me to get this done.

    Much thanks!

  • Below is my requirement. Please check it and assist. Highly appreciate your support.

    There are 2 sheets and 1 user form:

    1st sheet: Stationary Inventory

    2nd sheet: Order Request

    User Form: Order Request user form

    When an entry is made and saved in the user form it is recorded in Order Request sheet.

    As an example: in a order request, there is 20 pens is recorded. When the request is saved using save command button, another command button (CommandButtonIssue) should be created in the same row of the order request was recorded in Order Request sheet. When CommandButtonIssue is clicked, 20 pens should be deducted from pens inventory which is recorded in Stationary Inventory sheet.

    This is the part of a module which is for stationary management in a office.

    Through this staff can request stationary via order request. Stationary admin can issue stationary as per the request (via CommandButtonIssue). When Stationary is issued, inventory is auto updated.

    Many Thanks for assisting!

  • For Inventory Excel is not the best but of course can be used (a database program would be better like Access).

    if you want for each open request (can there be more than one?) a single button there is mor coding effort. What happens wit the Order in the Order Request sheet? Stays in or will be deleted?

    if it stays in the sheet then a much simpler approach would be to add a column "order accepted" with e.g. True/False and a always visible command button which does the deducing and also checking the corresponding "order accepted" cell. Additionally you can protect the cells for unwanted changes afterwards.

  • I think what you have suggested would fulfill my requirement. Could you please share the code (or the method) to do it.

    Many thanks!

  • then add a column and mark it as open or done. you donot need to generate an extra button. make the button visible if there is an open order and hide the button when the inventory list is updated

  • of course but this means extra effort (same as a button)

    a normal checkbox is an extra shape ans is connect to a cell. If you use the cell without the checkbox --> less effort

    If you use a ActiveX element which supports more events and not only code linked to it --> more effort

  • Is it. Okay i will try the previous method.

    Apart from that could you please check the attached file and advise me on how to insert a formula to auto sum up the item quantity in Inventory Sheet (adding up the items in receiving record item wise).

    Here I need to auto insert a new item to the Inventory sheet if receiving record had a new item. As an example, if blue pen is received on 20th Jan 2020, i need to add blue pen to cell B5 and add up the quantity of it to the cell C5.

    Many Thanks!

  • Thank you for your quick reply.

    If Receiving Summary sheet is updated from VBA user form, is it still possible to have it as a table?

  • Isn't there a way to auto copy the row to the table?

    Here I have attached the excel vba file i'm developing. Please have a look at it.

    In this file above mentioned Receiving summary sheet is equivalent to Inventory sheet and above mentioned Inventory sheet is equivalent to Stock sheet.

    Highly appreciate your effort. Thanks.

  • I had a short look but I do not want to find out what all the code does and how the data is connected.

    if you tell me what exactly should be copied into which dataset and what is the trigger to do so, then I would be able to provide a short code to add data to the table.

    But the better way to learn is if you do it yourself. Have a look into https://www.thespreadsheetguru…o-listobject-excel-tables this is a really good description how you can handle tables via VBA

  • Helo,

    Your link and advise was really helpful and i was able to link the tables and update with command button as required. I will try on deducting from Inventory sheet when order request is accepted as you have advised.

    Highly appreciate your support!! Many may thanks! Keep doing the good work! :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!