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?
Printable View
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?
Private Sub UserForm_Activate() is where I load my listboxs/combos/what not.
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.
Code:Private Sub ComboBox1_Change()
With Me.ComboBox1
.AddItem "TMMK-VEH"
.AddItem "TMMI"
.AddItem "TMMC"
End With
End Sub
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.
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.
Code:Option Explicit
Private Sub UserForm_Initialize()
'Put your code here to load your control or whatnot
End Sub
What is the difference between Activate and Initalize?
OK I am a little confused...
Here is what I have
Userform1 » Combobox1
When I double click on the Combobox1 it takes me to
located inside Module1Code:Private Sub ComboBox1_Change()
End Sub
I did get the combo_list to populate
But why does it take me to the change event?
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.
I have gotten this to work thanks for all the direction
What did you end up doing?
Activate is when the UserForm becomes the active form either from first time displaying or the user reselecting it when displayed modelessly.Quote:
Originally Posted by Hack
Quote:
Originally Posted by RobDog888
Here is the code behind the userform
Here is the form load eventCode: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 Application code:Code: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
^^^ I have a very unique task from here ^^^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
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 :D
You should convert your 2 lengthy "If" blocks to "Select Case" blocks that will be easier to code and look:
You did this twice:Code: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
Code:ElseIf myNamc = "TMMTX" Then
NAMC = 6
After I do this can I the if then else statements in the other module?
This actually is a mistake they all have a unigue number.Code: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
So I guess I just follow the format of the others.