Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Strikethrough Text to be identified with code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 363
Default 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....









Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 318
Default Strikethrough Text to be identified with code
Hope your code does what it is supposed to do.

"Corey" wrote:

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










Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Check boxes and text strikethrough / color change Christine Excel Worksheet Functions 2 October 12th 09 09:30 PM
Need to remove text with strikethrough - Excel 2007 HD[_2_] Excel Worksheet Functions 6 July 18th 08 07:37 AM
Each client to be identified into segment Mat Excel Discussion (Misc queries) 2 March 15th 07 03:59 PM
Summary Sheet For Identified Dupes Mhz New Users to Excel 6 July 14th 06 12:07 PM
Empty cell Identified Brian Matlack[_17_] Excel Programming 2 October 25th 05 03:33 PM


All times are GMT +1. The time now is 08:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement