+ Reply to Thread
Results 1 to 2 of 2

Userform: Retrieve data from excel sheet and overwrite with new data in the same rows

Hybrid View

  1. 03-03-2014, 04:13 PM #1
    Pbawal
    Pbawal is offline
    Registered User
    Join Date
    12-30-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Userform: Retrieve data from excel sheet and overwrite with new data in the same rows

    Hello,

    I am new to VBA, I have created a userform with multipage, has two page that help to add new record in a excel sheet. Data has a unique reference no.(TxtRef.Value) for each record. I am trying to add a button to load the added data for a specific record using reference no back to userform so that it can be updated and overwrite back to the sheet in the same row, So far it is adding new record correctly. I do not know how to populate all the fields of the of an existing record and overwrite it back to the same row instead of adding a new record. Below is my codes

    I thank you in advance for your time and effort.

    Thanks,
    Pbawal


    Private Sub CommandButton1_Click()

Dim Lastrow As Long

Lastrow = Worksheets("Audit Data").Range("B1048576").End(xlUp).Row + 1

'FOR RECORD TAB
Worksheets("Audit Data").Range("A" & Lastrow).Value = CmboAuditor.Value
Worksheets("Audit Data").Range("B" & Lastrow).Value = CmboAssociate.Value
Worksheets("Audit Data").Range("C" & Lastrow).Value = txtRecord.Value
Worksheets("Audit Data").Range("D" & Lastrow).Value = TxtRef.Value
Worksheets("Audit Data").Range("E" & Lastrow).Value = CmboOffice.Value
Worksheets("Audit Data").Range("F" & Lastrow).Value = TxtWeek.Value
Worksheets("Audit Data").Range("G" & Lastrow).Value = TxtDate.Value
Worksheets("Audit Data").Range("H" & Lastrow).Value = CmboPGroup.Value
Worksheets("Audit Data").Range("CT" & Lastrow).Value = CmboProcess.Value

'FOR PERSON TAB

If CheckBox1.Value = True Then
Worksheets("Audit Data").Range("I" & Lastrow).Value = "Y"
ElseIf CmboMr.Value <> "" Then
Worksheets("Audit Data").Range("I" & Lastrow).Value = CmboMr.Value
Else
Worksheets("Audit Data").Range("I" & Lastrow).Value = "NA"
End If

If CheckBox2.Value = True Then
Worksheets("Audit Data").Range("J" & Lastrow).Value = "Y"
ElseIf CmboTitle.Value <> "" Then
Worksheets("Audit Data").Range("J" & Lastrow).Value = CmboTitle.Value
Else
Worksheets("Audit Data").Range("J" & Lastrow).Value = "NA"
End If

If CheckBox3.Value = True Then
Worksheets("Audit Data").Range("K" & Lastrow).Value = "Y"
ElseIf CmboPeople.Value <> "" Then
Worksheets("Audit Data").Range("K" & Lastrow).Value = CmboPeople.Value
Else
Worksheets("Audit Data").Range("K" & Lastrow).Value = "NA"
End If

If CheckBox4.Value = True Then
Worksheets("Audit Data").Range("L" & Lastrow).Value = "Y"
ElseIf CmboPplkno.Value <> "" Then
Worksheets("Audit Data").Range("L" & Lastrow).Value = CmboPplkno.Value
Else
Worksheets("Audit Data").Range("L" & Lastrow).Value = "NA"
End If

If CheckBox5.Value = True Then
Worksheets("Audit Data").Range("M" & Lastrow).Value = "Y"
ElseIf CmboAddressed.Value <> "" Then
Worksheets("Audit Data").Range("M" & Lastrow).Value = CmboAddressed.Value
Else
Worksheets("Audit Data").Range("M" & Lastrow).Value = "NA"
End If

If CheckBox6.Value = True Then
Worksheets("Audit Data").Range("N" & Lastrow).Value = "Y"
ElseIf CmboEmail.Value <> "" Then
Worksheets("Audit Data").Range("N" & Lastrow).Value = CmboEmail.Value
Else
Worksheets("Audit Data").Range("N" & Lastrow).Value = "NA"
End If

If CheckBox7.Value = True Then
Worksheets("Audit Data").Range("O" & Lastrow).Value = "Y"
ElseIf CmboPplphon.Value <> "" Then
Worksheets("Audit Data").Range("O" & Lastrow).Value = CmboPplphon.Value
Else
Worksheets("Audit Data").Range("O" & Lastrow).Value = "NA"
End If

