News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • [H] Multiple CommandBox

    Posted on WSMathdarkmoon Comment on the AskWoody Lounge
    Viewing 2 reply threads
    • Author
      Posts
      • #2257020 Reply
        WSMathdarkmoon
        AskWoody Lounger

        Scenario:

        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 = “”
        .cmbMaterials1.Clear
        .cmbMaterials2.Clear
        .cmbMaterials3.Clear
        .cmbMaterials4.Clear
        .cmbMaterials5.Clear
        .cmbMaterials6.Clear
        .cmbMaterials7.Clear

        ‘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
        Else

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

        End If

        End With

        End Sub

        Attachments:
      • #2257031 Reply
        WSMathdarkmoon
        AskWoody Lounger

        Here’s the EXCEL file

        Sorry for double posting.

      • #2263065 Reply
        zeddy
        AskWoody_MVP

        Hi

        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

        zeddy

        Template-V7-_-fixed-zeddy-v1a

        Attachments:
    Viewing 2 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    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.