Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 6th 21, 11:10 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 52
Default 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?

  #2   Report Post  
Old April 6th 21, 11:24 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,872
Default 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
  #3   Report Post  
Old April 6th 21, 11:39 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 52
Default 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!
  #4   Report Post  
Old April 6th 21, 11:59 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,872
Default 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
  #5   Report Post  
Old April 6th 21, 01:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 52
Default 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


  #6   Report Post  
Old April 6th 21, 01:33 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,872
Default 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
  #7   Report Post  
Old April 6th 21, 04:33 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 52
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Popping out Message Box! deepak Excel Discussion (Misc queries) 4 September 15th 09 03:53 PM
message box keeps popping amelia Excel Programming 4 February 10th 09 02:48 PM
What's with this MS message that keeps popping up? Art Excel Programming 2 March 30th 07 02:52 PM
message box popping up when the selection of a Form combo box changes George Excel Programming 6 March 21st 07 10:31 PM
broken links message still popping up GJR3599 Excel Discussion (Misc queries) 1 March 30th 05 01:36 PM


All times are GMT +1. The time now is 02:54 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017