Vba userform to place data

Question

Ok guys I have an excel workbook which tracks scoring information. There are 12 sheets, one for each month. On these sheets b2:b54 contain office locations. F1:I1 contains titles "audit 1" "audit 2" and so on.

When the book opens, a userform initializes with 12 buttons for the months. I'll use March as my example. When the user clicks the March button it takes them to the March sheet.

Once the March sheet opens, another userform initializes.

This userform contains 2 list boxes, 1 textbox, and a submit button.

The idea is that the user would choose from the office location from listbox 1 which would determine the row to place the score on.

The user would select the type of audit from listbox 2 which would determine the column to place the score in.

The user would enter the score in textbox1 which would be the data needed for entry.

Upon pressing the "submit" button, the score would be placed at the intersection of the chosen office and type of audit.

I'm trying to work at this one piece at a time. So here's my first issue:

I can't figure out how to add the data from textbox1 to the intersection of the row containing the selection from listbox 1 and the column containing the selection listbox 2.

The following code is what I'm attempting at the moment with no luck:

Private Sub Marsubmit_Click()

Dim MarR As String

Dim MarC As Integer

    Dim M_A As String

        Dim M_B As String

        Dim M_S As String

        Dim M_C As Range

    Dim C As Integer

    Dim R As Integer



    R = 2

    C = 2







        M_S = Marscbx.Value

            M_B = Formboxmar.Value

                M_A = Officeboxmar.Value

                MarC = 2



                If M_B = ActiveSheet.Range("F1").Value Then MarC = MarC + 4

                If M_B = ActiveSheet.Range("G1").Value Then MarC = MarC + 5

                If M_B = ActiveSheet.Range("H1").Value Then MarC = MarC + 6

                If M_B = ActiveSheet.Range("I1").Value Then MarC = MarC + 7







               If M_A = ActiveSheet.Cell(C, R).Value Then MarR = "True"

               If MarR = "True" Then M_C = ActiveSheet.Cell(C, MarC)

            Do Until MarR = "True"

               C = C + 1

               Loop



                ActiveSheet.Range(M_C).Value = M_S

Can't look closer to your problem right now, but wouldn't be easier to have only one UserForm with three ComboBoxes (month, location, audit), the input and the submit? Of course you should change ActiveSheet with Worksheet(ComboMonth.value) or similar. I will try later if there are not any other responses. — CMArg
@CMArg the reason I've separated the userforms is that once I get this part to work and learn a little more about vba I plan to add a few additional items and functions to the userform that selects the month. In your opinion is vba a good first language to learn? I'm dabbling in a little c# and c++ also but so far vba has been the easiest for me to understand. I'm very new to this so any advice would be appreciated. — user7460372

CMArg CMArg · Accepted Answer · 2017-01-26T01:07:03

A very shrunken example. ListIndex indicates the number of the selected item of the ComboBox, starting at 0. Just change both +1 to get to the proper row/column. For example, if Audit1 goes to column F, Audit2 to G and Audit3 to I, then in the ComboBox for audit write ComboBoxAudit.ListIndex + 6.

Private Sub Marsubmit_Click()
    Dim C1 As Integer
    Dim C2 As Integer
    Dim T1 As String

    C1 = ComboBox1.ListIndex + 1
    C2 = ComboBox2.ListIndex + 1
    T1 = TextBox1.Value

    Worksheets("Mar").Cells(C1, C2) = T1
End Sub

Vba userform to place data

1 Answers