Thread: Append data in worksheets

hi Bong,

Try searching for tips on the Dates section (if you're not reassured by your testing) - I'll look tomorrow.

Good catch!
sORRY, I changed the rng to use "cells(8,2)" to stop the search finding blank rows at the top of the page but overlooked this impact. To ensure the correct figures are displayed when using "cells(8,2)", you need to change...

Code:

            lRow = cl.Row
'to...
            lRow = cl.Row - 7 (I think! my brain's a bit tired)

This will then mean that the cells which are referenced within the "With Rng" statement are correctly picked up.

goodluck
Rob

Hi Rob,

My apology for taking so long. I was concentrating on the form to add new entry and this is what I have done.

Code:

Dim bEditing As Boolean
Option Explicit

Sub UserForm_Initialize()
    Dim rng 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 rng = .Range(.Cells(8, 2), .Cells(.Rows.Count, 1).End(xlUp))
    End With

End Sub

Private Sub CommandButton1_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")

'   Sheet1.(Employee's List and Details)
    With ws1
    
        Set rng = .Range(.Cells(1, 2), .Cells(.Rows.Count, 1).End(xlUp))
'        sFind = rng.Value
        
        With rng
            Set cl = .Find(sFind, LookIn:=xlValues, lookat:=xlWhole)
            If Not cl Is Nothing Then
            lRow = cl.Row
            
            lRow = Application.WorksheetFunction.Max(ws1.Columns(1))
            lRow = lRow + 1
            
    bEditing = True
      
            lRow = ws1.Cells(Rows.Count, 1) _
            .End(xlUp).Offset(1, 0).Row
    
             .Cells(lRow, 1).Value = "=+R[-1]C+1"
             .Cells(lRow, 2).Value = Me.txtNewEmpNum.Value
             
    bEditing = False
    
              End If
        End With
      End With
      
'   Sheet2.(CAREER PROGRESSION)
'.................
'.................

The above code works fine, but I think it a messy setup coz I was just playing around to find out how things can be done. Hope you can show me how to make it tidy.

You may have noticed the brown colored text, as we have discussed before, the " +7 " thing does not help to show the proper display of the data.

I hope I am not being retarded.

Thanks again, hope to hear from you soon.

Best Regards,


bong

Edit: BTW my main key here is Employee Number so I have to make a new UserForm to call in so that I can add new record/data.

hi Bong,

It's good to hear you are learning through playing around & trying things out

I probably won't be on the computer for a few days so hopefully someone else can help you...
To make it easier for us, can you please upload the latest version of your file?

Rob

Hi Rob,

Yes of course I attached here my latest project.

Hope to hear from you sooner.

Best Regards,

Bong

Hi Rob,

I have been searching for Search/Find code to incorporate in my file, and I found this:

Code:

Dim MySearch as Range
Set MySearch = Sheets("MySheet").Columns("A").Find(Textbox1.text)
If Not MySearch Is Nothing Then 
Sheets("MySheet").Range(MySearch.Address).Select
Textbox2 = Sheets("MySheet").Range(MySearch.Address).Offset(0,1)
Else
Msgbox "Not Found"
End If

How can I use this for my file?

Please help.

Thanks and best regard,

Bong

EDIT: Is there a code to redirect the find result to ListBox as a selected value?

hi Bong,

I've had a go at tidying up your code - see what you think of the attached file...
I've included some commented Assumptions within the code.

I'm not sure exactly what you mean in your last post, can you please have another go at explaining your question?

hth
Rob

Hi Rob,

My findings with the new file are as follows:

1. During Editing Mode, If a user mistakenly select a textbox (for any Date), could not switch to any other textbox unless the value required is entered.

Code:

Private Sub txtBDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If txtBDate = vbNullString Then Exit Sub ‘ <-- added back this line, if this
           is the correct method and it’s seems ok, please correct me if this is
           not right.
    Cancel = Not (IsDateEntryValid(Me.txtBDate))
End Sub

2. Once the user is done with editing and fires up the Update, it calls up the "UformUpdateErrorMsg" for all the sheets. (With my little knowledge I could not figure out how).

Edit: uppsssss, I got it: sFind = Me.LBoxOfEmpNums.Text <-- should be sFind = Me.LBoxOfEmpNums.Value (your testing me right?)

3. The On Error Resume next <--- I did not bother to check out this, it was on the original code.

4. Adding new employee number - You are correct, so that’s fine.

5. I am trying to include the search/find function, say for example that the datas are huge and the need to search is required. Below is what I am trying to do. (again it’s not done correctly / not working at all).

Code:

Private Sub Find_Click()
    Dim i As Integer
    Dim bFound As Boolean
    Dim rngWhole As Range
    Dim rng As Range
    Dim LItem As Long
    Dim allData As Range
    Dim lRow As Long
    Dim EmpNumFound As Long
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Employee's List and Details")
 ………
………
………
………
………

    
    With ws1
        Set rngWhole = .Range("a1:IV9999")
        Set rng = .Range(.Cells(1, 2), .Cells(.Rows.Count, 1).End(xlUp))
        With rngWhole
            Set allData = .Find(txtFindWhat.Text)
            If Not allData Is Nothing Then
                LItem = .Range(allData.Address).Select
                lRow = (allData.Row)
                EmpNumFound = .Cells(lRow, 2).Value
                With rng
                For i = 0 To ListBox1.ListCount - 1
                
                  If ListBox1.List(i) = EmpNumFound Then
                  bFound = True
                  ListBox1.List(i).Select
                  Exit For
                     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")
………
………
                     Me.txtRem.Value = .Cells(lRow, 42).Value
                  End If
                  Next
                    If bFound = False Then
                        MsgBox "Employee Number for Search Not Found"
                    End If
               End With
            Else
            MsgBox "Could Not Find the Given Text"
            End If
        End With
    End With
End Sub

Note: I have to show you the code in the old format.

The search/find is added in the main form at the top right side with “Find button”, Label and TextBox for entering what to find.

Say user entered “passport number” and press find, the code should look for the entered value then if found the corresponding Employee Number in the listbox should be selected automatically and of course displays the information on the useform.

I know you are tired, coz the changes you made are massively huge, and could not find the way how to thank you enough.


Thanks and best regards,

Bong

EDIT2: Testing the new code I found another one (which I think another test for me):

Code:

 '  Sheet4.(LEAVE SCHEDULE)
    With LeaveSht
        RwToUse = FoundRow(AllocSht, sFind)
        If Not IsNull(RwToUse) Then

Where it should

Code:

    '  Sheet4.(LEAVE SCHEDULE)
    With LeaveSht
        RwToUse = FoundRow(LeaveSht, sFind)
        If Not IsNull(RwToUse) Then

Hi Bong,
I wasn't testing you - I was just dopy!

1. I think your approach will work but to minimise code duplication I've moved the check into the function changing it from "IsDateEntryValid" to "IsDateEntryEmptyOrValid".
2. Nope, I wasn't testing you - but I'm pleased you found it
You may know all this but in case you don't...
To help find what is happening when the code runs, you can type "stop" on a line by itself at the start of a function or sub. When the code stops on this line & highlights the line in the VBE you can press [ctrl + L] in the VBE to show the "call stack" & by clicking on the items you can go back to the location in the previous sub/function. Once you are looking at the preceding code you may be able to identify what has caused the error by holding the mouse over the variables (or creating a "Watch expression"). To subsequently continue the code, you can press [F8] to proceed one line at a time from the highlighted "stop", or [F5] to let the code run automatically.
3. I've removed the "on error resume next" & replaced it with a logic check.
4.
5. I've had a go at recreating a search function & including it in the code of the attached v3 file. Also, I have attached another file which contains a Search function that I've recently made for a work mate which may give you some further ideas.

Edit2. Ooopps! Good spotting
btw, I'm currently reading "Excel Programming with Visual Basics for Applications in 21 Days" by Matthew Harris & although it is "old" (written for Excel '95 users!)* it has some good concepts (well, I think they are ). One of these is the deliberate assignation of Null to indicate invalid data which can then be tested using "IsNull(...)" (p354-355). So I thought I'd give it a go in your code
* So old in fact, I was able to get it for free from a secondhand bookshop (www.skoob.com)

hth
Rob

Hi Rob,

Thanks again.

About the testing thing, I was enjoying it and I thought you are testing me.

To help find what is happening when the code runs ......

I honestly don't know that and hey thanks for the tip.

Thanks again for the search code, I will study it how this codes works.

I am excited to see this project goes.

cya later.

Thanks and Best Regards,

Bong

EDIT: WOW man, the search was great, and it even made the listbox automatically select the right row, this is what I was breaking my head with hummer just to figure out how. You are great man

Thanks a lot.

hi Bong

Thanks for the feedback - I'm pleased I could help

I haven't tested the code completely but I think we must be close to marking this thread as solved...
If you are happy with the solution, can you please mark the thread as solved & feel free to add to my rep (thanks for the previous rep )?
I think any small changes you need to make now can be the subject of a new thread (with a link to this thread for background info).

re "edit: Search selecting item in list"
Yes, it just comes from knowing how to word your search questions when Googling & the try & try again approach!
I found it using something like "select highlight listbox item in vba excel sub" as my search terms.

Goodluck
Rob

Dear Rob,

Yes, after doing some testing, I can tell that thread is done.

Thanks you very much indeed, much appreciated.

BTW I added another point to you.

Best Regards,

Bong