Click here to edit title

lets learn together

Download

UserForm-GNRP

Posted by [email protected] on March 17, 2017 at 11:20 AM

 

 

Private Sub CmbQC_Enter()

CmbQC.Clear

countqc = Sheets("Misc").Range("D1048576").End(xlUp).Row

For i = 2 To countqc

CmbQC.AddItem Sheets("Misc").Range("D" & i).Value

Next

End Sub

 

Private Sub CommandButton2_Click()

Unload Me

End Sub

 

Private Sub Frame2_Click()

 

End Sub

 

Private Sub TextBox7_Change()

 

End Sub

 

Private Sub UserForm_Activate()

Sheets("RawData").Select

TxtUID.SetFocus

TextBox9.Value = Application.UserName

TextBox8.Value = Date

lr = Sheets("Misc").Range("C1048576").End(xlUp).Row

For i = 2 To lr

CmbSubOption.AddItem Range("C" & i).Value

Next

End Sub

Private Sub CmbFLR_Enter()

CmbFLR.Clear

countqc = Sheets("Misc").Range("A1048576").End(xlUp).Row

For i = 2 To countqc

CmbFLR.AddItem Sheets("Misc").Range("A" & i).Value

Next

End Sub

Private Sub CmbCountry_Enter()

CmbCountry.Clear

lr = Sheets("Misc").Range("B1048576").End(xlUp).Row

For i = 2 To lr

CmbCountry.AddItem Sheets("Misc").Range("B" & i).Value

Next

End Sub

Private Sub CmbCountry_Change()

CmbSubOption.Clear

xx = CmbCountry.Value

sc = Sheets("RawData").Range("1:1").Find(what:=xx, after:=[Az1], LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Column

 

lngLastRow = Cells(Rows.Count, sc).End(xlUp).Row

 

For i = 2 To lngLastRow

CmbSubOption.AddItem Cells(i, sc)

Next

End Sub

Private Sub CmbSubOption_Change()

Opportunities.Clear

xx = CmbSubOption.Value

sc = Range("1:1").Find(what:=xx, after:=[Az1], LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Column

 

lngLastRow = Cells(Rows.Count, sc).End(xlUp).Row

 

For i = 2 To lngLastRow

Opportunities.AddItem Cells(i, sc)

Next

End Sub

 

 

Private Sub CommandButton1_Click()

sp = Sheet1.Range("H1048576").End(xlUp).Row + 1

For i = 0 To Opportunities.ListCount - 1

llr = Range("H1048576").End(xlUp).Row + 1

If Opportunities.Selected(i) = True Then

Sheet1.Range("H" & llr).Value = Opportunities.List(i)

End If

Next i

 

 

ep = Sheet1.Range("H1048576").End(xlUp).Row

 

Range(Cells(sp, 1), Cells(ep, 1)).Value = TxtUID.Value

Range(Cells(sp, 20), Cells(ep, 20)).Value = TxtNP.Value

Range(Cells(sp, 2), Cells(ep, 2)).Value = CmbFLR.Value

 

' Date Combos

 

dd = UserForm1.CmbFdd.Value

mm = UserForm1.CmbFMM.Value

yy = UserForm1.CmbFYY.Value

dd = Val(dd)

mm = Val(mm)

yy = Val(yy)

Range(Cells(sp, 3), Cells(ep, 3)) = DateSerial(yy, mm, dd)

 

'-

 

 

Range(Cells(sp, 4), Cells(ep, 4)).Value = "QC Name"

'----------------

 

ddd = UserForm1.CmbQDD.Value

mmm = UserForm1.CmbQMM.Value

yyy = UserForm1.CmbQYY.Value

ddd = Val(ddd)

mmm = Val(mmm)

yyy = Val(yyy)

 

Range(Cells(sp, 5), Cells(ep, 5)).Value = DateSerial(yyy, mmm, ddd)

'-----------------

 

Range(Cells(sp, 6), Cells(ep, 6)).Value = CmbCountry.Value

Range(Cells(sp, 7), Cells(ep, 7)).Value = CmbSubOption.Value

 

 

Range(Cells(sp, 9), Cells(ep, 9)).Value = ComboBox7.Value

Range(Cells(sp, 10), Cells(ep, 10)).Value = ComboBox8.Value

Range(Cells(sp, 11), Cells(ep, 11)).Value = ComboBox9.Value

Range(Cells(sp, 12), Cells(ep, 12)).Value = ComboBox10.Value

Range(Cells(sp, 13), Cells(ep, 13)).Value = ComboBox11.Value

Range(Cells(sp, 14), Cells(ep, 14)).Value = ComboBox12.Value

 

MsgBox " The Data has been submitted"

 

Dim Ctrl As Control

For Each Ctrl In Me.Controls

If TypeOf Ctrl Is MSForms.ComboBox Then

On Error Resume Next

Ctrl.Clear

End If

Next Ctrl

 

 

For Each Ctrl In Me.Controls

If TypeName(Ctrl) = "TextBox" Then

Ctrl.Text = ""

End If

Next Ctrl

TxtUID.SetFocus

End Sub

 

 

 

 

 

 

 

Categories: Userform, Loops

Post a Comment

Oops!

Oops, you forgot something.

Oops!

The words you entered did not match the given text. Please try again.

Already a member? Sign In

0 Comments