Click here to edit title

lets learn together

Download

Userform List Box Concept

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

 

 

Private Sub CmbSubDD_Exit(ByVal Cancel As MSForms.ReturnBoolean)

dd = Val(CmbSubDD): mm = Val(CmbSubMM): yy = Val(CmbSubYY):

If dd <> 0 And mm <> 0 And yy <> 0 Then

CmbPend.Visible = False

Label32.Visible = False

Else

CmbPend.Visible = True

Label32.Visible = True

End If

End Sub

 

 

Private Sub CmbSubMM_Exit(ByVal Cancel As MSForms.ReturnBoolean)

dd = Val(CmbSubDD): mm = Val(CmbSubMM): yy = Val(CmbSubYY):

If dd <> 0 And mm <> 0 And yy <> 0 Then

CmbPend.Visible = False

Label32.Visible = False

Else

CmbPend.Visible = True

Label32.Visible = True

End If

End Sub

 

Private Sub CmbSubYY_Exit(ByVal Cancel As MSForms.ReturnBoolean)

dd = Val(CmbSubDD): mm = Val(CmbSubMM): yy = Val(CmbSubYY):

If dd <> 0 And mm <> 0 And yy <> 0 Then

CmbPend.Visible = False

Label32.Visible = False

Else

CmbPend.Visible = True

Label32.Visible = True

End If

End Sub

 

Private Sub CommandButton2_Click()

Unload Me

End Sub

Private Sub CommandButton1_Click()

'Created by Prince Sethi

' On 14 Mar. 2017

Application.DisplayAlerts = False

 

If CmbProj = "" Or CmbSale = "" Or CmbDelivery = "" Or CmbRfpDD = "" Or CmbRfpMM = "" Or CmbRfpYY = "" Or CmbRespDD = "" Or CmbRespMM = "" Or CmbRespYY = "" Or CmbActDD = "" Or CmbActMM = "" Or CmbActYY = "" Or CmbType = "" Or CmbBELead = "" Or CmbReqDD = "" Or CmbReqMM = "" Or CmbReqYY = "" Then

MsgBox "Please fill all the required field"

Exit Sub

End If

 

 

On Error Resume Next

Sheets("RawData").Select

SP = Sheets("RawData").Range("H1048576").End(xlUp).Row + 1

For i = 0 To LstREquirement.ListCount - 1

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

If LstREquirement.Selected(i) = True Then

Sheets("RawData").Range("H" & llr).Value = LstREquirement.List(i)

End If

Next i

For i = 0 To BEOwner.ListCount - 1

If BEOwner.Selected(i) = True Then

temp = temp & "," & BEOwner.List(i)

End If

Next i

 

temp = Right(temp, Len(temp) - 1)

 

ep = Sheets("RawData").Range("H1048576").End(xlUp).Row

Sheets("RawData").Range(Cells(SP, 9), Cells(ep, 9)).Value = temp

With Sheets("RawData")

.Range(Cells(SP, 1), Cells(ep, 1)).Value = CmbProj

.Range(Cells(SP, 2), Cells(ep, 2)).Value = CmbSale

.Range(Cells(SP, 3), Cells(ep, 3)).Value = CmbDelivery

dd = Val(CmbRfpDD): mm = Val(CmbRfpMM): yy = Val(CmbRfpYY):

If dd = 0 Or mm = 0 Or yy = 0 Then

Range(Cells(SP, 4), Cells(ep, 4)) = ""

Else

Range(Cells(SP, 4), Cells(ep, 4)) = DateSerial(yy, mm, dd)

End If

dd = Val(CmbRespDD): mm = Val(CmbRespMM): yy = Val(CmbRespYY):

If dd = 0 Or mm = 0 Or yy = 0 Then

Range(Cells(SP, 5), Cells(ep, 5)) = ""

Else

Range(Cells(SP, 5), Cells(ep, 5)) = DateSerial(yy, mm, dd)

End If

dd = Val(CmbActDD): mm = Val(CmbActMM): yy = Val(CmbActYY):

If dd = 0 Or mm = 0 Or yy = 0 Then

Range(Cells(SP, 6), Cells(ep, 6)) = ""

Else

Range(Cells(SP, 6), Cells(ep, 6)) = DateSerial(yy, mm, dd)

End If

.Range(Cells(SP, 7), Cells(ep, 7)).Value = CmbType

.Range(Cells(SP, 10), Cells(ep, 10)).Value = CmbBELead

dd = Val(CmbReqDD): mm = Val(CmbReqMM): yy = Val(CmbReqYY):

If dd = 0 Or mm = 0 Or yy = 0 Then

Range(Cells(SP, 11), Cells(ep, 11)) = ""

Else

Range(Cells(SP, 11), Cells(ep, 11)) = DateSerial(yy, mm, dd)

End If

dd = Val(CmbSubDD): mm = Val(CmbSubMM): yy = Val(CmbSubYY):

If dd = 0 Or mm = 0 Or yy = 0 Then

Range(Cells(SP, 12), Cells(ep, 12)) = ""

Else

Range(Cells(SP, 12), Cells(ep, 12)) = DateSerial(yy, mm, dd)

End If

 

Debug.Print DateSerial(yy, mm, dd)

.Range(Cells(SP, 13), Cells(ep, 13)).Value = CmbPend

.Range(Cells(SP, 14), Cells(ep, 14)).Value = TxtComment

End With

Call Cleardata

CmbPend.Visible = True

Label32.Visible = True

End Sub

Private Sub UserForm_Activate()

TxtUser = Application.UserName

TxtDate = Date

TxtTime = Time

End Sub

 

Sub Cleardata()

CmbProj = Clear

CmbSale = Clear

CmbDelivery = Clear

CmbRfpDD = Clear

CmbRfpMM = Clear

CmbRfpYY = Clear

CmbRespDD = Clear

CmbRespMM = Clear

CmbRespYY = Clear

CmbActDD = Clear

CmbActMM = Clear

CmbActYY = Clear

CmbType = Clear

CmbBELead = Clear

CmbReqDD = Clear

CmbReqMM = Clear

CmbReqYY = Clear

CmbSubDD = Clear

CmbSubMM = Clear

CmbSubYY = Clear

CmbPend = Clear

TxtComment = Clear

For i = 0 To LstREquirement.ListCount - 1

If LstREquirement.Selected(i) = True Then

LstREquirement.Selected(i) = False

End If

Next i

For i = 0 To BEOwner.ListCount - 1

If BEOwner.Selected(i) = True Then

BEOwner.Selected(i) = False

End If

Next i

 

End Sub

 

 

 

Categories: None

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