' Blank all the fields

'Record


CmboAuditor.Value = ""
CmboAssociate.Value = ""
txtRecord.Value = ""
TxtRef.Value = ""
CmboOffice.Value = ""
TxtWeek.Value = ""
TxtDate.Value = ""
CmboPGroup.Value = ""

'Person
CmboMr.Value = ""
CmboTitle.Value = ""
CmboPeople.Value = ""
CmboPplkno.Value = ""
CmboAddressed.Value = ""
CmboEmail.Value = ""
CmboPplphon.Value = ""

' Person
CheckBox1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False
CheckBox4.Value = False
CheckBox5.Value = False
CheckBox6.Value = False
CheckBox7.Value = False
End Sub
    Last edited by Pbawal; 03-03-2014 at 04:31 PM.
    Reply With QuoteRegister To Reply
  2. 03-09-2014, 11:49 AM #2
    Pbawal
    Pbawal is offline
    Registered User
    Join Date
    12-30-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Userform: Retrieve data from excel sheet and overwrite with new data in the same rows

    Thanks People,

    I got it my self...searching through internet. Below is my coding and it is perfectly retrieving data from excel sheet.

    Private Sub CommandButton4_Click() 
     
     
     ' To retrieve the existing record data
     
     
    Dim strFind As String 'what to find
    Dim FirstAddress As String 
    Dim rSearch As Range 'range to search
    Set rSearch = Worksheets("Audit Data").Range("D4", Range("D1048576").End(xlUp)) 
    Dim f      As Integer 
     
     
    strFind = AuditData.TxtRef.Value 'what to look for
     
     
    With rSearch 
        Set C = .Find(strFind, LookIn:=xlValues) 
        If Not C Is Nothing Then 'found it
            C.Select 
             
            Col = Split(C(1).Address(1, 0), "$")(0) 
            Rownum = Split(C(1).Address(1, 0), "$")(1) 
             
             
             
             'FOR RECORD TAB
            AuditData.CmboAuditor.Value = Worksheets("Audit Data").Range("A" & Rownum).Value 
            AuditData.CmboAssociate.Value = Worksheets("Audit Data").Range("B" & Rownum).Value 
            AuditData.txtRecord.Value = Worksheets("Audit Data").Range("C" & Rownum).Value 
            AuditData.TxtRef.Value = Worksheets("Audit Data").Range("D" & Rownum).Value 
            AuditData.CmboOffice.Value = Worksheets("Audit Data").Range("E" & Rownum).Value 
            AuditData.TxtWeek.Value = Worksheets("Audit Data").Range("F" & Rownum).Value 
            AuditData.TxtDate.Value = Worksheets("Audit Data").Range("G" & Rownum).Value 
            AuditData.CmboPGroup.Value = Worksheets("Audit Data").Range("H" & Rownum).Value 
             
             'FOR PERSON TAB
            If Worksheets("Audit Data").Range("I" & Rownum).Value = "Y" Then 
                AuditData.CheckBox54.Value = True 
            ElseIf Worksheets("Audit Data").Range("I" & Rownum).Value <> "NA" Then 
                AuditData.CmboMr.Value = Worksheets("Audit Data").Range("I" & Rownum).Value 
            End If 
             
            If Worksheets("Audit Data").Range("J" & Rownum).Value = "Y" Then 
                AuditData.CheckBox60.Value = True 
            ElseIf Worksheets("Audit Data").Range("J" & Rownum).Value <> "NA" Then 
                AuditData.CmboTitle.Value = Worksheets("Audit Data").Range("J" & Rownum).Value 
            End If 
             
            If Worksheets("Audit Data").Range("K" & Rownum).Value = "Y" Then 
                AuditData.CheckBox59.Value = True 
            ElseIf Worksheets("Audit Data").Range("K" & Rownum).Value <> "NA" Then 
                AuditData.CmboPeople.Value = Worksheets("Audit Data").Range("K" & Rownum).Value 
            End If 
             
            If Worksheets("Audit Data").Range("L" & Rownum).Value = "Y" Then 
                AuditData.CheckBox58.Value = True 
            ElseIf Worksheets("Audit Data").Range("L" & Rownum).Value <> "NA" Then 
                AuditData.CmboPplkno.Value = Worksheets("Audit Data").Range("L" & Rownum).Value 
            End If 
             
             
            If Worksheets("Audit Data").Range("M" & Rownum).Value = "Y" Then 
                CheckBox57.Value = True 
            ElseIf Worksheets("Audit Data").Range("M" & Rownum).Value <> "NA" Then 
                CmboAddressed.Value = Worksheets("Audit Data").Range("M" & Rownum).Value 
            End If 
             
            If Worksheets("Audit Data").Range("N" & Rownum).Value = "Y" Then 
                CheckBox56.Value = True 
            ElseIf Worksheets("Audit Data").Range("N" & Rownum).Value <> "NA" Then 
                CmboEmail.Value = Worksheets("Audit Data").Range("N" & Rownum).Value = "NA" 
            End If 
             
        End If 
    End With 
     
