Excel 2000

K

Ken

How do I use a if statement to determine if a number has been formatted with
a strikethrough?

I could not locate a function that determines the formatting of a number.
 
B

Bernard Liengme

=CELL("format",A1) will give the format code of cell A1
but is not much use for strikethrough
best wishes
 
G

Gary L Brown

You need a User-Defined-Function like 'IsStrikeThru' below.
Then you can use an if statement.
Ex. If(IsStrikeThru(A5)=True,"It's a Strike!","No Strike.")

'/========================================/
Public Function IsStrikeThru(Select_Cell As Range) As Boolean
On Error Resume Next
Application.Volatile
IsStrikethrough = Select_Cell.Font.Strikethrough
End Function
'/========================================/

If you want to make the UDF available to all YOUR workbooks, put it in your
Personal.xls.

HTH,
 
K

Ken

Thanks Gary,

Parts of the script accomplished what I was looking for.
IsStrikethrough varies from TRUE to FALSE but does not affect the results.

I am trying to:
When I strike through a number in a cell, I do not want the number to sum in
A5.
A1 = 1
A2 =2
A3=SUM(A1..A2) results 3 A3 is not affected by strikethrough

I want:
A5 = 3 when the numbers are not strikethrough
A5 = 1 when A2 is strikethrough
A5 = 2 when A1 is strikethrough

The other problem is the function does not run when the strikethrough is
added to the cell, it only runs when the number is changed.

Your thoughts!

Thanks

Ken
 
G

Gary L Brown

The only way that I can think of to address your first issue is with a
specialized SUM UDF such as....
'/===========================================/
Public Function SumNotStrikeThru(Select_Cells As Range) As Double
Dim rngCell As Range
Application.Volatile
On Error Resume Next
SumNotStrikeThru = 0
For Each rngCell In Select_Cells
If WorksheetFunction.IsNumber(rngCell) Then
If rngCell.Font.Strikethrough = False Then
SumNotStrikeThru = SumNotStrikeThru + rngCell
End If
End If
Next rngCell
End Function
'/===========================================/

Your second issue is one of 'when does the worksheet calculate'. I'm not
going to get complicated with Class Modules and such so I'd say your best bet
is to either hit the F9 key every so often or put some code in the specific
worksheet that you are having this issue with so that when you move your
cursor the whole worksheet calculates....
'/===========================================/
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
'/===========================================/
HTH,
 
K

Ken

One way to accomplish the tasks is to add a button to the spreadsheet that
will take the selected cells and:
Strikethrough the numbers in the cells
Then recalculate the worksheet.

My approach is shown below. The questions:
How do I bring the selected cells identifications into the button
subroutine? #1
Can I have the subroutine strike over the numbers in the cell? #2

Private Sub CommandButton2_Click()
Select_Cells As Range ??? #1 ???
Dim mgCell As Range
Application.Volatile
On Error Resume Next
For Each mgCell In Select_Cells
If WorksheetFunction.IsNumber(mgCell) Then
mgCell.Font = mgCell.Font.Strikethrough ??? #2 ???
MsgBox (mgCell) ! Just to see what is happening, will be removed !
End If
Next mgCell
Application.Calculate
End Sub

Can I do this?

Ken
 
G

Gary L Brown

Ken,
I apologize but I'm having a very hard time understanding your 2 questions.

I think you're trying to put a strikethrough for your selected cells?
Select_Cells As Range ??? #1 ???
Click to expand...
Should Be...
Dim Select_Cells as Range
then to make it know what cells you've already selected...
Set Select_Cells = Selection
mgCell.Font = mgCell.Font.Strikethrough
Click to expand...
To make all selected cells have a Strikethrough it should be...
rngCell.Font.Strikethrough = True
MsgBox (mgCell) ! Just to see what is happening, will be removed !
Click to expand...
To show a message of what cell is being affected, should be...
MsgBox rngCell.Address

That said, here are some other thoughts.
In your example of 'When I strike through a number in a cell, I do not want
the number to sum in A5':
in cell A5 put...
=SumNotStrikeThru(A1:A2)
That will give you your correct answer by ignoring cells that have
strikethroughs.
If you format another cell with a strikethrough, just hit the F9 [Calculate]
key to ensure you have the correct total.

I suggest that you put the SumNotStrikeThru function in your Personal.xls so
that it will always be available to you.

