Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Using userform to edit worksheet data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Code:
     
    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.

  • #2
    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?

    Comment


    • #3
      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:
      Code:
       ActiveCell.Offset(0, 4).Text = txt1.Value 
      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.

      Comment


      • #4
        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!

        Comment


        • #5
          Re: Using userform to edit worksheet data

          This works for me

          Code:
          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

          Comment


          • #6
            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.

            Comment


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

              Comment


              • #8
                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:
                Code:
                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
                Code:
                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.

                Comment


                • #9
                  Re: Using userform to edit worksheet data

                  Bump

                  Comment


                  • #10
                    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.

                    Code:
                       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:

                    Code:
                       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")

                    Comment


                    • #11
                      Re: Using userform to edit worksheet data

                      That's great! Thanks Cytop. I had found a workaround of making my checkboxes into Yes/No comboboxes in the meantime but this is much cleaner and doesn't compromise the layout of my form.

                      Thanks for your help, really much appreciated.

                      Maria.

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X