REQ Help with simple Excel macro

[Olórin]

Hi there -

I haven't played around with macros much before and need to ask what must be
an extremely simple question.

I want to create a toolbar button in Excel (this is Excel 2003) to put a
tick in a cell. I recorded a macro of the action (below) but while it puts a
u-umlaut character in the selected cell, it only applies the right font
formatting to cell D7 (where the cursor happened to be at time of recording)
and leaves the current cell as is, whereas it needs to be in the Wingdings
font. I seem to remember in an older version of Office (95?), when you went
in to Record Macro it asked if you wanted to use absolute or relative cell
references, which might have done the trick, but that option seems to have
been moved or removed now. And I know no VBA, not even enough to make basic
changes to the macro.

Can anyone please help?

Many thanks!

Sub Tick()
'
' Tick Macro
' Macro recorded 01/08/2007
'

'
ActiveCell.FormulaR1C1 = "ü"
Range("D7").Select
With Selection.Font
..Name = "Wingdings"
..Size = 10
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
End Sub

 

[Les Stout]

Hi Olórin, hope this helps... ;0)
This will leave the cursor where it is but change the values of "I7"

Sub Tick()
'
Range("I7") = "ü"
With Range("I7").Font
.Name = "Wingdings"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Bold = True
End With

End Sub


Best regards,

Les Stout

 

[Ray]

Try this:
************
Sub Tick()

With ActiveCell.Offset(0, -1)
.FormulaR1C1 = "ü"
.Font.Name = "Wingdings"
.Font.Size = 10
.Font.Bold = True
End With

End Sub
*************

Change the values in the Offset to put the tick mark wherever you want
(in relation to the activecell) -- as is, the tick-mark will be one
cell to the LEFT of the activecell. OR, to put the tick-mark IN the
activecell, just delete the Offset property altogether ...

br//ray

 

[Olórin]

Thanks, but "always I7" isn't a huge improvement on "always D7"... or am I
missing something?

 

[Olórin]

Thanks, Ray - I get the picture now. All working fine.

Try this:
************
Sub Tick()

With ActiveCell.Offset(0, -1)
.FormulaR1C1 = "ü"
.Font.Name = "Wingdings"
.Font.Size = 10
.Font.Bold = True
End With

End Sub
*************

Change the values in the Offset to put the tick mark wherever you want
(in relation to the activecell) -- as is, the tick-mark will be one
cell to the LEFT of the activecell. OR, to put the tick-mark IN the
activecell, just delete the Offset property altogether ...

br//ray

 

[Les Stout]

Hi,

Just Change the "I7" for the cell where you want the Tick to be.


Sub Tick()
'
Range("I7") = "ü"With Range("I7").Font'<== And here...
.Name = "Wingdings"
.Size = 10
.Bold = True
End With

End Sub


Best regards,

Les Stout