Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Checking against rowsource (MSO 2003 wxpp)

    Hi
    I am using a combo box that has entries from a specified rowsource on another sheet. I want to evaluate the entry in the combo box, this may vary form that listed in rowsource, against the entries in the rowsource, if it matches one, find the match on the current worksheet and deliver data.
    Can this be done?

    cheers
    cheers

    Phil Carter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking against rowsource (MSO 2003 wxpp)

    Is it a combo box from the Forms toolbar or from the Control Toolbox?
    Could you attach a stripped down copy of the workbook? That would make it easier to test out things.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Checking against rowsource (MSO 2003 wxpp)

    Hans hi

    The combo box is from the control toolbox. See attached

    I am in the initial stages of setting this up, converting form another template. The code is attached to the "frmUpdate" form.

    What I am trying to achieve is, an employee attends training, this is entered in the update form, a check is made to evaluate if that training all ready has been recorded, if it has then find the training on the employee worksheet and enter the date and duration in the next empty cell next to the training. If the training does not exist goto the next empty cell in column "A" and enter the data (this works works fine).

    cheers
    cheers

    Phil Carter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking against rowsource (MSO 2003 wxpp)

    You have a problem since there are formulas in column N of the Template and employee sheets. If you enter a few dates for the same training, you'll overwrite the formula. I would put the (modified) formulas in column B. The code for the command button can then look like this:

    Private Sub cmdUpdate_Click()
    Dim strEmployeeName As String
    Dim strTrainingType As String
    Dim i As Integer
    Dim rng As Range

    strEmployeeName = Me.txtEmployeeName
    strTrainingType = Me.cmbTrainingType

    If strTrainingType = "" Then
    Me.cmbTrainingType.SetFocus
    MsgBox "No training entered!"
    Exit Sub
    End If

    Application.ScreenUpdating = False

    With Worksheets(strEmployeeName)
    Set rng = .Range(.Range("A3"), .Range("A65536").End(xlUp))
    Set rng = rng.Find(What:=strTrainingType, _
    LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then
    Set rng = .Range("A65536").End(xlUp).Offset(1, 0)
    rng.Value = strTrainingType
    End If
    Set rng = .Range("IV" & rng.Row).End(xlToLeft)
    rng.Offset(0, 1).Value = Me.Calendar1
    rng.Offset(0, 2).Value = Me.txtNumberDays

    On Error Resume Next
    For i = 1 To Me.Controls.Count
    Me.Controls(i) = ""
    Next i
    On Error GoTo 0

    Me.Calendar1 = Date
    End With

    Application.ScreenUpdating = True

    Unload frmUpdate
    End Sub

    I have made the indentation consistent, removed unnecessary duplication and avoided selecting cells. I also moved the line 'On Error Resume Next' down to where it is needed.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Checking against rowsource (MSO 2003 wxpp)

    Hans thank you very much

    That works just great. Although it took a while in testing as I had some data in rows A50:A60. Couldn't figure out why I couldn't see the manually entered course but the date and duration showed.

    Once again your a legend.
    cheers

    Phil Carter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •