Strikethrough Text to be identified with code
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
"Corey" wrote in message
...
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
"Corey" wrote in message
...
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....
"Alok" wrote in message
...
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
"Corey" wrote:
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....
|