Sheet cell data input from 2 ComboBox's

[WayneAli]

Hi all,
Used to tinker in a very amateur way with VBA many years ago and have discovered its not like riding a bike, some years on and I cant remember a thing.

I'm trying to put a sheet together that shows staff course qualifications and completion dates, the "course name" headers are in row 4 and the staff names are in column B.
I have a simple form that users can use to update their completion dates, this form comprises of 2 combo box's, the first (ComboBox1) lists staff names and the second (ComboBox2) lists course qualifications. Also on the form is a textbox (TextBox1) for the user to input a date of course completion, finally a submit button.

The theory behind the form is that when the user hits submit (CommandButton1), column B is searched for the users name selected in ComboBox1 and row 4 is searched for the course title from ComboBox2, then the completion date from TextBox1 is inputted in the relevant cell on the sheet ("MOK i-Learns")

This is the code that I have come up with but am getting a "Compile Error: For without Next"

Any help would be greatly appreciated. Thanks

Private Sub CommandButton1_Click()
Dim elearn_name As String
Dim crew_name As String
Dim i As Integer
Dim j As Integer
Dim lastRow As Long
Dim lastCol As Long

elearn_name = ComboBox2
crew_name = ComboBox1
lastRow = Worksheets("MOK i-Learns").Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Worksheets("MOK i-Learns").Cells(Columns.Count, 1).End(xlLeft).Column
For i = 4 To lastRow
For j = 2 To lastCol

If Worksheets("MOK i-Learns").Cells(i, 2).Value = crew_name And Worksheets("MOK i-Learns").Cells(4, j).Value = elearn_name Then
Worksheets("MOK i-Learns").Cells(i, j).Value = TextBox1.Text
End If
Next

End Sub

 

[WayneAli]

Just having a think on this and wondering if it would be easier to search row 4 of the sheet for a course match from "ComboBox2" and return the column number, then search column 2 for a match on staff name from ComboBox1. Then use these to input the date to the relevant cell.

I have also just edited the code above to

elearn_name = ComboBox2.value
crew_name = ComboBox1.value

 

[dmt32]

Hi,
see if this update to your code will do what you want

Private Sub CommandButton1_Click()
    Dim elearn_name    As Variant, crew_name As Variant
    Dim r              As Variant, c As Variant
    Dim ws             As Worksheet
 
    On Error GoTo myerror
    Set ws = ThisWorkbook.Worksheets("MOK i-Learns")
 
    crew_name = Me.ComboBox1.Value
    If Len(crew_name) = 0 Then Exit Sub
 
    elearn_name = Me.ComboBox2.Value
    If Len(elearn_name) = 0 Then Exit Sub
 
    r = Application.Match(crew_name, ws.Columns(2), 0)
    If IsError(r) Then Err.Raise 744, , crew_name & " Not Found"
    c = Application.Match(elearn_name, ws.Rows(4), 0)
    If IsError(c) Then Err.Raise 744, , elearn_name & " Not Found"
 
    ws.Cells(CLng(r), CLng(c)).Value = DateValue(Me.TextBox1.Text)
 
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Private Sub TextBox1_Change()
    'allow only dates to submitted
    Me.CommandButton1.Enabled = IsDate(Me.TextBox1.Value)
End Sub

I have included an additional code to try to ensure user enters a valid date in TextBox1 otherwise Commandbutton will be disabled

Dave

 

[mumps]

Try this macro (no looping):

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim userName As Range, title As Range, desWS As Worksheet
    Set desWS = Sheets("MOK i-Learns")
    Set userName = desWS.Range("B:B").Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not userName Is Nothing Then
        Set title = desWS.Rows(4).Find(ComboBox2.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not title Is Nothing Then
            desWS.Cells(userName.Row, title.Column) = TextBox1.Value
        Else
            MsgBox ("Course title not found.")
            Exit Sub
        End If
    Else
        MsgBox ("User name not found.")
    End If
    Unload Me
    Application.ScreenUpdating = False
End Sub

 

[WayneAli]

Brilliant, thank you Dave, this works perfectly.

I am trying to put together quite an intricate sheet where staff can enter dates that they complete an online course, each course needing to be completed either 6, 12 or 24 monthly and using cell formulas, return days remaining until they need to complete the course again. There's also a form so that learners can complete courses as a group and managers can input group course completion dates via a form and checkbox's. Finally, a sheet (or form that draws data from the sheet) that a manager can review to see when staff completed a course and enter the "record of learning" details into another program.

What seemed to be a theoretically straight forward idea, consisting of a spreadsheet, a hidden "data" spreadsheet and a handful of userforms is becoming a bit of a headache. All that said, that hardest bit I have found is trying to search and identify columns and rows and input data to the sheets the forms as above, this will help a long way.

Thanks again

 

[dmt32]

Hi,
Userforms can be hard work (even for experience developers) more so for complex projects as you need to code every aspect yourself - If you are trying to create a database management system (DBMS) then may want to look at a more suitable application like Access.

You have been shown a couple of search methods here that do what you want which should be helpful in developing your project further

Glad we were able to help & appreciate your feedback

Dave