REQ Help with simple Excel macro

O

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
 
L

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

*** Sent via Developersdex http://www.developersdex.com ***
 
R

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
 
O

Olórin

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

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
 
L

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

*** Sent via Developersdex http://www.developersdex.com ***
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top