Announcement

Collapse
No announcement yet.

Command Button and Tables for Data Entry

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Command Button and Tables for Data Entry



    Hey Guys!

    I am new here and I am not really new to Code but it takes me longer than normal to understand it as it takes that special teacher to explain it for me to understand it totally. So the question is this I know it can be done I just for the life of me cannot find it online to understand it. So here we go.....

    I have a workbook with 4 -5 tables on one sheet called FoodList( with tables for Protein, Carbs, Fats, Fiber, Specialty) might add more later... But anyway on the Main sheet (Meal Plan) I want add some command buttons to open these tables from the FoodList sheet in order to input new foods into the tables when called on. So I would like to add a button for each table for inputting the new food. I know this is possible. Excel is amazing and it does what ever you need you just need to understand it more.

    I have also added some dropdown boxes to the selections using the Vlookup function which works great I am just a little lost on the buttons.

    Do I need to make forms for each table?
    Is there a way to just call up the built in form for that table on a different sheet?

    I have attached a copy of the workbook I am dealing with so you can see and understand more what I am wanting to do. Now I have not added the buttons as of yet to the Main Sheet. (This is the one I use for Practicing before I get it figured out to move to the good one) LOL...

    Thanks in advance!!!
    Attached Files

  • #2
    Right-click on the command button in Design Mode, then choose View Code. That will open the VBA Editor and create an empty subroutine procedure for the button. Insert a line of code like this, using the name of the table for that button where I have "Table1":
    Code:
    Private Sub CommandButton1_Click()
      EntryForm ("Table1")
    End Sub
    Do that for each button. Then, in the VBA Editor, copy in this code:
    Code:
    Sub EntryForm(TableName As String)
      With Worksheets("FoodList")
        .ListObjects(TableName).Range.Name = "Database"
        .ShowDataForm
      End With
    End Sub
    The trick here is that the ShowDataForm method only works with tables named "Database" or the table at the top left of the worksheet. So we're renaming each table "Database" as needed.

    Comment


    • #3
      Thanks for the response.....

      So with the last part of your post... I have to go and change the makes of each table to "Database"? Take away the names of the tables I have now? I changed it to the below per the table I added the button for.

      Sub EntryForm(TableName As String)
      With Worksheets("FoodList")
      .ListObjects(Protein).Range.Name = "Protein"
      .ShowDataForm
      End With
      End Sub

      Comment


      • #4
        I changed it to this... I get a runtime error with it.

        Sub EntryForm(Protein As String)
        With Worksheets("FoodList")
        .ListObjects(Protein).Range.Name = "Protein"
        .ShowDataForm
        End With
        End Sub

        Comment


        • #5
          I changed the code to the "Table2,3,4,5,etc." Not the name of the tables I gave it and it worked just fine... Thanks for the help on this now I just have to add the buttons for each table. Amazing....

          One more quick question. Is there a simply line of code to add for it to open to the end of the list in the table for entry to the end of the table?

          Complete Code for the button to open the tables are as follow:


          Private Sub CommandButton1_Click()
          EntryForm ("Table3")
          End Sub

          Sub EntryForm(Table3 As String)
          With Worksheets("FoodList")
          .ListObjects(Table3).Range.Name = "Database"
          .ShowDataForm
          End With
          End Sub

          Comment


          • #6
            You're not actually renaming the tables, just (temporarily) naming the range of cells.

            The data form always defaults to the first row, There is a New button on the form, though, and that underlined letter means it's an accelerator (shortcut) key when combined with the Alt key (i.e., Alt+W), so we can send that keystroke combination to the form as it opens:
            Code:
            Sub EntryForm(TableName As String)
              With Worksheets("Structures")
                .ListObjects(TableName).Range.Name = "Database"
                SendKeys "%W"
                .ShowDataForm
              End With
            End Sub

            Comment


            • #7
              Perfection.... Thanks so much!!!

              Comment


              • #8
                And now when I add a new button for Table3 with the same code it tells me "it is an ambigious name detected:Entryform"... So am I not seeing something here....

                Private Sub CommandButton2_Click()
                EntryForm ("Table3")
                End Sub
                Sub EntryForm(Table3 As String)
                With Worksheets("FoodList")
                .ListObjects(Table3).Range.Name = "Database"
                SendKeys "%W"
                .ShowDataForm
                End With
                End Sub

                Or is it because the tables are under each other and not separated from the original cells... Or out of the A1 column?

                Comment


                • #9
                  As you're just starting out, I should warn you that several things about this particular solution are aberrant. The Excel/VBA system is generally a good design and such weirdness as this is rare.

                  Having the ShowDataForm method hard-coded for the word "Database" breaks with good programming practice (and is undocumented). Off the top of my head, I cannot think of any other example like this in the object model.

                  If you try and generate this code using the Macro Recorder, the code it produces will throw an error. That's also unusual (and a bug).

                  Needing to use SendKeys before the form opens also breaks the precept that processes should be sequential..

                  Comment


                  • #10
                    You only want one EntryForm subroutine; it sounds as though you made another copy and that confuses the system because it doesn't know which you are calling. The parameter name is like a variable; when calling the procedure the caller passes the value (table name) into that variable which gets used in the called subroutine.

                    Comment


                    • #11
                      I got it... After walking away and doing other things it came to me that it was looking for the same table as in the first code. But the thing is this as you said"It is looking for the same entrytable" But I want a different table so I simply just labeled them all different as below....


                      Private Sub CommandButton1_Click()
                      EntryForm ("Table2")
                      End Sub
                      Sub EntryForm(Table2 As String)
                      With Worksheets("FoodList")
                      .ListObjects(Table2).Range.Name = "Database"
                      SendKeys "%W"
                      .ShowDataForm
                      End With
                      End Sub


                      Private Sub CommandButton2_Click()
                      EntryForm1 ("Table3")
                      End Sub
                      Sub EntryForm1(Table3 As String)
                      With Worksheets("FoodList")
                      .ListObjects(Table3).Range.Name = "Database"
                      SendKeys "%W"
                      .ShowDataForm
                      End With
                      End Sub

                      And so forth with them all and they work just fine.... Thanks for all the help Johnathan... I knew there was a easy way to get this done...

                      Comment


                      • #12
                        That's not right. Again, you only want one EntryForm subroutine, exactly as in my post (do not edit it in any way). Each Click event calls that same subroutine, passing it the name of the button's associated table.
                        Last edited by JonathanVH; August 11th, 2018, 03:50.

                        Comment


                        • #13


                          JohnathanVH,

                          After I read your comments this morning I went back and did it again and you are so right it does call up just the one ENTRYFORM with just adding the button and the Associated tables open.... I was trying to add the entire CODE again not just the button click. I will correct this on the workbook. Thanks for all your Awesome information...

                          Comment

                          Working...
                          X