CODEWITHSUNDEEP
exceluserformvba
Mansour
Mansour

Reputation: 95

populate a textbox based on 2 comboboxes

I have two "combo boxes" and one "txtbox" in my userform, In workbook "sheet1" i have names on column A and Month on column B and columns C to N are Jan. to Dec. which contain production hours for each name/specific month

-cboName
-cboMonth
-txtHours

I use below code to populate txtHours

Private Sub cboName_Change()
    Dim EName As String
    Dim Row, Col As Integer
    EName = Me.cboName.Text        
    If EName <> "" Then
        With Application.WorksheetFunction
           Row = .Match(EName, Sheets("sheet1").Range("A2:A100"), 0)  
           GetMonthNum (Me.cboMonth.Text)
           txtShiftHours.Value = Sheets("sheet1").Cells(Row + 1, Col + 3)
        End With
    End If
End Sub

Private Sub GetMonthNum(Month As String)

    Select Case Month
        Case Jan
            Col = 3
        Case Feb
            Col = 4
        Case Mar
            Col = 5
        Case Apr
            Col = 6
        Case May
            Col = 7
        Case June
            Col = 8
        Case July
            Col = 9
        Case Aug
            Col = 10
        Case Sept
            Col = 11
        Case Oct
            Col = 12
        Case Nov
            Col = 13
        Case Dec
            Col = 14
        End Select
        End Sub

but regardless of month selection on cboMonth,txtProduct is populated with column 3 cuz this line

txtShiftHours.Value = Sheets("sheet1").Cells(Row + 1, Col + 3)

Please help me thanks

Upvotes: 0

Views: 649

Answers (2)

YowE3K
YowE3K

Reputation: 23974

You had several issues:

  • Your Case statements were checking the value of the String variable Month against undefined variables such as Jan, Feb, etc. This should have been checking against String literals such as "Jan", "Feb", etc.
  • In your GetMonthNum subroutine, you were assigning a value to an undefined variable Col.
  • In your cboName_Change subroutine you were using a variable Col which had never been assigned a value, so it would have had the default value of zero.

You also had some minor issues, which wouldn't have stopped your code from working, but could lead to problems down the track:

  • You used several variable names (Row, Month) which are the same as built in Functions / Properties within VBA. This is usually a very bad idea.
  • You declared Row as a Variant, despite declaring Col as an Integer.
  • It's a good idea to define row and column variables to be Long rather than Integer - the maximum number of rows in Excel is now 1048576, but an Integer can only hold numbers up to 65536.

It is also a good idea to always include the Option Explicit statement as the first line of each of your code modules. This tells the compiler to check that all your variables have been declared, and thus prevents many typos and attempts to use variables in one subroutine which are local to another subroutine.

I have refactored your code and hopefully it should now work.

Option Explicit

Private Sub cboName_Change()
    Dim EName As String
    Dim RowNum As Long, ColNum As Long
    EName = Me.cboName.Text        
    If EName <> "" Then
        With Application.WorksheetFunction
           RowNum = .Match(EName, Sheets("sheet1").Range("A2:A100"), 0)  
           ColNum = GetMonthNum(Me.cboMonth.Text) + 2
           txtShiftHours.Value = Sheets("sheet1").Cells(RowNum + 1, ColNum)
        End With
    End If
End Sub

Private Function GetMonthNum(Mth As String) As Long
    Select Case Mth
        Case "Jan":  GetMonthNum = 1
        Case "Feb":  GetMonthNum = 2
        Case "Mar":  GetMonthNum = 3
        Case "Apr":  GetMonthNum = 4
        Case "May":  GetMonthNum = 5
        Case "June": GetMonthNum = 6
        Case "July": GetMonthNum = 7
        Case "Aug":  GetMonthNum = 8
        Case "Sept": GetMonthNum = 9
        Case "Oct":  GetMonthNum = 10
        Case "Nov":  GetMonthNum = 11
        Case "Dec":  GetMonthNum = 12
    End Select
End Function

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33672

You could use some of Excel's Date & Time built-in functions, to replace your entire Private Sub GetMonthNum(Month As String) with the 1 line of code below:

ColNum = Month(DateValue("1/" & Me.cboMonth.Text & "/2017")) + 2

Explanation: since your cboMonth Combo-Box has month strings in the mmm month format. If you select "Feb", then when you get to this section ("1/" & Me.cboMonth.Text & "/2017") you are getting "1/Feb/2017".

When adding DateValue before, you get 1/Feb/2017, and when adding the Month before, the result is 2.

Upvotes: 0

Related Questions