How to suppress a message box from popping up during a macro
 

Hi everybody!
When I run a certain macro I'm receiving the following message box from Excel which I have to always answer with "Ok":

"There's already data here. Do you want to replace it?"

It happens when I run a command to split a text to columns.
The text (in cell: F13) is e.g.: BLACK+NATURAL+STONE


The code looks like this:
Range("I13:K13").Select
ActiveSheet.Unprotect
Selection.ClearContents
Range("F13").Select
Selection.Copy
' separating the names of single components into Colour 1, 2 and 3
Selection.TextToColumns Destination:=Range("I13"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="+", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True


How can I suppress the message from popping up?

How to suppress a message box from popping up during a macro
 

Hi Norbert,

Am Tue, 6 Apr 2021 03:10:32 -0700 (PDT) schrieb Norbert:

It happens when I run a command to split a text to columns.
The text (in cell: F13) is e.g.: BLACK+NATURAL+STONE

The code looks like this:
Range("I13:K13").Select
ActiveSheet.Unprotect
Selection.ClearContents
Range("F13").Select
Selection.Copy
' separating the names of single components into Colour 1, 2 and 3
Selection.TextToColumns Destination:=Range("I13"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="+", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

try:

Range("F13").TextToColumns Destination:=Range("I13"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="+", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True


Regards
Claus B.
--
Windows10
Microsoft 365 for business

How to suppress a message box from popping up during a macro
 

On Tuesday, 6 April 2021 at 12:24:52 UTC+2, Claus Busch wrote:

try:

Range("F13").TextToColumns Destination:=Range("I13"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="+", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True


Regards
Claus B.
--
Windows10
Microsoft 365 for business

Hi Claus,
thanks for your help!
Unfortunately, I'm getting the exact same popup message.

My code right now:

Sub MELANGE_YARN_DELIVERY()

Application.ScreenUpdating = False

Range("I13:K13").Select
ActiveSheet.Unprotect
Selection.ClearContents
Range("F13").TextToColumns Destination:=Range("I13"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="+", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True


What I don't understand: what does Excel mean by: "There is already data here!"
I deleted the data before the TextToColumns command!

How to suppress a message box from popping up during a macro
 

Hi Norbert,

Am Tue, 6 Apr 2021 03:39:55 -0700 (PDT) schrieb Norbert:

Unfortunately, I'm getting the exact same popup message.

My code right now:

Sub MELANGE_YARN_DELIVERY()

Application.ScreenUpdating = False

Range("I13:K13").Select
ActiveSheet.Unprotect
Selection.ClearContents
Range("F13").TextToColumns Destination:=Range("I13"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="+", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True

for me following code works even if there are values in I13:K13:

With ActiveSheet
.Unprotect
.Range("I13:K13").Clear
.Range("F13").TextToColumns Destination:=.Range("I13"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="+", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
End With


Regards
Claus B.
--
Windows10
Microsoft 365 for business

How to suppress a message box from popping up during a macro
 

for me following code works even if there are values in I13:K13:

With ActiveSheet
.Unprotect
.Range("I13:K13").Clear
.Range("F13").TextToColumns Destination:=.Range("I13"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="+", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
End With
Regards
Claus B.
--
Windows10
Microsoft 365 for business


Hi Claus,
now it works, the only thing I realised is that it clears also the formats of range I13:K13 and when I changed your
code to:
..Range("I13:K13").ClearContents (instead of: .Range("I13:K13").Clear)

it comes up with the same message box as before. It would be nice if I could keep my formatting but if it is not
possible, I can also live with what it looks now.

Regards,
Norbert

How to suppress a message box from popping up during a macro
 

Hi Norbert,

Am Tue, 6 Apr 2021 05:05:59 -0700 (PDT) schrieb Norbert:

now it works, the only thing I realised is that it clears also the formats of range I13:K13 and when I changed your
code to:
.Range("I13:K13").ClearContents (instead of: .Range("I13:K13").Clear)

it comes up with the same message box as before. It would be nice if I could keep my formatting but if it is not
possible, I can also live with what it looks now.

try it this way:

With ActiveSheet
.Unprotect
.Range("I13:K13").ClearContents
Application.DisplayAlerts = False
.Range("F13").TextToColumns Destination:=.Range("I13"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="+", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Application.DisplayAlerts = True
End With


Regards
Claus B.
--
Windows10
Microsoft 365 for business

How to suppress a message box from popping up during a macro
 

Hi Claus,
thank you very much. It works like a "bomb".

Regards,
Norbert