Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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?

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    10,362

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

    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
    657

    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

    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
    10,362

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

    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
    10,362

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

    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, 20:22
  2. Edit Worksheet Data Table Via UserForm
    By aertanconfin in forum EXCEL HELP
    Replies: 8
    Last Post: June 3rd, 2009, 07:31
  3. View & Edit Worksheet In UserForm
    By weezybaby in forum EXCEL HELP
    Replies: 3
    Last Post: June 6th, 2008, 11:57
  4. Edit worksheet with Userform up?
    By Aussie in forum EXCEL HELP
    Replies: 3
    Last Post: September 20th, 2004, 07: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