[beginner] create macro to insert symbol

[Artavar]

Hello,

I'm only starting to use macros and I would like to create a macro to
insert a little arrow in a cell instead of inserting the symbol
everytime....
Now I have recorded a macro but the trouble is I would like to insert
it in a cell where there's already text. What happens is that
everything disappears and only my symbol remains....I also would like
to insert several arrows in the same cell....

Can somebody help me?

Thanks a lot,
Artavar

 

[Norman Jones]

Hi Artavar,

Post your code and someone will help you to amend it.

 

[Tom Ogilvy]

Sub InsertArrow()
sStr = ActiveCell.Value
Num = 3
sStr1 = sStr & Application.Rept(Chr(218), Num)
ActiveCell.Value = sStr1
Debug.Print sStr1, Len(sStr), Len(sStr1)
With ActiveCell.Characters(Start:=Len(sStr) + 1, _
Length:=Len(sStr1) - Len(sStr)).Font
.Name = "Wingdings"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub

Set num to the number of arrows you want to insert.

 

[Artavar]

Thank you Tom and Norman for responding to my question !

I tried your script out and it's getting closer to what I'd like to
have. Two questions about it:
-How can i make sure the arrows are inserted before the text (now it's
after)?
-If I have enabled the option "wrap text" in the format cell
properties, is it possible to put an arrow in front of every line?

Another minor problem is that I'd like to have a shortcut for that
operation, for example Ctrl+r, but when i insert that in the coding
like as follows, everything is just erased in the cell and I don't even
get the arrows !?:

Sub InsertArrow()
'
' Keyboard Shortcut: Ctrl+r
'
sStr = ActiveCell.Value
Num = 3
sStr1 = sStr & Application.Rept(Chr(218), Num)
ActiveCell.Value = sStr1
Debug.Print sStr1, Len(sStr), Len(sStr1)
With ActiveCell.Characters(Start:=Len(sStr) + 1, _
Length:=Len(sStr1) - Len(sStr)).Font
..Name = "Wingdings"
..FontStyle = "Regular"
..Size = 10
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
End With

End Sub

I'll keep trying, and hopefully someone can still help me further !
Thank you already!

Greets,

Artavar

 

[Tom Ogilvy]

Putting in

' Keyboard Shortcut: Ctrl+r

in the coding does not assign the macro to the Ctrl+r combination.

You would need to select the macro in the macro dialog and click on the
properties button - make the assignment there.

change
Num = 1
sStr1 = Application.Rept(Chr(218), Num) & str

You can take out the Debug.Print line.

 

[Artavar]

Hi again,

I now fixed the shortcut problem, but when I change the code to sStr1
Application.Rept(Chr(218), Num) & *str* , then I get the error messag
Compile error: Argument not optional and str is highlited in the VB
editor....
Can you tell me what is still wrong about it?

Thanks a lot already,

Artava

 

[Dave Peterson]

Try:

sStr1 = Application.Rept(Chr(218), Num) & sStr

But then the loop has to do the font changes at the beginning.

Option Explicit
Sub InsertArrow2()

Dim sStr As String
Dim sStr1 As String
Dim Num As Long

sStr = ActiveCell.Value
Num = 3
sStr1 = Application.Rept(Chr(218), Num) & sStr
ActiveCell.Value = sStr1
'Debug.Print sStr1, Len(sStr), Len(sStr1)
With ActiveCell.Characters(Start:=1, Length:=Num).Font
.Name = "Wingdings"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub

 

[Artavar]

Thanks for that reply Dave,

with the last script I'm getting really close to what I'd like to
have....
Only problem still: imagine I have a cell in which I've checked on
"wrap text" in the cell properties...Now I have five lines of text and
I'd like to have a little arrwo in front of each of them... possible or
not?

Thanks a lot guys,

Artavar

 

[Dave Peterson]

Depends on how you got the lines to wrap.

If you let excel do the work, then I don't know a good way to find out where the
line split.

If you used alt-enters, you could read through that cell and look for that
character (chr(10)) and do stuff based on that.