User Form Help

[prm11876]

I normally wouldn't ask for help on a forum (mostly because I'm wickedly stubborn) but whichever part of my brain VBA uses is absolutely fried.

I have a workbook with the identical columns and rows on 52 different worksheets. Each worksheet is named for each Wednesday of the year.

Here are the items from the user form and what I need them to do

ComboBox1: Contains the dates for every Wednesday. I've already created the available selections. When the user makes their selection, I need it to correspond to the worksheet with the same name / date


ComboBox2: Contains the names of 23 different facilities. Selections have already been created and are working fine. When the user makes their selection from this box, it should correspond to the same name (text) in column A, rows 2-9, 11-16 and 18-26. This does not vary from sheet to sheet. All are identical.


TextBoxes 1 through 6: The user will enter a number in each of these boxes. I need the number to populate into the corresponding column for that facility. (i.e. facility 1, value 1 should populate in Cell B2, Facility 1, Value 2, Cell B3 and so on):

The user will only be choosing one facility per form, so (in theory) it should be relatively straight forward, like I said, brain is officially deep fried at this point and I just can't seem to get it right. I know I should write the code to my "submit" command button, but something is telling me that I should be writing code to the text boxes as well.

So basically, what I need is this:

how to activate the sheet based on ComboBox1 Selection
how to select / write to the row based on ComboBox 2 selection
how to make Values 1 to 6 correspond to the columns of the selected row

project.jpg

[mdmackillop]

Can you post a workbook containing your userform an a few worksheets for testing/name checking. Go Advanced/Manage Attachments.

[prm11876]

Thank you for the reply mdmackillop. Workbook attached.

[Paul_Hossler]

I think all you need is an event handler for [Submit] that takes the date and facility from the UF and puts the values on the correct ws in the correct row

No error handling, but something like this

Oh, I did add a [Cancel] button

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim iFac As Long
    
    Set ws = Worksheets(Me.ComboBox1.Value)
    iFac = Application.WorksheetFunction.Match(Me.cboFacility.Value, ws.Columns(1), 0)
    ws.Cells(iFac, 2).Value = Me.InpatientUnder7.Value
    ws.Cells(iFac, 3).Value = Me.InpatientOver7.Value
    ws.Cells(iFac, 4).Value = Me.OutpatientRefToCodingOver7.Value
    ws.Cells(iFac, 5).Value = Me.OutpatientRefToCodingOver7.Value
    ws.Cells(iFac, 6).Value = Me.OutpatientRecodeReject.Value
    ws.Cells(iFac, 7).Value = Me.OutpatientSuspPending.Value
End Sub

Private Sub CommandButton2_Click()
    Me.Hide
    Unload Me
End Sub