+ Reply to Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. 11-14-2009, 10:54 AM #1
    bong25
    bong25 is offline
    Registered User
    Join Date
    06-22-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    20

    Append data in worksheets

    Hi to all,

    I am hoping that you can give me a sample code to append data on Excel Worksheets, I have attached my project as a sample, as u can see the code I have here is only to view the Datas on Multiple worksheets but no code for appending the entries.

    Code:
    Sub UserForm_Initialize()
    Dim EmpNu As Range
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Employee's List and Details")
    Dim ws2 As Worksheet
    Set ws2 = Worksheets("CAREER PROGRESSION")
    Dim ws3 As Worksheet
    Set ws3 = Worksheets("ALLOCATIONS")
    Dim ws4 As Worksheet
    Set ws4 = Worksheets("LEAVE SCHEDULE")
   
    With ws1
        Set EmpNu = .Range(.Cells(8, 2), .Cells(.Rows.Count, 1).End(xlUp))
        ListBox1.List = EmpNu.Value
    End With
    
End Sub

Private Sub ListBox1_change()

    Dim rng    As Range
    Dim cl     As Range
    Dim sFind  As String
    Dim lRow      As Long
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Employee's List and Details")
    Dim ws2 As Worksheet
    Set ws2 = Worksheets("CAREER PROGRESSION")
    Dim ws3 As Worksheet
    Set ws3 = Worksheets("ALLOCATIONS")
    Dim ws4 As Worksheet
    Set ws4 = Worksheets("LEAVE SCHEDULE")
    

    '  Sheet1.Activate
    With ws1
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then lRow = cl.Row
            Me.lblEmpNum.Caption = .Cells(lRow, 2).Value

        Me.txtFname.Value = ws1.Cells(lRow, 3).Value
        Me.txtSName.Value = ws1.Cells(lRow, 4).Value
        Me.txtDJoin.Value = ws1.Cells(lRow, 5).Value
        Me.txtNationality.Value = ws1.Cells(lRow, 6).Value
        Me.txtCPassPNum.Value = ws1.Cells(lRow, 7).Value
        Me.txtCPassPDateIs.Value = ws1.Cells(lRow, 8).Value
        Me.txtCPassPDateExp.Value = ws1.Cells(lRow, 9).Value
        Me.txtOPassPNum.Value = ws1.Cells(lRow, 10).Value
        Me.txtOPassPDateIs.Value = ws1.Cells(lRow, 11).Value
        Me.txtOPassPDateExp.Value = ws1.Cells(lRow, 12).Value
        Me.txtCPRNum.Value = ws1.Cells(lRow, 13).Value
        Me.txtCPRDateExp.Value = ws1.Cells(lRow, 14).Value
        Me.txtRPNum.Value = ws1.Cells(lRow, 15).Value
        Me.txtRPDateIs.Value = ws1.Cells(lRow, 16).Value
        Me.txtRPDateExp.Value = ws1.Cells(lRow, 17).Value
        Me.txtQualification.Value = ws1.Cells(lRow, 18).Value
        Me.txtYearExp.Value = ws1.Cells(lRow, 19).Value
        Me.txtOccuV.Value = ws1.Cells(lRow, 20).Value
        Me.txtOccuA.Value = ws1.Cells(lRow, 21).Value
        Me.txtTWHpD.Value = ws1.Cells(lRow, 22).Value
        Me.txtTermNotPer.Value = ws1.Cells(lRow, 23).Value
        Me.txtTelNum.Value = ws1.Cells(lRow, 24).Value
        Me.txtGender.Value = ws1.Cells(lRow, 25).Value
        Me.txtMStatus.Value = ws1.Cells(lRow, 26).Value
        Me.txtBDate.Value = ws1.Cells(lRow, 27).Value
        Me.txtBasWages.Value = ws1.Cells(lRow, 28).Value
        Me.txtTrans.Value = ws1.Cells(lRow, 29).Value
        Me.txtBHSocInsNum.Value = ws1.Cells(lRow, 30).Value
        Me.txtSocInsEarn.Value = ws1.Cells(lRow, 31).Value
        Me.txtLMRA_HFund.Value = ws1.Cells(lRow, 32).Value
        Me.txtSocInsDed.Value = ws1.Cells(lRow, 33).Value
        Me.txtSocInsContr.Value = ws1.Cells(lRow, 34).Value
        Me.txtOSVocTrain.Value = ws1.Cells(lRow, 35).Value
        Me.txtAccomExpe.Value = ws1.Cells(lRow, 36).Value
        Me.txtTravelTime.Value = ws1.Cells(lRow, 37).Value
        Me.txtAirFare.Value = ws1.Cells(lRow, 38).Value
        Me.txtELPIns.Value = ws1.Cells(lRow, 39).Value
        Me.txtKitchenExpe.Value = ws1.Cells(lRow, 40).Value
        Me.txtMobExpe.Value = ws1.Cells(lRow, 41).Value
        Me.txtRem.Value = ws1.Cells(lRow, 42).Value
        
        End With
    End With
    
    '  Sheet2.Activate
    With ws2
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then lRow = cl.Row
            Me.lblEmpNumP2.Caption = .Cells(lRow, 2).Value
            
        Me.lblGNameP2.Caption = ws1.Cells(lRow, 3).Value
        Me.lblSNameP2.Caption = ws1.Cells(lRow, 4).Value
        Me.txtCurRenDate.Value = ws2.Cells(lRow, 5).Value
        Me.txtCurExpiDate.Value = ws2.Cells(lRow, 6).Value
        Me.txtCurPeriod.Value = ws2.Cells(lRow, 7).Value
        Me.txtCurChSalary.Value = ws2.Cells(lRow, 8).Value
        Me.txtCurCOBenef.Value = ws2.Cells(lRow, 9).Value
        Me.txtIVRenDate.Value = ws2.Cells(lRow, 10).Value
        Me.txtIVExpiDate.Value = ws2.Cells(lRow, 11).Value
        Me.txtIVPeriod.Value = ws2.Cells(lRow, 12).Value
        Me.txtIVChSalary.Value = ws2.Cells(lRow, 13).Value
        Me.txtIVCOBenef.Value = ws2.Cells(lRow, 14).Value
        Me.txtIIIRenDate.Value = ws2.Cells(lRow, 15).Value
        Me.txtIIIExpiDate.Value = ws2.Cells(lRow, 16).Value
        Me.txtIIIPeriod.Value = ws2.Cells(lRow, 17).Value
        Me.txtIIIChSalary.Value = ws2.Cells(lRow, 18).Value
        Me.txtIIICOBenef.Value = ws2.Cells(lRow, 19).Value
        Me.txtIIVRenDate.Value = ws2.Cells(lRow, 20).Value
        Me.txtIIExpiDate.Value = ws2.Cells(lRow, 21).Value
        Me.txtIIPeriod.Value = ws2.Cells(lRow, 22).Value
        Me.txtIIChSalary.Value = ws2.Cells(lRow, 23).Value
        Me.txtIICOBenef.Value = ws2.Cells(lRow, 24).Value
        Me.txtIRenDate.Value = ws2.Cells(lRow, 25).Value
        Me.txtIExpiDate.Value = ws2.Cells(lRow, 26).Value
        Me.txtIPeriod.Value = ws2.Cells(lRow, 27).Value
        Me.txtIChSalary.Value = ws2.Cells(lRow, 28).Value
        Me.txtICOBenef.Value = ws2.Cells(lRow, 29).Value
        Me.txtCarRem.Value = ws2.Cells(lRow, 30).Value
        
        End With
    End With
   
    '  Sheet3.Activate
    With ws3
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then lRow = cl.Row
            Me.lblEmpNumP3.Caption = .Cells(lRow, 2).Value
            
        Me.lblGNameP3.Caption = ws1.Cells(lRow, 3).Value
        Me.lblSNameP3.Caption = ws1.Cells(lRow, 4).Value
        Me.txtCurSite.Value = ws3.Cells(lRow, 5).Value
        Me.txtCReason.Value = ws3.Cells(lRow, 6).Value
        Me.txtPrevSiteI.Value = ws3.Cells(lRow, 7).Value
        Me.txtPrevDepartmtI.Value = ws3.Cells(lRow, 8).Value
        Me.txtReasonI.Value = ws3.Cells(lRow, 9).Value
        Me.txtPrevSiteII.Value = ws3.Cells(lRow, 10).Value
        Me.txtPrevDepartmtII.Value = ws3.Cells(lRow, 11).Value
        Me.txtReasonII.Value = ws3.Cells(lRow, 12).Value
        Me.txtPrevSiteIII.Value = ws3.Cells(lRow, 13).Value
        Me.txtPrevDepartmtIII.Value = ws3.Cells(lRow, 14).Value
        Me.txtReasonIII.Value = ws3.Cells(lRow, 15).Value
        Me.txtAllocRem.Value = ws3.Cells(lRow, 16).Value
        
        End With
    End With

    '  Sheet4.Activate
    With ws4
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then lRow = cl.Row
            
        Me.txtLatNumDays.Value = ws4.Cells(lRow, 5).Value
        Me.txtLatType.Value = ws4.Cells(lRow, 6).Value
        Me.txtLatPeriod.Value = ws4.Cells(lRow, 7).Value
        Me.txtLatNoDays.Value = ws4.Cells(lRow, 8).Value
        Me.txtLatArriDate.Value = ws4.Cells(lRow, 9).Value
        Me.txtTypePrevI.Value = ws4.Cells(lRow, 10).Value
        Me.txtPerPrevI.Value = ws4.Cells(lRow, 11).Value
        Me.txtNoDaysPrevI.Value = ws4.Cells(lRow, 12).Value
        Me.txtTypePrevII.Value = ws4.Cells(lRow, 13).Value
        Me.txtPerPrevII.Value = ws4.Cells(lRow, 14).Value
        Me.txtNoDaysPrevII.Value = ws4.Cells(lRow, 15).Value
        Me.txtTypePrevIII.Value = ws4.Cells(lRow, 16).Value
        Me.txtPerPrevIII.Value = ws4.Cells(lRow, 17).Value
        Me.txtNoDaysPrevIII.Value = ws4.Cells(lRow, 18).Value
        Me.txtTypePrevIV.Value = ws4.Cells(lRow, 19).Value
        Me.txtPerPrevIV.Value = ws4.Cells(lRow, 20).Value
        Me.txtNoDaysPrevIV.Value = ws4.Cells(lRow, 21).Value
        
        End With
    End With

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the Close button!"
  End If
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub
    Can you guys help me to complete this project?

    Thanks a lot guys.
    Attached Files Attached Files
    Last edited by bong25; 12-02-2009 at 10:26 AM. Reason: Removed some un-necessary codes
    Reply With Quote Reply With Quote
  2. 11-15-2009, 02:12 AM #2
    bong25
    bong25 is offline
    Registered User
    Join Date
    06-22-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Append data in worksheets

    bump......
    Reply With Quote Reply With Quote
  3. 11-15-2009, 02:25 AM #3
    teylyn
    teylyn is offline
    Forum Moderator teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    2003 & 2010
    Posts
    10,042

    Re: Append data in worksheets

    Patience! It's the weekend and there tend to be more people online during the week.

    teylyn
    Microsoft MVP - Excel
    At Excelforum, you can say "Thank you!" by clicking the icon below the post.

    Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
    Reply With Quote Reply With Quote
  4. 11-16-2009, 03:02 AM #4
    bong25
    bong25 is offline
    Registered User
    Join Date
    06-22-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Append data in worksheets

    My biggest apology...

    Thanks for noticing this thread.
    Reply With Quote Reply With Quote
  5. 11-16-2009, 04:00 AM #5
    broro183
    broro183 is offline
    Forum Guru
    Join Date
    01-03-2006
    Location
    Basingstoke, England
    MS-Off Ver
    2007 (work & home)
    Posts
    2,240

    Re: Append data in worksheets

    hi,

    Your code (seems to?) shows that you understand how to pull data from the spreadsheet... to put data back into the spreadsheet is effectively the opposite, for example

    Code:
    Private Sub ListBox1_change()