'/===========================================/
Public Function SumNotStrikeThru(Select_Cells As Range) As Double
Dim rngCell As Range
Application.Volatile
On Error Resume Next
SumNotStrikeThru = 0
For Each rngCell In Select_Cells
If WorksheetFunction.IsNumber(rngCell) Then
If rngCell.Font.Strikethrough = False Then
SumNotStrikeThru = SumNotStrikeThru + rngCell.Value
End If
End If
Next rngCell
End Function
'/===========================================/

Note about User-Defined-Function SumNotStrikeThru():
Select_Cells is the range of cells to be analyzed. This selection of
cells can either be typed in or selected using the mouse. In the above
example, A1:A2 is the Select_Cells range.
rngCell is used in the FOR loop to look at each cell in the Select_Cells
range.
IsNumber - check to see if the cell being looked at (rngCell) is a number.
If it is then go to the next line of code, otherwise ignore it because we
only want to add numbers.
rngCell.Font.Strikethrough - check to see if the Strikethrough property of
the font of the cell being looked at (rngCell) has been turned on. If it has
been, ignore it, otherwise add it to the results you already have.

f you want to contact me directly, take the '_NOSPAM_' out of my email
address below.

HTH,
 
K

Ken

Thank you, Gary.

The changes worked and the script does exactly what I wanted it to do.

I did save the functions in a personal file for future use. Great idea for
future reference.

Have a good weekend.

Ken


Gary L Brown said:
Ken,
I apologize but I'm having a very hard time understanding your 2 questions.

I think you're trying to put a strikethrough for your selected cells?
Select_Cells As Range ??? #1 ???
Click to expand...
Should Be...
Dim Select_Cells as Range
then to make it know what cells you've already selected...
Set Select_Cells = Selection
mgCell.Font = mgCell.Font.Strikethrough
Click to expand...
To make all selected cells have a Strikethrough it should be...
rngCell.Font.Strikethrough = True
MsgBox (mgCell) ! Just to see what is happening, will be removed
Click to expand...
!
To show a message of what cell is being affected, should be...
MsgBox rngCell.Address

That said, here are some other thoughts.
In your example of 'When I strike through a number in a cell, I do not want
the number to sum in A5':
in cell A5 put...
=SumNotStrikeThru(A1:A2)
That will give you your correct answer by ignoring cells that have
strikethroughs.
If you format another cell with a strikethrough, just hit the F9 [Calculate]
key to ensure you have the correct total.

I suggest that you put the SumNotStrikeThru function in your Personal.xls so
that it will always be available to you.

'/===========================================/
Public Function SumNotStrikeThru(Select_Cells As Range) As Double
Dim rngCell As Range
Application.Volatile
On Error Resume Next
SumNotStrikeThru = 0
For Each rngCell In Select_Cells
If WorksheetFunction.IsNumber(rngCell) Then
If rngCell.Font.Strikethrough = False Then
SumNotStrikeThru = SumNotStrikeThru + rngCell.Value
End If
End If
Next rngCell
End Function
'/===========================================/

Note about User-Defined-Function SumNotStrikeThru():
Select_Cells is the range of cells to be analyzed. This selection of
cells can either be typed in or selected using the mouse. In the above
example, A1:A2 is the Select_Cells range.
rngCell is used in the FOR loop to look at each cell in the Select_Cells
range.
IsNumber - check to see if the cell being looked at (rngCell) is a number.
If it is then go to the next line of code, otherwise ignore it because we
only want to add numbers.
rngCell.Font.Strikethrough - check to see if the Strikethrough property of
the font of the cell being looked at (rngCell) has been turned on. If it has
been, ignore it, otherwise add it to the results you already have.

f you want to contact me directly, take the '_NOSPAM_' out of my email
address below.

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


Ken said:
One way to accomplish the tasks is to add a button to the spreadsheet that
will take the selected cells and:
Strikethrough the numbers in the cells
Then recalculate the worksheet.

My approach is shown below. The questions:
How do I bring the selected cells identifications into the button
subroutine? #1
Can I have the subroutine strike over the numbers in the cell? #2

Private Sub CommandButton2_Click()
Select_Cells As Range ??? #1 ???
Dim mgCell As Range
Application.Volatile
On Error Resume Next
For Each mgCell In Select_Cells
If WorksheetFunction.IsNumber(mgCell) Then
mgCell.Font = mgCell.Font.Strikethrough ??? #2 ???
MsgBox (mgCell) ! Just to see what is happening, will be removed !
End If
Next mgCell
Application.Calculate
End Sub

Can I do this?

Ken

best
bet ''Was
this to
sum in
Click to expand...
Click to expand...
 
Top