End Sub
    and to update the data in the same row to overwrite I am using 

    Private Sub CommandButton5_Click() 
     
     '   To Update the existing record
     
     
    Dim strFind As String 'what to find
    Dim FirstAddress As String 
    Dim rSearch As Range 'range to search
    Set rSearch = Worksheets("Audit Data").Range("D4", Range("D1048576").End(xlUp)) 
    Dim f      As Integer 
     
     
    strFind = AuditData.TxtRef.Value 'what to look for
     
     
    With rSearch 
        Set C = .Find(strFind, LookIn:=xlValues) 
        If Not C Is Nothing Then 'found it
            C.Select 
             
            Col = Split(C(1).Address(1, 0), "$")(0) 
            Rownum = Split(C(1).Address(1, 0), "$")(1) 
             
             
             
             'FOR RECORD TAB
            Worksheets("Audit Data").Range("A" & Rownum).Value = CmboAuditor.Value 
            Worksheets("Audit Data").Range("B" & Rownum).Value = CmboAssociate.Value 
            Worksheets("Audit Data").Range("C" & Rownum).Value = txtRecord.Value 
            Worksheets("Audit Data").Range("D" & Rownum).Value = TxtRef.Value 
            Worksheets("Audit Data").Range("E" & Rownum).Value = CmboOffice.Value 
            Worksheets("Audit Data").Range("F" & Rownum).Value = TxtWeek.Value 
            Worksheets("Audit Data").Range("G" & Rownum).Value = TxtDate.Value 
            Worksheets("Audit Data").Range("H" & Rownum).Value = CmboPGroup.Value 
            Worksheets("Audit Data").Range("CT" & Rownum).Value = CmboProcess.Value 
             
             'FOR PERSON TAB
             
            If CheckBox54.Value = True Then 
                Worksheets("Audit Data").Range("I" & Rownum).Value = "Y" 
            ElseIf CmboMr.Value <> "" Then 
                Worksheets("Audit Data").Range("I" & Rownum).Value = CmboMr.Value 
            Else 
                Worksheets("Audit Data").Range("I" & Rownum).Value = "NA" 
            End If 
             
            If CheckBox60.Value = True Then 
                Worksheets("Audit Data").Range("J" & Rownum).Value = "Y" 
            ElseIf CmboTitle.Value <> "" Then 
                Worksheets("Audit Data").Range("J" & Rownum).Value = CmboTitle.Value 
            Else 
                Worksheets("Audit Data").Range("J" & Rownum).Value = "NA" 
            End If 
             
            If CheckBox59.Value = True Then 
                Worksheets("Audit Data").Range("K" & Rownum).Value = "Y" 
            ElseIf CmboPeople.Value <> "" Then 
                Worksheets("Audit Data").Range("K" & Rownum).Value = CmboPeople.Value 
            Else 
                Worksheets("Audit Data").Range("K" & Rownum).Value = "NA" 
            End If 
             
        End If 
    End With 
     
End Sub
    This is not a complete module just the part I asked for..!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need Userform to overwrite same set of data
    By ericbinfet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2014, 08:01 PM
  2. Use textbox in userform to retrieve data
    By Fogsta65 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-12-2013, 04:59 PM
  3. Retrieve data from excel in userform to edit
    By Daantje71 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2013, 03:06 PM
  4. [SOLVED] Read/Retrieve/locate data between different excel sheet using macro/vba
    By EugeneE in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-26-2013, 07:50 AM
  5. Split data from Master Sheet to existing worksheets and overwrite data
    By vmwest in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-21-2013, 05:36 PM

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.6.0 RC 1