'code...
        Me.txtFname.Value = ws1.Cells(lRow, 3).Value
'code...
end sub
'the above pulls the data while the below will return it to the sheet (if the other appropriate code is included in the sub)

Private Sub cmdAdd_Click()
'code...
        ws1.Cells(lRow, 3).Value = Me.txtFname.Value 
'code...
End Sub
    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...
    Reply With Quote Reply With Quote
  6. 11-16-2009, 04:07 AM #6
    bong25
    bong25 is offline
    Registered User
    Join Date
    06-22-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Append data in worksheets

    Quote Originally Posted by broro183 View Post
    hi,

    Your code (seems to?) shows that you understand how to pull data from the spreadsheet... to put data back into the spreadsheet is effectively the opposite, for example

    Code:
    Private Sub ListBox1_change()
'code...
        Me.txtFname.Value = ws1.Cells(lRow, 3).Value
'code...
end sub
'the above pulls the data while the below will return it to the sheet (if the other appropriate code is included in the sub)

Private Sub cmdAdd_Click()
'code...
        ws1.Cells(lRow, 3).Value = Me.txtFname.Value 
'code...
End Sub
    hth
    Rob
    Hi Rob,

    Thank you very much for the reply.

    I will try your suggestion and see what is the outcome, I have just copied the code elsewhere here and not fully understand how these codes works. As I am just learning.

    TY again and come back later.
    Reply With Quote Reply With Quote
  7. 11-16-2009, 04:59 AM #7
    bong25
    bong25 is offline
    Registered User
    Join Date
    06-22-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Append data in worksheets

    Hi again,

    Yap you are right, the code works.

    I added the code as shown below:

    Code:
    Private Sub lblChange_Click()


    Dim rng    As Range
    Dim cl     As Range
    Dim sFind  As String
    Dim lRow      As Long
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Employee's List and Details")
    Dim ws2 As Worksheet
    Set ws2 = Worksheets("CAREER PROGRESSION")
    Dim ws3 As Worksheet
    Set ws3 = Worksheets("ALLOCATIONS")
    Dim ws4 As Worksheet
    Set ws4 = Worksheets("LEAVE SCHEDULE")
    
    bEditing = 1
    '  Sheet1.Activate
    With ws1
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then lRow = cl.Row
            Me.lblEmpNum.Caption = .Cells(lRow, 2).Value

             ws1.Cells(lRow, 3).Value = Me.txtFname.Value
             ws1.Cells(lRow, 4).Value = Me.txtSName.Value
             ws1.Cells(lRow, 5).Value = Me.txtDJoin.Value
             ws1.Cells(lRow, 6).Value = Me.txtNationality.Value
             ws1.Cells(lRow, 7).Value = Me.txtCPassPNum.Value
             ws1.Cells(lRow, 8).Value = Me.txtCPassPDateIs.Value
             ws1.Cells(lRow, 9).Value = Me.txtCPassPDateExp.Value
             ws1.Cells(lRow, 10).Value = Me.txtOPassPNum.Value
             ws1.Cells(lRow, 11).Value = Me.txtOPassPDateIs.Value
             ws1.Cells(lRow, 12).Value = Me.txtOPassPDateExp.Value
             ws1.Cells(lRow, 13).Value = Me.txtCPRNum.Value
             ws1.Cells(lRow, 14).Value = Me.txtCPRDateExp.Value
             ws1.Cells(lRow, 15).Value = Me.txtRPNum.Value
             ws1.Cells(lRow, 16).Value = Me.txtRPDateIs.Value
             ws1.Cells(lRow, 17).Value = Me.txtRPDateExp.Value
             ws1.Cells(lRow, 18).Value = Me.txtQualification.Value
             ws1.Cells(lRow, 19).Value = Me.txtYearExp.Value
             ws1.Cells(lRow, 20).Value = Me.txtOccuV.Value
             ws1.Cells(lRow, 21).Value = Me.txtOccuA.Value
             ws1.Cells(lRow, 22).Value = Me.txtTWHpD.Value
             ws1.Cells(lRow, 23).Value = Me.txtTermNotPer.Value
             ws1.Cells(lRow, 24).Value = Me.txtTelNum.Value
             ws1.Cells(lRow, 25).Value = Me.txtGender.Value
             ws1.Cells(lRow, 26).Value = Me.txtMStatus.Value
             ws1.Cells(lRow, 27).Value = Me.txtBDate.Value
             ws1.Cells(lRow, 28).Value = Me.txtBasWages.Value
             ws1.Cells(lRow, 29).Value = Me.txtTrans.Value
             ws1.Cells(lRow, 30).Value = Me.txtBHSocInsNum.Value
             ws1.Cells(lRow, 31).Value = Me.txtSocInsEarn.Value
             ws1.Cells(lRow, 32).Value = Me.txtLMRA_HFund.Value
             ws1.Cells(lRow, 33).Value = Me.txtSocInsDed.Value
             ws1.Cells(lRow, 34).Value = Me.txtSocInsContr.Value
             ws1.Cells(lRow, 35).Value = Me.txtOSVocTrain.Value
             ws1.Cells(lRow, 36).Value = Me.txtAccomExpe.Value
             ws1.Cells(lRow, 37).Value = Me.txtTravelTime.Value
             ws1.Cells(lRow, 38).Value = Me.txtAirFare.Value
             ws1.Cells(lRow, 39).Value = Me.txtELPIns.Value
             ws1.Cells(lRow, 40).Value = Me.txtKitchenExpe.Value
             ws1.Cells(lRow, 41).Value = Me.txtMobExpe.Value
             ws1.Cells(lRow, 42).Value = Me.txtRem.Value
        End With
    End With
    
    '  Sheet2.Activate
    With ws2
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then lRow = cl.Row
            Me.lblEmpNumP2.Caption = .Cells(lRow, 2).Value
            
             ws2.Cells(lRow, 3).Value = Me.lblGNameP2.Caption
             ws2.Cells(lRow, 4).Value = Me.lblSNameP2.Caption
             ws2.Cells(lRow, 5).Value = Me.txtCurRenDate.Value
             ws2.Cells(lRow, 6).Value = Me.txtCurExpiDate.Value
             ws2.Cells(lRow, 7).Value = Me.txtCurPeriod.Value
             ws2.Cells(lRow, 8).Value = Me.txtCurChSalary.Value
             ws2.Cells(lRow, 9).Value = Me.txtCurCOBenef.Value
             ws2.Cells(lRow, 10).Value = Me.txtIVRenDate.Value
             ws2.Cells(lRow, 11).Value = Me.txtIVExpiDate.Value
             ws2.Cells(lRow, 12).Value = Me.txtIVPeriod.Value
             ws2.Cells(lRow, 13).Value = Me.txtIVChSalary.Value
             ws2.Cells(lRow, 14).Value = Me.txtIVCOBenef.Value
             ws2.Cells(lRow, 15).Value = Me.txtIIIRenDate.Value
             ws2.Cells(lRow, 16).Value = Me.txtIIIExpiDate.Value
             ws2.Cells(lRow, 17).Value = Me.txtIIIPeriod.Value
             ws2.Cells(lRow, 18).Value = Me.txtIIIChSalary.Value
             ws2.Cells(lRow, 19).Value = Me.txtIIICOBenef.Value
             ws2.Cells(lRow, 20).Value = Me.txtIIVRenDate.Value
             ws2.Cells(lRow, 21).Value = Me.txtIIExpiDate.Value
             ws2.Cells(lRow, 22).Value = Me.txtIIPeriod.Value
             ws2.Cells(lRow, 23).Value = Me.txtIIChSalary.Value
             ws2.Cells(lRow, 24).Value = Me.txtIICOBenef.Value
             ws2.Cells(lRow, 25).Value = Me.txtIRenDate.Value
             ws2.Cells(lRow, 26).Value = Me.txtIExpiDate.Value
             ws2.Cells(lRow, 27).Value = Me.txtIPeriod.Value
             ws2.Cells(lRow, 28).Value = Me.txtIChSalary.Value
             ws2.Cells(lRow, 29).Value = Me.txtICOBenef.Value
             ws2.Cells(lRow, 30).Value = Me.txtCarRem.Value
        End With
    End With
   
    '  Sheet3.Activate
    With ws3
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then lRow = cl.Row
            Me.lblEmpNumP3.Caption = .Cells(lRow, 2).Value
            
            ws3.Cells(lRow, 3).Value = Me.lblGNameP3.Caption
            ws3.Cells(lRow, 4).Value = Me.lblSNameP3.Caption
            ws3.Cells(lRow, 5).Value = Me.txtCurSite.Value
            ws3.Cells(lRow, 6).Value = Me.txtCReason.Value
            ws3.Cells(lRow, 7).Value = Me.txtPrevSiteI.Value
            ws3.Cells(lRow, 8).Value = Me.txtPrevDepartmtI.Value
            ws3.Cells(lRow, 9).Value = Me.txtReasonI.Value
            ws3.Cells(lRow, 10).Value = Me.txtPrevSiteII.Value
            ws3.Cells(lRow, 11).Value = Me.txtPrevDepartmtII.Value
            ws3.Cells(lRow, 12).Value = Me.txtReasonII.Value
            ws3.Cells(lRow, 13).Value = Me.txtPrevSiteIII.Value
            ws3.Cells(lRow, 14).Value = Me.txtPrevDepartmtIII.Value
            ws3.Cells(lRow, 15).Value = Me.txtReasonIII.Value
            ws3.Cells(lRow, 16).Value = Me.txtAllocRem.Value
     
        End With
    End With

    '  Sheet4.Activate
    With ws4
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then lRow = cl.Row
            
            ws4.Cells(lRow, 3).Value = Me.lblGNameP3.Caption
            ws4.Cells(lRow, 4).Value = Me.lblSNameP3.Caption
            ws4.Cells(lRow, 5).Value = Me.txtLatNumDays.Value
            ws4.Cells(lRow, 6).Value = Me.txtLatType.Value
            ws4.Cells(lRow, 7).Value = Me.txtLatPeriod.Value
            ws4.Cells(lRow, 8).Value = Me.txtLatNoDays.Value
            ws4.Cells(lRow, 9).Value = Me.txtLatArriDate.Value
            ws4.Cells(lRow, 10).Value = Me.txtTypePrevI.Value
            ws4.Cells(lRow, 11).Value = Me.txtPerPrevI.Value
            ws4.Cells(lRow, 12).Value = Me.txtNoDaysPrevI.Value
            ws4.Cells(lRow, 13).Value = Me.txtTypePrevII.Value
            ws4.Cells(lRow, 14).Value = Me.txtPerPrevII.Value
            ws4.Cells(lRow, 15).Value = Me.txtNoDaysPrevII.Value
            ws4.Cells(lRow, 16).Value = Me.txtTypePrevIII.Value
            ws4.Cells(lRow, 17).Value = Me.txtPerPrevIII.Value
            ws4.Cells(lRow, 18).Value = Me.txtNoDaysPrevIII.Value
            ws4.Cells(lRow, 19).Value = Me.txtTypePrevIV.Value
            ws4.Cells(lRow, 20).Value = Me.txtPerPrevIV.Value
            ws4.Cells(lRow, 21).Value = Me.txtNoDaysPrevIV.Value
     
        End With
    End With

