Does Excel see a cell w/ formulas as non-empty cell?

[jwright650]

When using a VBA UserForm to write data to the worksheet....How do you make excel ignore conditional formatting within the cells and see them as blank cells?

I was using the find last row code and when the useform writes the data it sees any conditional formatting as non-blank cells and writes the data in the rows below.

How can I force it to write in the cells with conditional formatting in them?

I will have to post an example tomorrow when I get to work.

[JBeaucaire]

You'd have to post your code. None of the code I use would see a cell with CF on it but nothing in the cell as anything other than empty.

[jwright650]

Private Sub cmdCloseandSave_Click()
'write data to NC data sheet
Dim LastRow As Object

          Set LastRow = Sheet3.Range("a65536").End(xlUp)

          LastRow.Offset(1, 0).Value = cboJob.Text
          LastRow.Offset(1, 1).Value = txtDate1NCform.Text
          LastRow.Offset(1, 2).Value = txtSONum.Text
          LastRow.Offset(1, 3).Value = txtPcMks.Text
          LastRow.Offset(1, 4).Value = txtProbDescrip.Text
          LastRow.Offset(1, 5).Value = chkRework.Value
          LastRow.Offset(1, 6).Value = chkRepair.Value
          LastRow.Offset(1, 7).Value = opbtnAsIs.Value
          LastRow.Offset(1, 8).Value = opbtnScrap.Value
          LastRow.Offset(1, 9).Value = txtDisposition.Text
          LastRow.Offset(1, 10).Value = txtReworkInst.Text
          LastRow.Offset(1, 11).Value = txtReInsp.Text
          LastRow.Offset(1, 12).Value = txtDate2NCform.Text
          LastRow.Offset(1, 13).Value = chkCARyes.Value
          LastRow.Offset(1, 14).Value = txtCARNum.Text
          LastRow.Offset(1, 15).Value = txtCauseCode1.Value
          LastRow.Offset(1, 16).Value = txtTimeReq.Text
          

          MsgBox "One record written to Non-Conformance Data"

          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

          If response = vbYes Then
              cboJob.Text = ""
              txtDate1NCform.Text = ""
              txtSONum.Text = ""
              txtPcMks.Text = ""
              txtProbDescrip.Text = ""
              chkRework.Value = False
              chkRepair.Value = False
              opbtnAsIs.Value = False
              opbtnScrap.Value = False
              txtDisposition.Text = ""
              txtReworkInst.Text = ""
              txtReInsp.Text = ""
              txtDate2NCform.Text = ""
              chkCARyes.Value = False
              chkCARno.Value = False
              txtCARNum.Text = ""
              txtCauseCode1.Value = ""
              ListBox1.Value = ""
              txtTimeReq.Text = ""
              
              
              cboJob.SetFocus

          Else
              Unload Me
          End If


End Sub

I mis-spoke lastnight about the formatting, I was trying to put in an "If Then" in the cells were it would write true or false, and I was wanting it to say yes or no...If "true", then "yes", "no"......

[JBeaucaire]

Although different in approach, your code worked fine for me once I took out the reference to ListBox1 which apparently doesn't exist.

This is how I would write the same code, small differences.

Private Sub cmdCloseandSave_Click()
'write data to NC data sheet
Dim NR As Long

    NR = Sheet3.Range("A" & Sheet3.Rows.Count).End(xlUp).Row + 1

    Cells(NR, "A").Value = cboJob.Text
    Cells(NR, "B").Value = txtDate1NCform.Text
    Cells(NR, "C").Value = txtSONum.Text
    Cells(NR, "D").Value = txtPcMks.Text
    Cells(NR, "E").Value = txtProbDescrip.Text
    Cells(NR, "F").Value = chkRework.Value
    Cells(NR, "G").Value = chkRepair.Value
    Cells(NR, "H").Value = opbtnAsIs.Value
    Cells(NR, "I").Value = opbtnScrap.Value
    Cells(NR, "J").Value = txtDisposition.Text
    Cells(NR, "K").Value = txtReworkInst.Text
    Cells(NR, "L").Value = txtReInsp.Text
    Cells(NR, "M").Value = txtDate2NCform.Text
    Cells(NR, "N").Value = chkCARyes.Value
    Cells(NR, "O").Value = txtCARNum.Text
    Cells(NR, "P").Value = txtCauseCode1.Value
    Cells(NR, "Q").Value = txtTimeReq.Text
    
    If MsgBox("One record written to Non-Conformance Data." & vbLf & vbLf & _
        "Do you want to enter another record?", vbYesNo, "Continue?") = vbYes Then
            cboJob.Text = ""
            txtDate1NCform.Text = ""
            txtSONum.Text = ""
            txtPcMks.Text = ""
            txtProbDescrip.Text = ""
            chkRework.Value = False
            chkRepair.Value = False
            opbtnAsIs.Value = False
            opbtnScrap.Value = False
            txtDisposition.Text = ""
            txtReworkInst.Text = ""
            txtReInsp.Text = ""
            txtDate2NCform.Text = ""
            chkCARyes.Value = False
            chkCARno.Value = False
            txtCARNum.Text = ""
            txtCauseCode1.Value = ""
            txtTimeReq.Text = ""
            cboJob.SetFocus
    Else
        Unload Me
    End If
End Sub

[jwright650]

I took out the reference to ListBox1 which apparently doesn't exist.

JB, I'm going to study your version for a little bit to see what all you did there....(I'm still learning better ways to do things as I go)


The ListBox1 was deleted from that userform and placed on a userform by itself to populate a textbox with the selections(concatenated, if the user chooses more than one cause code)

Yeah, I don't need to set ListBox1 back to null if it doesn't exist...thanks for catching that.

That was one of my first VBA userform projects....I started out with a doosey for my forst project. It was one of those where I would have never got it to work if it wasn't for this forum and the kind folks here who helped me along.

[jwright650]

Used the LastRow method shown above, thread is "Solved"