News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • [H] Multiple CommandBox

    Viewing 2 reply threads
    • Author
      • #2257020
        AskWoody Lounger


        I have 7 combo box and 1 cmb will have 14 items in the list.

        I have 2 sets for this 14 items which are PRODUCTION and SOLD/WITHDRAWN

        Now, if I put random materials 4 out of 7 (leaving to 3 blanks). what code to populate the excel?

        Thanks in Advance.

        Sub Reset()

        Dim iRow As Long

        iRow = [Counta(Database Table!A:A)] ‘ identifying the last row

        With frmForm

        .txtTimeStart.Value = “”
        .txtTimeEnd.Value = “”
        .txtTotalFeed.Value = “”
        .txtProduction1.Value = “”
        .txtProduction2.Value = “”
        .txtProduction3.Value = “”
        .txtProduction4.Value = “”
        .txtProduction5.Value = “”
        .txtProduction6.Value = “”
        .txtProduction7.Value = “”
        .txtWithdrawals1.Value = “”
        .txtWithdrawals2.Value = “”
        .txtWithdrawals3.Value = “”
        .txtWithdrawals4.Value = “”
        .txtWithdrawals5.Value = “”
        .txtWithdrawals6.Value = “”
        .txtWithdrawals7.Value = “”
        .cmbMaterials1.Value = “”
        .cmbMaterials2.Value = “”
        .cmbMaterials3.Value = “”
        .cmbMaterials4.Value = “”
        .cmbMaterials5.Value = “”
        .cmbMaterials6.Value = “”
        .cmbMaterials7.Value = “”

        ‘Creating a dynamic name for department

        Sheet3.Range(“A2”, Sheet3.Range(“A” & Application.Rows.Count).End(xlUp)).Name = “Dynamic”

        .cmbMaterials1.RowSource = “Dynamic”
        .cmbMaterials2.RowSource = “Dynamic”
        .cmbMaterials3.RowSource = “Dynamic”
        .cmbMaterials4.RowSource = “Dynamic”
        .cmbMaterials5.RowSource = “Dynamic”
        .cmbMaterials6.RowSource = “Dynamic”
        .cmbMaterials7.RowSource = “Dynamic”


        .lstDatabase.ColumnCount = 10
        .lstDatabase.ColumnHeads = True

        .lstDatabase.ColumnWidths = “30,60,75,40,60,45,55,70,70”

        If iRow > 1 Then

        .lstDatabase.RowSource = “Database Table!A8:J” & iRow

        .lstDatabase.RowSource = “Database Table!A8:J8”

        End If

        End With

        End Sub

      • #2257031
        AskWoody Lounger

        Here’s the EXCEL file

        Sorry for double posting.

      • #2263065


        I’ve just seen your post. Interesting request.

        I like your Form. Nice layout. I liked the way you named the controls on the Form. I appreciate cementics.

        I fixed the name assignment for the [Reset] command button on your Form.

        I have done some initial coding to start you off, nothing concrete, just a start.

        In the sample file attached, extract the Excel file to a folder of you choice, then load it into Excel for testing.

        I have added a button [Show Form] in the top-left-corner of worksheet [Database Table].

        Click it to start the Form.

        You can experiment with selecting items from the Materials dropdowns on the Form, and placing values in the relevant textboxes.

        When you click the [Save] button, it should update to the next data row on the worksheet [Database Table], using column B for finding the next row to post to.

        The save routine could be adjusted to post to the row that corresponds to ‘today’s date’ etc etc etc.

        I haven’t done anything with the database list on the bottom of your Form. Perhaps some of our hardened experts here could contribute.

        I have tried to document each line of my vba coding to help you follow what I did.

        Please let me know if this helps you get started.

        If not, let me know anyway



    Viewing 2 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, no politics or religion.

    Reply To: [H] Multiple CommandBox

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.