Hi all,

I tried to do "Text to Columns" with VBA and it works fine except
while it is executing it, msg box comes up and says " Replace
destination Cells? with Ok and Cancel button". Obviously if I press
"OK" I get the result I want. However How do I make it ignore the msg
box and execute it or how do I set "OK" without msg box popping up?



Selection.TextToColumns Destination:=Range("C1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="-", FieldInfo:=Array(Array(1, 2), Array(2, 2)),
TrailingMinusNumbers:=True

Following turns off the message. Insert it at the beginning of the code:-
Application.DisplayAlerts = False

Following turns them back on again. Ensure that you do insert this at the
end of the code:-
Application.DisplayAlerts = True

--

Regards,

OssieMac


"James8309" wrote:

Hi all,

I tried to do "Text to Columns" with VBA and it works fine except
while it is executing it, msg box comes up and says " Replace
destination Cells? with Ok and Cancel button". Obviously if I press
"OK" I get the result I want. However How do I make it ignore the msg
box and execute it or how do I set "OK" without msg box popping up?



Selection.TextToColumns Destination:=Range("C1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="-", FieldInfo:=Array(Array(1, 2), Array(2, 2)),
TrailingMinusNumbers:=True