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

[Norbert]

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?

[Claus Busch]

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

[Norbert]

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!

[Claus Busch]

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

[Norbert]

> 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

[Claus Busch]

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

[Norbert]

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

Regards,
Norbert