vba excel combo box in userform

0
votes

Basically the module Onboarding is asking the path of the tracker i want to update. I am updating details in the sheet1 of the tracker. I am setting the values of fields in userform 'OnboardingForm' to blank(so that the values entered last time to the form is not visible when I am opening the form this time. Now I am opening the form 'OnboardingForm' and entering values in the subsequent fields. I have put a check button in my userform 'OnboardingForm' which is invisible to the front end user. Now in the tracker there is a sheet named 'Project Tracks' which has information of all current projects Once the submit button is clicked the control will go to the tracker's 'Project Tracks' sheet. It will validate the track entered in the userform 'OnboardingForm' with the tracks present in the tracker's 'Project Tracks' sheet. Once found the other details against that particular track will get fetched to the tracker's sheet1(this I have done so that I will not have to enter values manually to the userform 'OnboardingForm' so that the form looks simple). There are no chances of the track not matching.

Now one command button new track has been put in my current userform 'OnboardingForm'. Once clicked this will take the control to the userform2 'ProjectTracksForm'.This is basically put so that if I am adding a new track, the form takes the detail and enters in the tracker's 'Project Tracks' sheet.

Question 1> My current userform's Track button is a combo box. How do I add values in the dropdown from the tracker's 'Project Tracker' sheet to the dropdown.

Question 2> Once I add a new track in userform2 'ProjectTracksForm',submit and then when I come back to my current userform 'OnboardingForm' that added track should be shown in the dropdown of Track combo box. Please find below my piece of code.

This is my module for onboarding

Public Sub OnBoarding()
    On Error GoTo ErrorHandler
    Dim Owb As Object
    Dim ran As Range
    strTalentTrackerPath = shTracker.Cells(2, 2).Value

    'Default the form values to null
    With OnboardingForm
        .combTrackofWork.Value = ""
        .txtFirstName.Text = ""
        .txtLastName.Text = ""
        .combResCat.Value = ""
        .combBFTE.Value = ""
        .combLevel.Value = ""
        .combLocType = ""
        .txtAccessInfo.Text = ""
    End With
    OnboardingForm.Show
    SetFocus.combTrackofWork

    With OnboardingForm
        'Details to be entered in the form'
        strTOW = Trim$(.combTrackofWork.Value)
        strFN = Trim$(.txtFirstName.Text)
        strLN = Trim$(.txtLastName.Text)
        strResCat = Trim$(.combResCat.Value)
        strBilFTE = Trim$(.combBFTE.Value)
        strLevel = Trim$(.combLevel.Value)
        strLocType = (.combLocType.Value)
        strAccessInfo = (.txtAccessInfo.Text)
    End With

    If OnboardingForm.chkOKButtonClick = True Then
        Set oExcel = New Excel.Application
        strMyFolder = strTalentTrackerPath
        Set Owb = oExcel.Workbooks.Open(strMyFolder)
        IntRowCount = Owb.Sheets(1).UsedRange.Rows.Count
        With Owb.Sheets(1)
            With Owb.Sheets("Project Tracks")
                IntTrackRowCount = .UsedRange.Rows.Count
                For IntCurrentRow = 1 To IntTrackRowCount
                    If .Cells(IntCurrentRow, 1) = strTOW Then
                        Owb.Sheets(1).Cells(IntRowCount + 1, OnboardingFormcolumn.colTrackofWork) _
                                = .Cells(IntCurrentRow, ProjectTrackscolumn.colTrack)
                        Owb.Sheets(1).Cells(IntRowCount + 1, OnboardingFormcolumn.colBPO) = .Cells _
                                                                                            (IntCurrentRow, ProjectTrackscolumn.colBPO)
                        Owb.Sheets(1).Cells(IntRowCount + 1, OnboardingFormcolumn.colCostCenter) _
                                = .Cells(IntCurrentRow, ProjectTrackscolumn.colCostCenter)
                        Owb.Sheets(1).Cells(IntRowCount + 1, OnboardingFormcolumn.colGroup) _
                                = .Cells(IntCurrentRow, ProjectTrackscolumn.colGroup)
                        Exit For
                    End If
                Next
            End With
        End With
        .Cells(IntRowCount + 1, OnboardingFormcolumn.colTrackofWork) = strTOW
        .Cells(IntRowCount + 1, OnboardingFormcolumn.colFirstName) = strFN
        .Cells(IntRowCount + 1, OnboardingFormcolumn.colLastName) = strLN
        .Cells(IntRowCount + 1, OnboardingFormcolumn.colResourceCategory) = strResCat
        .Cells(IntRowCount + 1, OnboardingFormcolumn.colBilledFTE) = strBilFTE
        .Cells(IntRowCount + 1, OnboardingFormcolumn.colLevel) = strLevel
        .Cells(IntRowCount + 1, OnboardingFormcolumn.colLocationType) = strLocType
        .Cells(IntRowCount + 1, OnboardingFormcolumn.colAccessInformation) = strAccessInfo

        Owb.Close True
        Set Owb = Nothing
        Set oExcel = Nothing
    Else
        Exit Sub
    End If
    Exit Sub

ErrorHandler:
    If Owb Is Nothing Then
    Else
        Owb.Close False
    End If
    If oExcel Is Nothing Then
    Else
        Set oExcel = Nothing
    End If
    MsgBox "Unhandled Error. Please Report" & vbCrLf & "Error Description: " & _
           Err.Description, vbExclamation
End Sub

This is for cancel button of Onboarding Form

Private Sub cmdbtn_Cancel_Click()
    OnboardingForm.Hide
    MsgBox ("No data entered")
End Sub

This is for OnboardingForm submit button

Private Sub cmdbtn_Submit_Click()
    If Trim(OnboardingForm.combTrackOfWork.Value) = ""  Then
        OnboardingForm.combTOW.SetFocus
        MsgBox ("Track of Work cannot be blank")
        Exit Sub
    End If
    If Trim(OnboardingForm.txtFirstName.Value) = "" Then
        OnboardingForm.txtFN.SetFocus
        MsgBox ("First name cannot be blank")
        Exit Sub
    End If
    If Trim(OnboardingForm.txtLastName.Value) = "" Then
        OnboardingForm.txtLN.SetFocus
        MsgBox ("Last name cannot be blank")
        Exit Sub
    End If
End Sub

Module for Project Tracks

Public Sub prjctTracks()
    On Error GoTo ErrorHandler
    Dim Owb As Object
    strTalentTrackerPath = shTracker.Cells(2, 2).Value
    With ProjectTracksForm
        .txtTOW = ""
        .txtBPO = ""
        .txtCOCE = ""
        .txtSOW = ""
        .txtGroup = ""
    End With
    ProjectTracksForm.Show
    With ProjectTracksForm
        strTOW = Trim$(.txtTOW.Text)
        strBPO = Trim$(.txtBPO.Text)
        strCOCE = Trim$(.txtCOCE.Text)
        strSOW = Trim$(.txtSOW.Value)
        strGroup = Trim$(.txtGroup.Value)
    End With
    ProjectTracksForm.Hide
    If ProjectTracksForm.chkbtn_OKclick = True Then
        Set oExcel = New Excel.Application
        strMyFolder = strTalentTrackerPath
        Set Owb = oExcel.Workbooks.Open(strMyFolder)
        With Owb.Sheets("Project Tracks")
            intUsedRowCount = .UsedRange.Rows.Count
            .Cells(intUsedRowCount + 1, Trackscolumn.colTrack) = strTOW
            .Cells(intUsedRowCount + 1, Trackscolumn.colBPO) = strBPO
            .Cells(intUsedRowCount + 1, Trackscolumn.colCostCenter) = strCOCE
            .Cells(intUsedRowCount + 1, Trackscolumn.colSOW) = strSOW
            .Cells(intUsedRowCount + 1, Trackscolumn.colGroup) = strGroup
        End With
        Owb.Close True
        Set Owb = Nothing
        Set oExcel = Nothing
    Else
        Exit Sub
    End If
    Exit Sub
ErrorHandler:
    If Owb Is Nothing Then
    Else
        Owb.Close False
    End If
    If oExcel Is Nothing Then
    Else
        Set oExcel = Nothing
    End If
    MsgBox "Unhandled Error. Please Report" & vbCrLf & "Error Description: " & _
           Err.Description, vbExclamation
End Sub
1
vbaexcel
Why have you not bothered to reply to this post? - Davesexcel

1 Answers

0
votes

Question 1> My current userform's Track button is a combo box. How do I add values in the dropdown from the tracker's 'Project Tracker' sheet to the dropdown.

I am calling the combobox "ComboBox1" in this example

The Range to place in the combobox would look like this...

enter image description hereenter image description here

The code to populate the combobox would be in the Userform Module.

Private Sub UserForm_Initialize()
    Dim LstRw As Long
    Dim Rng As Range
    Dim ws As Worksheet

    Set ws = Sheets("Project Tracker")

    With ws
        LstRw = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set Rng = .Range("A2:A" & LstRw)
    End With

    ComboBox1.List = Rng.Value

End Sub

Question 2> Once I add a new track in userform2 'ProjectTracksForm',submit and then when I come back to my current userform 'OnboardingForm' that added track should be shown in the dropdown of Track combo box

When you activate your userform again, you can clear the combobox and repopulate it with the new list.

Private Sub UserForm_Activate()
    Dim LstRw As Long
    Dim Rng As Range
    Dim ws As Worksheet

    Set ws = Sheets("Project Tracker")

    With ws
        LstRw = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set Rng = .Range("A2:A" & LstRw)
    End With

    ComboBox1.Clear
    ComboBox1.List = Rng.Value

End Sub

I assume that somewhere you would have a code that will add a new item to the List in sheet("Project Tracker"),

Something like:

Private Sub CommandButton1_Click()
'THIS IS IN THE OTHER USERFORM
'add item to first blank cell in column A sheets("Project Tracker")

    Dim sh As Worksheet
    Dim LstRws As Long

    Set sh = Sheets("Project Tracker")
    With sh
        LstRws = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(LstRws, 1) = "SomeThingNew"    'whatever you are adding to the list
    End With


End Sub

The code will add something new to the list in your worksheet.

When you show the form again, the new item will be in the combobox.

enter image description here

You can either use a Button, Combobox event, textbox event, to add the item to the new list.