reference, test and change cell font.

[feedscrn]

Hi All,

Excel VB is fun. I prefer MS C/C++, but I don't have the
compiler at work here... so....

I have the start of an Excel VB function... I would like to- via
a pushbutton to:
1. Test to see if the immediate left cell has strikeout font or
not.
2. If so, toggle to regular font.
2a. If regular font, toggle to strikeout.

This is for a timecard function... to say if the 'end time' is
tentative or not.

The pushbutton is set, with the basic structure. I don't know the
functions needed to reference, test or change the cell contents,
however

What I have so far, for a pushbutton in G10, is:
----------------------------------------------
Sub TempPermHrs()
Dim state As Integer // comment this out- how?
Set c = Range("F10")

End If
-----------------------------------------------

Thanks for any help,

Feedscrn


----------------------------------------------------------------------
When dinosaurs roamed the earth.. (hear the stomps in the background),
I started to learn Basic. I first learned some arcane commands: Peek
and Poke. That was enough to turn me off to the language for a long
time. VB is much more sophisticated now. It uses a 'DDEPoke Method'.
Everything is better now..

 

[Tom Ogilvy]

A really great tool you have in Excel is the macro recorder. I entered some
text in a cell, then selected it, turned on the recorder and made the font
strikethrough. then I turned it off and looked at the recorded code. This
isn't a good way to program but it is a quick way to look at the properties
and how they are used.

With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = True
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

so for your code

Sub TempPermHrs()
' a comment has a leading single quote
' once a single quote is encountered, that starts
' the quote - each line needs one
Dim state As Integer '// comment this out- how?
Set c = Range("F10")
c.Font.StrikeThrough = not c.Font.StrikeThrough

End sub

Note that the user can format individual characters. In which case, if a
single character is formatted as strikethrough rather than the whole cell,
then
c.Font.StrikeThrough would return NULL
End If


---
so just a demo from the immediate window:

activeCell.Font.Strikethrough = True
? activeCell.Font.Strikethrough
True
ActiveCell.Font.Strikethrough = False
? activeCell.Font.Strikethrough
False
ActiveCell.Characters(2,1).Font.Strikethrough = True
? activeCell.Font.Strikethrough
Null

 

[Leith Ross]

Hello feedscrn,

Here is some code for your button that expands on Tom's tutorial. Thi
code will automatically check the cell to the left of the button fo
Strikethrough effects. If true (all characters are stuck through) o
false (no characters) then the effect is toggled. If Strikethroug
returns a Null (some characters are struck through) it steps throug
each character in the cell and sets it to false.


Code
-------------------

Sub TempPermHrs()

Dim Btn As String
Dim C As Long
Dim TestCell As Range 'Object variable
Dim ST
Dim X

'Get the name of the button on the Worksheet
Btn = Application.Caller

'Get cell address of the button's upper left corner
X = ActiveSheet.Shapes(Btn).TopLeftCell.Address

'Make the object varaible point to the cell to the left of the button
Set TestCell = ActiveSheet.Range(X).Offset(1, -1)

'Get the current state of Strikethrough in the Test Cell
ST = TestCell.Font.Strikethrough

'Null indicates some characters have Strikethrough effect set
If IsNull(ST) Then
For C = 1 To Len(TestCell.Value)
TestCell.Characters(C, 1).Font.Strikethrough = False
Next C
Else
'Toggle the state of Strikethough using the logical Not operator
TestCell.Font.Strikethrough = Not ST
End If

End Sub

 

[feedscrn]

Tom, Leith,

Thank you for your collaborative tips. It works exactly as
expected. Pretty cool. I just had to change the initial offset to (0,
-1).


Feedscrn

+--------------------------------------------+
| The screen is hungry, Feed It! |
+--------------------------------------------+