Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Using userform to edit worksheet data

  1. #1
    Join Date
    15th August 2011
    Posts
    29

    Using userform to edit worksheet data

    Hi,

    I have a userform currently used to add and view records in a training plan. I'm hoping to include the ability to update the record as part of the view userform. I've scoured Ozgrid (and some less reputable sites too with some horrifyingly muddled solutions) and tried a few different examples but can't quite seem to get it right. Could anyone shed some light on where I am going wrong here?

    VB:
     
    Private Sub cmdUpdate_Click() 
        If MsgBox("Are you sure you wish to change this record?", vbYesNo, "Confirm edit") = vbYes Then 
             
            Dim strFind 
            Dim Nullstring 
            Dim rSearch As Range 'range to search
            Set rSearch = ThisWorkbook.Worksheets("Raw Data").Range("A1:BJ4000") 
            Dim rngFind 
            strFind = comboIdentifier.Value 
            If strFind = Nullstring Then Goto error1 
            With rSearch 
                Set rngFind = .Find(What:=strFind, MatchCase:=True) 
                If Not rngFind Is Nothing Then 'found it
                     
                    ThisWorkbook.Worksheets("Raw Data").Select 
                    rngFind.Activate 
                    ActiveCell.Offset(0, 4).Text = txt1.Value 
                    ActiveCell.Offset(0, 5).Text = txt2.Value 
                    ActiveCell.Offset(0, 6).Text = txt3.Value 
                    ActiveCell.Offset(0, 7).Text = txt4.Value 
                    ActiveCell.Offset(0, 8).Text = txt5.Value 
                    ActiveCell.Offset(0, 9).Text = txt6.Value 
                     
                     
                    MsgBox "The record has sucessfully been updated", vbOKOnly 
    error1: 
                     
                End If 
            End With 
        End If 
         
    End Sub 
    
    
    Thanks,

    Maria.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    7,916

    Re: Using userform to edit worksheet data

    Just reading it your code seems OK - so just to clarify... what's wrong, what's happening (or not as the case may be)?

    Only other observation is you're searching a 'wide' range - is it possible the search term could be found in a column other than the Identifier column?
    Did you try limiting the search to just the Identifier column?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th August 2011
    Posts
    29

    Re: Using userform to edit worksheet data

    Hi,

    Sorry it has taken me so long to get back to this. It is failing at the following line:
    VB:
    [FONT=monospace]ActiveCell.Offset(0, 4).Text = txt1.Value [/FONT] 
    
    
    I've checked and the correct cell is activated in the relevant worksheet, but then when it comes to writing the values it fails? I really can't see at all why this doesn't work for me, I've pasted it into a new workbook and still it fails. I've also triple checked all the cell/textbox/worksheet references for spelling errors and they look fine? I imagine that I've done something small and stupid somewhere but I just can't see it. If anyone can spot the error I would be really grateful, alternatively I would be willing to start again if someone knows a different way of doing this as a work around.

    Thanks,

    Maria.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th August 2011
    Posts
    29

    Re: Using userform to edit worksheet data

    Oh, sorry also meant to add that I've now limited the search to the identifier column, you were right it was a pointlessly large area to be searching. Oops!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    8th July 2004
    Posts
    579

    Re: Using userform to edit worksheet data

    This works for me

    VB:
    Private Sub cmdUpdate_Click() 
         'If MsgBox("Are you sure you wish to change this record?", vbYesNo, "Confirm edit") = vbYes Then  '<----Activate again
         
        Dim strFind 
        Dim Nullstring 
        Dim rSearch As Range 'range to search
        Set rSearch = ThisWorkbook.Worksheets("Sheet2").Range("A1:A10") '<----- Change to suit
        Dim rngFind 
        strFind = ComboIdentifier.Value 
        If strFind = Nullstring Then Goto error1 
        With rSearch 
            Set rngFind = .Find(What:=strFind, MatchCase:=True) 
            If Not rngFind Is Nothing Then 'found it
                 
                 
                rngFind.Offset(0, 4).Value = txt1.Value 
                rngFind.Offset(0, 5).Value = txt2.Value 
                rngFind.Offset(0, 6).Value = txt3.Value 
                rngFind.Offset(0, 7).Value = txt4.Value 
                rngFind.Offset(0, 8).Value = txt5.Value 
                rngFind.Offset(0, 9).Value = txt6.Value 
                 
                 
                MsgBox "The record has sucessfully been updated", vbOKOnly 
    error1: 
                 
            End If 
        End With 
         'End If  '<----Activate again
         
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    15th August 2011
    Posts
    29

    Re: Using userform to edit worksheet data

    Ace! Swapping .Text with .Value has fixed the issue. Thanks! I knew I must be doing something wrong. Out of interest can anyone explain why that is the case? All of the values are text values, and a few of the working examples that I drew code from seemed to be using .Text as an argument so I'm wondering why it isn't working in this case.

    Other than that I'm over the moon that this is working, thanks for your help!

    Maria.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    1st September 2010
    Posts
    7,916

    Re: Using userform to edit worksheet data

    The .VALUE property of a cell is the underlying value. The .TEXT property is the displayed. formatted value. Mostly they're the same unless you have special formatting in place. For example, the value 3 in a cell formatted with multiple decimal places gives these results (printing to the debug window:

    ?activecell.Value
    3
    ?activecell.Text
    3.00000000000

    Apologies if I missed that little nuance in my first reply... I never use the .TEXT property unless there's a particular reason to - always the .VALUE property

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th August 2011
    Posts
    29

    Re: Using userform to edit worksheet data

    I'm pretty sure what I'm about to ask is still relevant to the title, so I'll steam ahead, apologies though if I'm contravening the rule to post separate questions in separate threads.

    I've just realised that I've omitted my checkboxes in the original "add data" userform. I had assumed that to add this would be fairly simple using the following:
    VB:
    If chckReferences.Value = True Then 
        rngFind.Offset(0, 21) = "Received" 
    Else 
        If chckReferences.Value = False Then 
            rngFind.Offset(0, 21) = "Outstanding" 
        End If 
    
    
    however this seems to be ignored by my code in practise. Here is the full script
    VB:
    Private Sub cmdUpdate_Click() 
        If MsgBox("Are you sure you wish to change this record?", vbYesNo, "Confirm edit") = vbYes Then 
            Dim strFind 
            Dim Nullstring 
            Dim rSearch As Range 'range to search
            Set rSearch = ThisWorkbook.Worksheets("Raw Data").Range("A1:A400") 
            Dim rngFind 
            strFind = comboCandidateIdentifier.Value 
            If strFind = Nullstring Then Goto error1 
            With rSearch 
                Set rngFind = .Find(What:=strFind, MatchCase:=True) 
                If Not rngFind Is Nothing Then 'found it
                     
                    ThisWorkbook.Worksheets("Raw Data").Select 
                    rngFind.Activate 
                    rngFind.Offset(0, 4).Value = txtTitle.Value 
                    rngFind.Offset(0, 5).Value = txtName.Value 
                    rngFind.Offset(0, 6).Value = txtSurname.Value 
                    rngFind.Offset(0, 7).Value = txtDateInt.Value 
                    rngFind.Offset(0, 8).Value = txtAddress1.Value 
                    rngFind.Offset(0, 9).Value = txtAddress2.Value 
                    rngFind.Offset(0, 10).Value = txtAddress3.Value 
                    rngFind.Offset(0, 11).Value = txtAddress4.Value 
                    rngFind.Offset(0, 12).Value = txtPostcode.Value 
                    rngFind.Offset(0, 13).Value = txtMobile.Value 
                    rngFind.Offset(0, 14).Value = txtHome.Value 
                    rngFind.Offset(0, 15).Value = txtEmail.Value 
                    rngFind.Offset(0, 16).Value = txtDOB.Value 
                    rngFind.Offset(0, 17).Value = txtCurrEmp.Value 
                    rngFind.Offset(0, 18).Value = txtNotice.Value 
                    rngFind.Offset(0, 19).Value = txtNation.Value 
                    rngFind.Offset(0, 20).Value = txtRegulatory.Value 
                     
                     
                    MsgBox "Working up until regulatory", vbOKOnly 
                     
                     
                    rngFind.Offset(0, 33).Value = comboBase.Value 
                    rngFind.Offset(0, 34).Value = comboAltBase.Value 
                    rngFind.Offset(0, 35).Value = comboRank.Value 
                    rngFind.Offset(0, 36).Value = comboFleet.Value 
                    rngFind.Offset(0, 37).Value = comboExp.Value 
                    rngFind.Offset(0, 38).Value = comboContractType.Value 
                    rngFind.Offset(0, 39).Value = comboContractVersion.Value 
                    rngFind.Offset(0, 40).Value = txtOfferVersion.Value 
                    rngFind.Offset(0, 41).Value = txtTAVersion.Value 
                    rngFind.Offset(0, 42).Value = txtStartingSalary.Value 
                    rngFind.Offset(0, 43).Value = txtLineManager.Value 
                    rngFind.Offset(0, 44).Value = txtComments2.Value 
                     'Message box to check progress
                    MsgBox "Working up until comments2", vbOKOnly 
                    rngFind.Offset(0, 45).Value = comboStatus.Value 
                     
                     'Space for Next Stage once finalised in ADDCandidate Form
                     
                    rngFind.Offset(0, 47).Value = txtNextDeadline.Value 
                    rngFind.Offset(0, 48).Value = comboSecurityPass.Value 
                    rngFind.Offset(0, 49).Value = txtSecurityDeadline.Value 
                    rngFind.Offset(0, 50).Value = txtVerbalAccepted.Value 
                    rngFind.Offset(0, 51).Value = txtOfferLetterSent.Value 
                    rngFind.Offset(0, 52).Value = txtTAIssued.Value 
                    rngFind.Offset(0, 53).Value = txtTAReturned.Value 
                    rngFind.Offset(0, 54).Value = txtPassIssued.Value 
                    rngFind.Offset(0, 55).Value = txtProjStart.Value 
                    rngFind.Offset(0, 56).Value = txtProjEmpStart.Value 
                    rngFind.Offset(0, 57).Value = txtDateAuthorised.Value 
                    rngFind.Offset(0, 58).Value = comboAuthorisers.Value 
                    rngFind.Offset(0, 59).Value = txtActualTraining.Value 
                    rngFind.Offset(0, 60).Value = txtAdminComms.Value 
                    rngFind.Offset(0, 61).Value = txtClearedFly.Value 
                     'Message box to check progress
                    MsgBox "Working up until start of checkboxes", vbOKOnly 
                    If chckReferences.Value = True Then 
                        rngFind.Offset(0, 21) = "Received" 
                    Else 
                        If chckReferences.Value = False Then 
                            rngFind.Offset(0, 21) = "Outstanding" 
                        End If 
                         'THIS MESSAGE BOX IS NOT DISPLAYED
                        MsgBox "Working up until after first checkbox", vbOKOnly 
                         
                        If chckMedical.Value = True Then 
                            rngFind.Offset(0, 22) = "Received" 
                        Else 
                            If chckMedical.Value = False Then 
                                rngFind.Offset(0, 22) = "Outstanding" 
                            End If 
                             
                            If chckLicence.Value = True Then 
                                rngFind.Offset(0, 23) = "Received" 
                            Else 
                                If chckLicence.Value = False Then 
                                    rngFind.Offset(0, 23) = "Outstanding" 
                                End If 
                                 
                                If chckMCC.Value = True Then 
                                    rngFind.Offset(0, 24) = "Received" 
                                Else 
                                    If chckMCC.Value = False Then 
                                        rngFind.Offset(0, 24) = "Outstanding" 
                                    End If 
                                     
                                    If chckJOC.Value = True Then 
                                        rngFind.Offset(0, 25) = "Received" 
                                    Else 
                                        If chckJOC.Value = False Then 
                                            rngFind.Offset(0, 25) = "Outstanding" 
                                        End If 
                                         
                                        If chckIR.Value = True Then 
                                            rngFind.Offset(0, 26) = "Received" 
                                        Else 
                                            If chckIR.Value = False Then 
                                                rngFind.Offset(0, 26) = "Outstanding" 
                                            End If 
                                             
                                            If chckIdentity.Value = True Then 
                                                rngFind.Offset(0, 27) = "Received" 
                                            Else 
                                                If chckIdentity.Value = False Then 
                                                    rngFind.Offset(0, 27) = "Outstanding" 
                                                End If 
                                                 
                                                If chckProof.Value = True Then 
                                                    rngFind.Offset(0, 28) = "Received" 
                                                Else 
                                                    If chckProof.Value = False Then 
                                                        rngFind.Offset(0, 28) = "Outstanding" 
                                                    End If 
                                                     
                                                    If chckFunding.Value = True Then 
                                                        rngFind.Offset(0, 29) = "Received" 
                                                    Else 
                                                        If chckFunding.Value = False Then 
                                                            rngFind.Offset(0, 29) = "Outstanding" 
                                                        End If 
                                                         
                                                        If chckOAA.Value = True Then 
                                                            rngFind.Offset(0, 30) = "Received" 
                                                        Else 
                                                            If chckOAA.Value = False Then 
                                                                rngFind.Offset(0, 30) = "Outstanding" 
                                                            End If 
                                                             
                                                            If chckWish.Value = True Then 
                                                                rngFind.Offset(0, 31) = "Received" 
                                                            Else 
                                                                If chckWish.Value = False Then 
                                                                    rngFind.Offset(0, 31) = "Outstanding" 
                                                                End If 
                                                                 
                                                                If chckUniform.Value = True Then 
                                                                    rngFind.Offset(0, 32) = "Received" 
                                                                Else 
                                                                    If chckUniform.Value = False Then 
                                                                        rngFind.Offset(0, 32) = "Outstanding" 
                                                                    End If 
                                                                     
                                                                     
                                                                    MsgBox "The record has sucessfully been updated", vbOKOnly 
    error1: 
                                                                     
                                                                    If MsgBox("Are you sure you wish to change this record?", vbYesNo, "Confirm edit") = vbNo Then 
                                                                        Unload Me 
                                                                         
                                                                    End If 
                                                                End If 
                                                            End If 
                                                        End If 
                                                    End If 
                                                End If 
                                            End If 
                                        End If 
                                    End If 
                                End If 
                            End If 
                        End If 
                    End If 
                End If 
            End With 
        End If 
         
    End Sub 
    
    
    Everything up until the message boxes is running, I used message boxes to check progress and the one after the first checkbox doesn't show, and neither does the final one. I also can't account for all of the End If statements required? I've added in extra because the debugger kept recommending them but I don't follow the logic. Sorry for the additional question, and I hope this makes sense, I completely forgot it wasn't all textboxes on the original.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    15th August 2011
    Posts
    29

    Re: Using userform to edit worksheet data

    Bump

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    1st September 2010
    Posts
    7,916

    Re: Using userform to edit worksheet data

    Is the logic correct... ? I have a feeling it's not. Each of the If ... End If blocks should be terminated immediately rather than holding the End If until the end of the procedure.

    VB:
    If chckReferences.Value = True Then 
        rngFind.Offset(0, 21) = "Received" 
    Else 
        If chckReferences.Value = False Then 
            rngFind.Offset(0, 21) = "Outstanding" 
        End If 
         'THIS MESSAGE BOX IS NOT DISPLAYED
        MsgBox "Working up until after first checkbox", vbOKOnly 
    
    
    The message box will never display if chckReferences.Value is True

    You might want to simplify the code structure. It's easy to get lost in all those control statements.

    They could be replaced with something like:

    VB:
     
    rngFind.Offset(0, 21).Value = iif(chckReferences.Value = True, "Received", "Outstanding") 
    rngFind.Offset(0, 22).Value = iif(chckMedical.Value = True, "Received", "Outstanding") 
    rngFind.Offset(0, 23).value = iif(chckLicence.Value = True, "Received", "Outstanding") 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. edit data in different rows via userform
    By ncaravela in forum EXCEL HELP
    Replies: 2
    Last Post: October 18th, 2010, 19:22
  2. Edit Worksheet Data Table Via UserForm
    By aertanconfin in forum EXCEL HELP
    Replies: 8
    Last Post: June 3rd, 2009, 06:31
  3. View & Edit Worksheet In UserForm
    By weezybaby in forum EXCEL HELP
    Replies: 3
    Last Post: June 6th, 2008, 10:57
  4. Edit worksheet with Userform up?
    By Aussie in forum EXCEL HELP
    Replies: 3
    Last Post: September 20th, 2004, 06:01

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
  •  
porno