Strikethrough Text to be identified with code

  • Thread starter Thread starter Corey
  • Start date Start date
C

Corey

In my quest to complete a fairly detailed workbook,
i need to remove values that are in are populated into a combobox already
displayed in a sheet where the values are stored.

Using codes such as .offset(x, x).value and
rngFound.Offset(x, x).value,
My reasoning is to change where the values are taken from and populated into
the combobox,
to be changed to StrikeThrough ONCED that value has been used.

But this would mean that i need code to them ONLY Populate the comboboxes
with values that do NOT contain StrikeThrough's.


How can i do this?
Can code determine IF values are containing StrikeThrough ?




Corey....
 
This should help get you going

If Cells(1, 1).Font.Strikethrough = True Then Cells(i, 2) = "S/T"
 
Thanks for the reply John.
I cannot work out where and if i can use it with:

Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long

lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row

With ActiveWorkbook.workSheets("InspectionData")
..Select 'first thing to do with a With statement that occurs on a second
sheet
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text = ComboBox28.Text And
..Cells(myrow, 1).Offset(-1, 6).Text = ComboBox1.Text And
IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
If Cells(1, 1).Font.Strikethrough = True Then ' <===============
Here ?
ComboBox6.AddItem Cells(myrow, 3).Offset(2, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(3, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(4, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(5, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(6, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(7, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(8, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(9, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(10, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(11, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(12, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(13, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(14, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(15, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(16, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(17, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(18, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(19, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(20, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(21, 0)
ComboBox6.AddItem Cells(myrow, 3).Offset(22, 0)

End If
End If
End If
Next
End With
End Sub

I tried it above also as:
If .Cells(myrow, 1).Offset(-1, 2).Text = ComboBox28.Text And .Cells(myrow,
1).Offset(-1, 6).Text = ComboBox1.Text And IsNumeric(Trim(Mid(.Cells(myrow,
1), 2))) = True and .cells.font.Strikethrough = False Then
But if 1 value in ALL the list has Strikethrough i get NO values at all
then?

Corey....
 
Corey,
I have not looked through your entire code but this is my best guess at what
you are trying to do

Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long

lastcell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text = ComboBox28.Text And _
.Cells(myrow, 1).Offset(-1, 6).Text = ComboBox1.Text And _
IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(2, i).Font.Strikethrough =
False Then
ComboBox6.AddItem Cells(myrow, 3).Offset(2, i)
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True

End Sub
 
Thanks Alok,
I pasted your code in and i think you are correctly on the right track of
what i am after.
But,
I cannot see how to adjust it, but your code lists the values across the
columns, where i need the list to be down in rows in column "C".

How can i adjust that, i cann make out where the offset looks across rahter
than down.
ALSO, i need to tyry to remove any "" values

Corey....
 
Ok,
got the empty values to be removed by:


If Cells(myrow, 3).Offset(2, i).Font.Strikethrough = False And Cells(myrow,
3).Offset(2, i).Value <> "" Then
 
Ended up with:

Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long

lastcell = workSheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.workSheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text = ComboBox28.Text And
..Cells(myrow, 1).Offset(-1, 6).Text = ComboBox1.Text And
IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Font.Strikethrough =
False And Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 3).Offset(i, 0)
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub
 
Hope your code does what it is supposed to do.

Corey said:
Ended up with:

Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long

lastcell = workSheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.workSheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text = ComboBox28.Text And
..Cells(myrow, 1).Offset(-1, 6).Text = ComboBox1.Text And
IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Font.Strikethrough =
False And Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 3).Offset(i, 0)
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub
Click to expand...
 
What column holds the cell to inspect? Is that cell on the same row as myRow?

maybe...

If .Cells(myRow, 26).Font.Strikethrough = True Then
(26 means column Z. Change it to what you want.)
 
Back
Top