Cell value is preceded by an apostrophe.

On trigger to that cell, I need to detect and...
- show msgbox "apstrophe detected"
- remove apstrophe
- show msgbox "apstrophe removed"


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=395245

Hi,
This assumes first character is apstrophe:

If Left(ActiveCell, 1) = "," Then
MsgBox " Apostrophe detected "
Range("a1") = Replace(ActiveCell, ",", "")
' Or
Range("a1") = Right(ActiveCell, Len(Range("a1")) - 1)
MsgBox " Apostrophe removed "
End If

"ilyaskazi" wrote:


Cell value is preceded by an apostrophe.

On trigger to that cell, I need to detect and...
- show msgbox "apstrophe detected"
- remove apstrophe
- show msgbox "apstrophe removed"


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=395245

I want Apostrophe = "*::'::*"
and not comma = "*::-,-::*

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=39524

If Left(ActiveCell, 1) = "'" Then
MsgBox " Apostrophe detected "
Range("a1") = Replace(ActiveCell, "'", "")
' Or
Range("a1") = Right(ActiveCell, Len(Range("a1")) - 1)
MsgBox " Apostrophe removed "
End If


"ilyaskazi" wrote:


I want Apostrophe = "*::'::*"
and not comma = "*::-,-::*"


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=395245

Oops again! Sorry!

Range("a1") = Replace(ActiveCell, "'"' "")

"Toppers" wrote:

If Left(ActiveCell, 1) = "'" Then
MsgBox " Apostrophe detected "
Range("a1") = Replace(ActiveCell, "'", "")
' Or
Range("a1") = Right(ActiveCell, Len(Range("a1")) - 1)
MsgBox " Apostrophe removed "
End If


"ilyaskazi" wrote:


I want Apostrophe = "*::'::*"
and not comma = "*::-,-::*"


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=395245

Dear Toppers,

Removing apostrophe is not the problem for me. Problem is to detect and
show msgbox saying "Apostrophe detected"

Removing is working perfect if seprated from the if loop.
Please check with the if loop to detect.

Regards
ilyaskazi


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=395245

Is there anybody who can help me with this issue plz?

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=39524

edit- replace
against <find what---- type
"
leave blank <replace with
click <replace all

is this what you want.



ilyaskazi wrote in
message ...

Is there anybody who can help me with this issue plz??


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=395245

Hi,

a single apostrophe at the beginning of a cell is a prefix for formatting
a cell. it is put in a cell, however, it is not a part of cell's value but
cell's format. so you can delete it by using EditClearFormts or pasting
formats as well. (cell values will not be changed in these ways)
Value property of Range object doesn't return it. but PrefixCharacter
property does. for example,

Sub Test()
Dim r As Range
Dim Ret As Variant

Set r = ActiveSheet.UsedRange

Ret = r.PrefixCharacter
If IsNull(Ret) Then Ret = "'"
If Ret = "" Then
MsgBox "no prefix detected"
Else
MsgBox "prefix detected"
End If
End Sub

however, if you want to remove the prefix and reenter cell values
(i.e. if you want to convert from a text to a number, date, blank, etc.),
shouldn't you detect all text constants and reenter them?
because a cell without a prefix may also have a text as if a number, date,
blank, etc. (on usual sheet).

--
HTH,

okaizawa

Not sure where you are going with the IsNull, but with an empty activecell
(not even in the used range).

? isnull(activecell.PrefixCharacter)
False

? activecell.PrefixCharacter = ""
True

There are very few uses in Excel VBA that I have come across for IsNull

--
Regards,
Tom Ogilvy

"okaizawa" wrote in message
...
Hi,

a single apostrophe at the beginning of a cell is a prefix for formatting
a cell. it is put in a cell, however, it is not a part of cell's value but
cell's format. so you can delete it by using EditClearFormts or pasting
formats as well. (cell values will not be changed in these ways)
Value property of Range object doesn't return it. but PrefixCharacter
property does. for example,

Sub Test()
Dim r As Range
Dim Ret As Variant

Set r = ActiveSheet.UsedRange

Ret = r.PrefixCharacter
If IsNull(Ret) Then Ret = "'"
If Ret = "" Then
MsgBox "no prefix detected"
Else
MsgBox "prefix detected"
End If
End Sub

however, if you want to remove the prefix and reenter cell values
(i.e. if you want to convert from a text to a number, date, blank, etc.),
shouldn't you detect all text constants and reenter them?
because a cell without a prefix may also have a text as if a number, date,
blank, etc. (on usual sheet).

--
HTH,

okaizawa

Working Perfect and it is solved now as per my requirement.

thankyou: okaizawa, Tom Ogilv

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=39524