RESOLVED > Excel VB » Userform » Combobox

[ggodwin]

Should be a simple question.
I am creating a combo box on a user form in Excel.
Where to I add the choices for the combo box that create the list?

[Hack]

Private Sub UserForm_Activate() is where I load my listboxs/combos/what not.

[ggodwin]

Here is what I was trying to do.
But not working...

Am I close?. When I double click on the form in design view it give me the code window of Private Sub ComboBox1_Change()

So I tried this..

My main goal once the form is filled in I want to assign this value to "myNAMC" variable on the submit event.

Private Sub ComboBox1_Change()
With Me.ComboBox1
    .AddItem "TMMK-VEH"
    .AddItem "TMMI"
    .AddItem "TMMC"
End With
End Sub

[Hack]

First, the change event is only going to fire if something changes with the control. If there is nothing in the control (and there isn't) then there is nothing to change, therefore, the event will never fire.

Move your additem code to the userform's activate event and completely remove it from the Combo's change event.

[RobDog888]

Use the Initialize event as its the equilivalent to the Form_Load in VB6 if you are familiar with that.

the double clicking of the userform will bring up the code behind for it and write out its default event which is the Change event. Not really what you are needing ot use in your situation.

Option Explicit

Private Sub UserForm_Initialize()
    'Put your code here to load your control or whatnot
End Sub

[Hack]

What is the difference between Activate and Initalize?

[ggodwin]

OK I am a little confused...

Here is what I have

Userform1 » Combobox1

When I double click on the Combobox1 it takes me to

Private Sub ComboBox1_Change()

End Sub

located inside Module1

I did get the combo_list to populate

But why does it take me to the change event?

[anhn]

That is the behaviour of VBA code editor, sometimes it is quite annoying.

If no event Sub existed in code module for the control that you double click, a default event Sub will be created for that control. That is not neccessary you have to use that event. If you don't want it (in many cases), just make another event Sub then delete the unwanted Sub.

[ggodwin]

I have gotten this to work thanks for all the direction

[Hack]

What did you end up doing?

[RobDog888]

What is the difference between Activate and Initalize?

Activate is when the UserForm becomes the active form either from first time displaying or the user reselecting it when displayed modelessly.

Use the Initialize event as its the equilivalent to the Form_Load in VB6 if you are familiar with that.

[ggodwin]

Here is the code behind the userform

Private Sub SubmitButton_Click()

'Assign the user inputs to variables that will be used in the Web application.
'Tell VBA what to do when the user does not input a date format
On Error GoTo NotADate

    myNamc = txt_Namc
    myType = txt_Type
    myOccurenceDate = Format(CDate(txt_OccurenceDate), "mm/dd/yyyy")
    myFrom = txt_From
    myTo = txt_To
    myPartNumber = txt_PartNumber
    myScrapTag = txt_ScrapTag
    myProblemTracking = txt_ProblemTracking
    myDisputeRequestDate = Format(CDate(txt_DisputeRequestDate), "mm/dd/yyyy")
    myReason = txt_Reason

    'Inform the user that this program is operating correctly.
    MsgBox "Please wait, SKPI is Loading!"
    ' I am just testing my variables here, this will be eliminated later.
    MsgBox "OccurenceDate: " & (myOccurenceDate) & " " & "NAMC: " & (myNamc) & " " & "Type: " & (myType) & " " & "From: " & (myFrom) & " " & "To: " & (myTo) & " " & "PartNumber: " & (myPartNumber) & " " & "ScrapTag: " & (myScrapTag) & " " & "ProblemTracking: " & (myProblemTracking)
    MsgBox "DisputeRequestDate: " & (myDisputeRequestDate) & " " & "Reason: " & (myReason)
    Application.Run "VBA_SKPI_Dispute.xls!toyotasupplier_LOGIN"
'Exit the Sub
Exit Sub
'Handle non Date format input with NotADate
NotADate:
MsgBox "ERROR - You must enter valid formats for your data"
End Sub
Private Sub UserForm_Initialize()
    With Me.txt_Namc
    .AddItem "TMMK-VEH"
    .AddItem "TMMK-PWT"
    .AddItem "TMMI"
    .AddItem "TMMC"
    .AddItem "TMMTX"
    .AddItem "TABC"
    .AddItem "NUMMI"
    .AddItem "TMMBC"
    .AddItem "TMMAL"
    .AddItem "TMMNK"
End With
    With Me.txt_Type
    .AddItem "All"
    .AddItem "Scrap"
    .AddItem "Re-Work"
    .AddItem "Use As Is"
End With

End Sub

Here is the form load event

Public myOccurenceDate As String, myNamc As Variant, myType As Variant, myFrom As Integer, myTo As Integer, myPartNumber As Variant, myScrapTag As Variant, myProblemTracking As Variant, myDisputeRequestDate As String, myReason As Variant
Private Sub userform1_load()
Option Explicit
'Load the user form and establish the value of the user inputs variables.
Dim txt_OccurenceDate As String
Dim txt_Namc As Variant
Dim txt_Type As Variant
Dim txt_From As Integer
Dim txt_To As Integer
Dim txt_PartNumber As Varient
Dim txt_ScrapTag As Varient
Dim txt_ProblemTracking As Varient
Dim txt_DisputeRequestDate As String
Dim txt_Reason As Varient

    txt_OccurenceDate.Text = Format(CDate(txt_OccurenceDate), "mm/dd/yyyy")
    txt_Namc.Text = ""
    txt_Type.Text = ""
    txt_From.Text = ""
    txt_To.Text = ""
    txt_PartNumber.Text = ""
    txt_ScrapTag.Text = ""
    txt_ProblemTracking.Text = ""
    txt_DisputeRequestDate.Text = Format(CDate(txt_DisputeRequestDate), "mm/dd/yyyy")
    txt_Reason.Text = ""
End Sub

Here is the Application code:

Public Sub toyotasupplier_LOGIN()
Dim QPR As Object
Dim lnk As Object
Dim frm As Object
Dim start As Object
Dim fin As Object
Dim drp1 As Object
Dim drp2 As Object
Dim src1 As Object
Dim p1 As Variant
Dim NAMC As Integer
Dim ScrapType As Integer



Set QPR = CreateObject("InternetExplorer.application")
    
    QPR.Visible = True
    
    QPR.navigate "https://www.portal.toyotasupplier.com/wps/myportal/"
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
    
    With QPR.document.forms("Login")
        .User.Value = "******"
        .Password.Value = "*****"
        .submit
    End With

    Application.Wait Now + TimeSerial(0, 0, 11)

    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/")
    
    Application.Wait Now + TimeSerial(0, 1, 30)
    
    If myNamc = "TMMK-VEH" Then
        NAMC = 3
    ElseIf myNamc = "TMMK-PWT" Then
        NAMC = 3
    ElseIf myNamc = "TMMC" Then
        NAMC = 5
    ElseIf myNamc = "TMMTX" Then
        NAMC = 6
    ElseIf myNamc = "TABC" Then
        NAMC = 7
    ElseIf myNamc = "NUMMI" Then
        NAMC = 8
    ElseIf myNamc = "TMMI" Then
        NAMC = 9
    ElseIf myNamc = "TMMTX" Then
        NAMC = 6
    ElseIf myNamc = "TMMBC" Then
        NAMC = 10
    ElseIf myNamc = "TMMAL" Then
        NAMC = 11
    ElseIf myNamc = "TMMNK" Then
        NAMC = 12
    End If
    
    If myType = "All" Then
        ScrapType = 1
    ElseIf myType = "Scrap" Then
        ScrapType = 2
    ElseIf myType = "Re-Work" Then
        ScrapType = 3
    ElseIf myType = "Use As Is" Then
        ScrapType = 4
    End If
    
    
    Set lnk = QPR.document.Links(NAMC) ' 4=TMMK-VEH,3=TMMK-PWT,5=TMMC,6=TMMTX,7=TABC,8=NUMMI,9=TMMI,10=TMMBC,11=TMMAL,12=TMMNK
    
    lnk.Click
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop

    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/SkpiGatewayServlet?jadeAction=NCPARTS_SEARCH")
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
  
    Set frm = QPR.document.forms("form1")
    Set dwn = QPR.document.forms("page")
        
    Set start = frm.all("SKPI_SEARCH_START_DATE_KEY")
    start.Value = myOccurenceDate
    
    Set finish = frm.all("SKPI_SEARCH_END_DATE_KEY")
    finish.Value = myOccurenceDate
    
    Set drp2 = frm.all("SKPI_SEARCH_NC_TYPE_KEY")
    drp2.Item(ScrapType).Selected = True
    
    Set src1 = frm.all("Submit")
    
    src1.Click
       
End Sub

^^^ I have a very unique task from here ^^^

src1.Click will take me to a page that has a few records (no more than 20)
Each record has a check box assiciated with it. I have to search that page for myScrapTag and select the corresponding checkbox. I hope this is possible. But this seems to be working very well on multiple PC's.

Thank you all

[anhn]

You should convert your 2 lengthy "If" blocks to "Select Case" blocks that will be easier to code and look:

    Select Case myNamc
        'Case "TMMK-VEH": NAMC = 3
        'Case "TMMK-PWT": NAMC = 3
        '-- combine 2 lines above into next line
        Case "TMMK-VEH", "TMMK-PWT": NAMC = 3
        Case "TMMC":     NAMC = 5
        Case "TMMTX":    NAMC = 6
        Case "TABC":     NAMC = 7
        Case "NUMMI":    NAMC = 8
        Case "TMMI":     NAMC = 9
        Case "TMMBC":    NAMC = 10
        Case "TMMAL":    NAMC = 11
        Case "TMMNK":    NAMC = 12
    End Select
    Select Case myType
        Case "All":       ScrapType = 1
        Case "Scrap":     ScrapType = 2
        Case "Re-Work":   ScrapType = 3
        Case "Use As Is": ScrapType = 4
    End Select

You did this twice:

    ElseIf myNamc = "TMMTX" Then
        NAMC = 6

[ggodwin]

After I do this can I the if then else statements in the other module?

    Select Case myNamc
        'Case "TMMK-VEH": NAMC = 3
        'Case "TMMK-PWT": NAMC = 3
        '-- combine 2 lines above into next line
        Case "TMMK-VEH", "TMMK-PWT": NAMC = 3

This actually is a mistake they all have a unigue number.
So I guess I just follow the format of the others.