[beginner] create macro to insert symbol
 

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.

--
Regards,
Tom Ogilvy

"Artavar" wrote in
message ...

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


--
Artavar
------------------------------------------------------------------------
Artavar's Profile:
http://www.excelforum.com/member.php...o&userid=25029
View this thread: http://www.excelforum.com/showthread...hreadid=385571

[beginner] create macro to insert symbol
 

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


--
Artavar
------------------------------------------------------------------------
Artavar's Profile: http://www.excelforum.com/member.php...o&userid=25029
View this thread: http://www.excelforum.com/showthread...hreadid=385571

[beginner] create macro to insert symbol
 

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.

--
Regards,
Tom Ogilvy

"Artavar" wrote in
message ...

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


--
Artavar
------------------------------------------------------------------------
Artavar's Profile:
http://www.excelforum.com/member.php...o&userid=25029
View this thread: http://www.excelforum.com/showthread...hreadid=385571

[beginner] create macro to insert symbol
 

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

--
Artava
-----------------------------------------------------------------------
Artavar's Profile: http://www.excelforum.com/member.php...fo&userid=2502
View this thread: http://www.excelforum.com/showthread.php?threadid=38557

[beginner] create macro to insert symbol
 

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 wrote:

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 message
Compile error: Argument not optional and str is highlited in the VBA
editor....
Can you tell me what is still wrong about it?

Thanks a lot already,

Artavar

--
Artavar
------------------------------------------------------------------------
Artavar's Profile: http://www.excelforum.com/member.php...o&userid=25029
View this thread: http://www.excelforum.com/showthread...hreadid=385571

--

Dave Peterson

[beginner] create macro to insert symbol
 

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


--
Artavar
------------------------------------------------------------------------
Artavar's Profile: http://www.excelforum.com/member.php...o&userid=25029
View this thread: http://www.excelforum.com/showthread...hreadid=385571

[beginner] create macro to insert symbol
 

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.

Artavar wrote:

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

--
Artavar
------------------------------------------------------------------------
Artavar's Profile: http://www.excelforum.com/member.php...o&userid=25029
View this thread: http://www.excelforum.com/showthread...hreadid=385571

--

Dave Peterson