End Sub
    But once I click one of the item in the listbox, I get this error:

    Run-time error '6':

    Overflow
    I attached the amended file just in case you want to see the exact changes I made.

    Thank you very much again.

    Edit: The error occurs once I finished entering the datas then click Change Button. After that Select again for another data from listbox then boom.
    Attached Files Attached Files
    Last edited by bong25; 11-16-2009 at 05:05 AM.
    Reply With Quote Reply With Quote
  8. 11-16-2009, 06:09 AM #8
    broro183
    broro183 is offline
    Forum Guru
    Join Date
    01-03-2006
    Location
    Basingstoke, England
    MS-Off Ver
    2007 (work & home)
    Posts
    2,240

    Re: Append data in worksheets

    hi,

    Which line (if any) highlights in yellow when the error occurs?

    I haven't been able to duplicate your error in Excel 2007 but then I haven't tried very hard either!

    I suggest that:
    - In each Sub where you use a "set xyz = ..." statement to define an object/range then you also state "set xyz = Nothing" at the end of the Sub (unless it is a global variable - I don't think you have any of these though?).

    - The code is currently set up to search for the Employee number ("sFind = Me.ListBox1.Text") & if none have been selected it will populate the first row of the spreadsheet (because it finds ""). Can you figure out a way to overcome this?

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...
    Reply With Quote Reply With Quote
  9. 11-16-2009, 06:21 AM #9
    trucker10
    trucker10 is offline
    Forum Contributor trucker10's Avatar
    Join Date
    07-22-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 / 2007 / 2010 prof +
    Posts
    143

    Re: Append data in worksheets

    I think there is a problem with the format of the cells , For example, column X with the phone numbers .
    Reply With Quote Reply With Quote
  10. 11-16-2009, 08:50 AM #10
    bong25
    bong25 is offline
    Registered User
    Join Date
    06-22-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Append data in worksheets

    Hi again Rob and Hi trucker10,

    trucker10 You are right the column TEL NO was causing the problem and i deleted that one and works fine.

    and Rob, the highlighted yellow (once I choose debug) is the same TEL NO column.

    And regarding this:

    - The code is currently set up to search for the Employee number ("sFind = Me.ListBox1.Text") & if none have been selected it will populate the first row of the spreadsheet (because it finds ""). Can you figure out a way to overcome this?
    Sorry to say that I cannot overcome this, hope u can give me an input.

    Regards

    Bong
    Reply With Quote Reply With Quote
  11. 11-17-2009, 04:04 AM #11
    broro183
    broro183 is offline
    Forum Guru
    Join Date
    01-03-2006
    Location
    Basingstoke, England
    MS-Off Ver
    2007 (work & home)
    Posts
    2,240

    Re: Append data in worksheets

    hi Bong,

    I'm assuming that you are happy with the Tel ph number issue for the moment, is that okay?
    (perhaps we can look at it again later - I still haven't been able to duplicate the issue)
    Does it make any difference if you change the code from:
    Code:
    ws1.Cells(lRow, 24).Value = Me.txtTelNum.Value
'to define the "value2" property...
ws1.Cells(lRow, 24).Value2 = Me.txtTelNum.Value
'or to use the text value using 
ws1.Cells(lRow, 24).Value = Me.txtTelNum.text
'or maybe
ws1.Cells(lRow, 24).Value2 = Me.txtTelNum.text
    Here's some code which should hopefully overcome the Search issue:
    Code:
    Private Sub lblChange_Click()
'...
    bEditing = 1
'#########
    '  Sheet1.Activate
    With ws1
'RB: changed from ".cells(1,1)" to ".cells(8,1)" to remove the header rows from the range
        Set rng = .Range(.Cells(8, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
'RB: changed to look at the whole cell value
            Set cl = .Find(sFind, LookIn:=xlValues, lookat:=xlWhole)
            If Not cl Is Nothing Then
                lRow = cl.Row
            Else
            'RB: if the record is not found, create a new one in the first empty row of column C (First Name)
lRow = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row
            End If
'#########
'...
    If you agree with the logic I've used in the comments of the above code, you'll need to make similar changes to the other sections of your code which use the same principles. Ideally, where sections of code are duplicated you'd move them into a separate macro & call them from the first macro with parameters if necessary. We may be able to do this for you once you have your code working.
    If you don't agree with my logic in the above code & can't fix it yourself - let us know...

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...
    Reply With Quote Reply With Quote
  12. 11-17-2009, 08:50 AM #12
    bong25
    bong25 is offline
    Registered User
    Join Date
    06-22-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Append data in worksheets

    Hi Rob,

    Thanks again for the input, much appreciated.

    Yes I am happy with the Tel Num issue, it's OK now.

    About the search issue, I will try the code you suggested and see what will it end, and will come back to you as soon as possible.

    Thanks and best regards,

    Bong
    Reply With Quote Reply With Quote
  13. 11-17-2009, 11:00 AM #13
    bong25
    bong25 is offline
    Registered User
    Join Date
    06-22-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Append data in worksheets

    Hi Rob,

    Seems the suggested code works fine. The changes are as follows:

    Code:
        bEditing = 1
    '  Sheet1.( Employee's List and Details)
    With ws1
    
        Set rng = .Range(.Cells(8, 1), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues, lookat:=xlWhole)
            If Not cl Is Nothing Then
            lRow = cl.Row
            
             Me.lblEmpNum.Caption = ws1.Cells(lRow, 2).Value
             
             ws1.Cells(lRow, 3).Value = Me.txtFname.Value
             ws1.Cells(lRow, 4).Value = Me.txtSName.Value
             ws1.Cells(lRow, 5).Value = Format(Me.txtDJoin.Value, "dd/MMM/yyyy")
' ...........
' ...........
             
            Else
            lRow = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row
            End If

        End With
    End With
    As you can see I have added a "Format" for Date Entries.

    Thanks you very much for your help.

    PS: I cannot say at the moment that this project is final, can be able to tell you if everything goes well by tomorrow.
    Last edited by bong25; 11-17-2009 at 11:02 AM. Reason: Additional Info
    Reply With Quote Reply With Quote
  14. 11-17-2009, 05:09 PM #14
    broro183
    broro183 is offline
    Forum Guru
    Join Date
    01-03-2006
    Location
    Basingstoke, England
    MS-Off Ver
    2007 (work & home)
    Posts
    2,240

    Re: Append data in worksheets

    hi Bong,

    Another vbe function that may help when dealing with dates is "Cdate".

    Ummm...
    I think (without checking) that the way you have used the If statement means that the "lrow" defined within the Else clause, will not be used after the End If (b/c you then move onto the next sheet) therefore it may as well be deleted. Is this right?
    From memory, I think that this also means that you can't add new records to the file using the Change button. How will you add new records/employees to the file?

    Another tip for code tidyness (speed?) is to add another With statement eg:
    Code:
    with ws1
            Me.lblEmpNum.Caption = .Cells(lRow, 2).Value             
             .Cells(lRow, 3).Value = Me.txtFname.Value
             .Cells(lRow, 4).Value = Me.txtSName.Value
             .Cells(lRow, 5).Value = Format(Me.txtDJoin.Value, "dd/MMM/yyyy")
end with
    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...
    Reply With Quote Reply With Quote
  15. 11-18-2009, 08:06 AM #15
    bong25
    bong25 is offline
    Registered User
    Join Date
    06-22-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Append data in worksheets

    Hi Rob,

    Thanks again for the Tip.

    Regarding Date, Is this code OK?

    Code:
    Private Sub txtBDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If txtBDate = vbNullString Then Exit Sub
     
    If IsDate(txtBDate) Then
        txtBDate = Format(txtBDate, "dd/MMM/yyyy")
    Else
         MsgBox "Wrong Entry - Format: dd/MM/yyy"
        Cancel = True
    End If
End Sub

Private Sub txtDJoin_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If txtDJoin = vbNullString Then Exit Sub
     
    If IsDate(txtDJoin) Then
        txtDJoin = Format(txtDJoin, "dd/MMM/yyyy")
    Else
         MsgBox "Wrong Entry - Format: dd/MM/yyy"
        Cancel = True
    End If
End Sub
    And with regards of adding a record, I am thinking to add additional button to insert for a new Serial Number and Employee Number, but I am still trying to figure out how is this possible, may you can give me a hint.

    Thanks and regards,

    Bong

    Edit: ----------------------------------------------------

    I noticed a problem with the below code:

    Code:
    Sub UserForm_Initialize()
On Error Resume Next
    Dim EmpNu As Range
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Employee's List and Details")
    Dim ws2 As Worksheet
    Set ws2 = Worksheets("CAREER PROGRESSION")
    Dim ws3 As Worksheet
    Set ws3 = Worksheets("ALLOCATIONS")
    Dim ws4 As Worksheet
    Set ws4 = Worksheets("LEAVE SCHEDULE")

    With ws1
        Set EmpNu = .Range(.Cells(8, 2), .Cells(.Rows.Count, 1).End(xlUp))
        ListBox1.List = EmpNu.Value
    End With

End Sub

Private Sub ListBox1_change()

    Dim rng    As Range
    Dim cl     As Range
    Dim sFind  As String
    Dim lRow      As Long
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Employee's List and Details")
    Dim ws2 As Worksheet
    Set ws2 = Worksheets("CAREER PROGRESSION")
    Dim ws3 As Worksheet
    Set ws3 = Worksheets("ALLOCATIONS")
    Dim ws4 As Worksheet
    Set ws4 = Worksheets("LEAVE SCHEDULE")

  If bEditing Then Exit Sub

    '  Sheet1.(Employee's List and Details)
    With ws1
Before this is OK      Set rng = .Range(.Cells(1, 2), .Cells(.Rows.Count, 1).End(xlUp))
Changing to this creates incorrect display        Set rng = .Range(.Cells(8, 2), .Cells(.Rows.Count, 1).End(xlUp))
        sFind = Me.ListBox1.Text
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues, lookat:=xlWhole)
            If Not cl Is Nothing Then
            lRow = cl.Row
            
            Me.lblEmpNum.Caption = .Cells(lRow, 2).Value

            Me.txtFname.Value = .Cells(lRow, 3).Value
            Me.txtSName.Value = .Cells(lRow, 4).Value
            Me.txtDJoin.Value = Format(.Cells(lRow, 5).Value, "dd/MMM/yyyy")
'..........
'..........

            Else
            lRow = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row
            End If

        End With
    End With
    Hope you can figure out what just happened.

    Again best regards,

    Bong
    Last edited by bong25; 11-19-2009 at 04:07 AM. Reason: removing company name
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.2.0