Announcement

Collapse
No announcement yet.

Userform to check for changed values and add to spreadsheet

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

  • Userform to check for changed values and add to spreadsheet



    Hi all,

    Another question.

    I have a simple userform that writes data from userform text fields to the corresponding spreadsheet row.

    Code:
     
    
    Set DataRow = Sheet1.Cells(ActiveCell.Row, "A")
    'enter data from userform1  into active cell or open row
             
              DataRow.Offset(0, 15).Value = OPS_WINDOWS_created_YN.Text
             
              DataRow.Offset(0, 16).Value = phone_setup_YN.Text
    What I want to add is a check that if there no value in the spreadsheet, write the new value, if a different value is already there, check if it is the same value in the useroform text field, if it is, do nothing, if it is different, add the new value in Blue text. So we know if data has been changed from when it was first entered.

    Basically allowing a user to read the data as it shows up in the userform, (it does this already) but if a user changes the userform data in the text field to add the new data to the spreadsheet but highlight the data in blue text on the spreadsheet.

    Can anyone help? Driving me insane.

    Thanks!!!!!

  • #2
    Re: Userform to check for changed values and add to spreadsheet

    So, how do I properly word this in code to say, on the modifiy button,


    Code:
    IF 
    
    OPS_WINDOWS_created_YN.Value <> DataRow.Offset(0, 15).Value 
    
    Then 
    
    DataRow.Offset(0, 15).Value= OPS_WINDOWS_created_YN.Value & Blue text.
    Anyone?

    Comment


    • #3
      Re: Userform to check for changed values and add to spreadsheet

      Code:
      If OPS_WINDOWS_created_YN.Value <> DataRow.Offset(0, 15).Value THen
           
          With DataRow.Offset(0, 15)
              .Value= OPS_WINDOWS_created_YN.Value
              .Font.Color = vbBlue
          End with
      End If
      But you'll also have to figure out how to reset the colour at some stage...

      Comment


      • #4
        Re: Userform to check for changed values and add to spreadsheet

        Excellent!!! It works, is there also anyway to code this to basically say if there is no spreadsheet value to begin with, keep text normal, like for the first time data is written to the spreadsheet. This way I dont need a modify button. Something like,

        Code:
        If 
        DataRow.Offset(0, 15).Value = “ “ 
        
        Then 
        
        DataRow.Offset(0, 15).Value = OPS_WINDOWS_created_YN.Text
        
         Else 
        
        If OPS_WINDOWS_created_YN.Value <> DataRow.Offset(0, 15).Value 
        
        Then 
             
            With DataRow.Offset(0, 15) 
                .Value= OPS_WINDOWS_created_YN.Value 
                .Font.Color = vbBlue 
            End With 
        End If

        Comment


        • #5
          Re: Userform to check for changed values and add to spreadsheet

          Untested, but try changing the line
          Code:
              DataRow.Offset(0, 15).Value = “ “
          to
          Code:
              DataRow.Offset(0, 15).Value = vbNullString
              '// Or
              DataRow.Offset(0, 15).Value = ""
          A space (" ") is not the same as a null string ("") to VBA.

          You'll also need a Closing 'End If' for the new 'If' statement.

          The basic structure of an If... statement is
          Code:
             If Condition Then
                '// Code for matching condition
             Else
                '// Code for non-matching condition
             End If
          You have the 'Then' on a separate line which will throw an error.

          Comment


          • #6
            Re: Userform to check for changed values and add to spreadsheet

            I got it to WORK!!!!!! Wow thanks so much for your help!!!!!

            Code:
            If DataRow.Offset(0, 15).Value = "" Then
            DataRow.Offset(0, 15).Value = OPS_WINDOWS_created_YN.Text
             With DataRow.Offset(0, 15)
                        .Value = OPS_WINDOWS_created_YN.Value
                        .Font.Color = vbBlack
            End If
                 
            If OPS_WINDOWS_created_YN.Value <> DataRow.Offset(0, 15).Value Then
                     
                    With DataRow.Offset(0, 15)
                        .Value = OPS_WINDOWS_created_YN.Value
                        .Font.Color = vbBlue
                    End With
                End If
                 
                  End Sub

            Comment


            • #7


              Re: Userform to check for changed values and add to spreadsheet

              One small point, the 'standard' forecolor is xlAutomatic, not vbBlack...

              Comment

              Working...
              X