Text to Columns, can't stop it..

[DonG]

Using Excel 2010...I copy & paste a column of numbers into Cell A1, Sheet 1. Example below.

1.000000 0.002400
0.950000 0.010500
0.900000 0.020200
0.800000 0.042100
0.700000 0.064900
0.600000 0.086800
0.500000 0.105500

Then activate through a Command Button on Sheet 1 that's linked to a 'Text to Columns' macro. Works fine the first time through but clearing the sheet, then entering more data in Col A, it automatically runs the macro again.....Currently I have to close the program and reopen it....I need it to stop this until I click on the Command Button.....Probably something very simple but escapes me.....TIA...

Don

[kev_]

Sounds like you could have something triggering a macro to run automatically
Right click on the sheet tab \ select View Code
- what is in the code window?

What is in the code behind the button?

[DonG]

Please Login or Register  to view this content.

There is the code that is attached to the Button....the Button was just assigned to that Sub, no other code in it...

As you can see the macro also Concantenates the data back into the proper format (separated by a comma)

Don

[xladept]

Maybe:

Please Login or Register  to view this content.

*But , I wonder why you're copying F there at the end??

[kev_]

VBA does not require objects to be selected
avoid selection with ...

Please Login or Register  to view this content.

Like xladept, I wonder why you are copying column F when it is not pasted anywhere
but avoid selection with ...

Please Login or Register  to view this content.

There is nothing the code you provided to make it run more than once
- is this the ONLY code in your workbook?
- suggest you attach a sample workbook so we can look at it (Remember to desensitize any data)

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[DonG]

Using the code posted above I received a Run-time error '1004'.....

Have attached the WB and also a Text file (E207.txt) to use for testing.

The WB when opened shows three 'Command Buttons' and some data in Col L

I would like to hide or protect those populated cells in Col L in some way as they will never change but that data
needs to be available for the next 'BUILD DAT FILE' macro..this is not important at this time.

'PROCESS DATA'
'BUILD DAT FILE'
'CLEAR SHEET'

The populated Cells in Col L contain data that is relevant to every use,
this data is not cleared by the 'CLEAR SHEET' button.

First: Enter data from text file, row 4 to bottom line, in A1. This will populate down Col A.

Then click on 'Process Data', some processing will occur and a small form will open requiring name and thickness.
The required data, in this case, is 'E207' for name and .296 for % of thickness.

Enter that data and click 'Continue' and the data will be further processed into Col F and where needed in Col L

Clicking 'Build Dat File' will further process the data and populate same in Col M into the format required.
Col M is also selected and copied onto clipboard by code for transfer to a text editor.

I'm fairly certain that the problem is in the first module as, the first use runs fine, but does the automatic 'data to columns'
when attempting to use the program a second time without closing and reopening it.

As to why I selected Col F, I can only guess that I thought that was needed for the 'AutoFit' command....In the attached WB
I've commented that out and the program runs successfully, but only on the first run through.....

Please excuse the amateur like code, I'm not a pro at this.....Thanks for your interest in helping me here....

Don

[xlnitwit]

Once you have run Text To Columns, Excel will reapply those settings if you then paste delimited data (it doesn't actually run your code again, unless you have something triggering it). To prevent that, you either restart Excel, or run a Text To Columns operation with no delimiters specified.

[kev_]

Once you have run Text To Columns, Excel will reapply those settings if you then paste delimited data

@xlnitwit -

I was not aware of that, so I tested it
- put data in columnA
- ran TextToColumns with delimiter specified
- pasted fresh delimited data into column A
- nothing happened
- created VBA for TextToColumns with delimiter specified
- ran VBA
- pasted fresh data
- nothing happened

What you say mirrors OP problem - so what you are saying is correct
But I cannot replicate - it. So something must be different. If we find out what is different perhaps that would solve OP problem
Any ideas

Excel 2016 behaves differently to 2010 perhaps??

EDIT
Or are you saying If you "deliberately" run TextToColums again it retains its old settings ?
- that I can replicate

If that is the case, how about resetting everything like this:
(after original TextToColumns add a line in VBA to)
- run "dummy" TextToColumns without a delimiter against a "dummy" cell
(cell must contain text)

[DonG]

I tried to run the TextToColumns with no delimiters after running the program one time. But you need data for it to work, but no matter what cell I
tried to put data in to run the TextToColumns with no delimiters, the TextToColumns immediately runs with delimiters.

Maybe a solution will be to test A1 when the 'Process Data' button is clicked...if A1 has data run with delimiters, if A1 has no data then run the balance of that macro. Pasting the data for the second run into Col A will be processed with delimiter to Col B and C.

Let me play with this a bit and I'll post my results back here....Thanks again for the input.

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[xlnitwit]

You seem to be splitting on a Space, so just select any populated cell, and then:
Data- Text to Columns
Choose Delimited
Click Next
Enable the Tab delimiter and disable all the others
Clear 'Treat consecutive delimiters as one'
Click Finish
Then try pasting your data again

[xlnitwit]

Excel 2016 behaves differently to 2010 perhaps??

I would suspect that must be the difference.

If that is the case, how about resetting everything like this:
(after original TextToColumns add a line in VBA to)
- run "dummy" TextToColumns without a delimiter against a "dummy" cell
(cell must contain text)

Yes, that's basically what I am suggesting to allow the paste to work as normal.

[kev_]

how about putting the reset into the VBA as the last command
- run it against a cell that is guaranteed to contain text

[xlnitwit]

I have just realised that I forgot to mention a crucial piece of information. This auto-split only happens when copying from another non-Office application- e.g. a web page.

[kev_]

Ah - that explains why I could not replicate
Would pasting to a dummy range first and then copy/pasting from there to the correct column fix OP problem?

[DonG]

It works....added a snippet to the clearing macro and tested the WB a few times. Also cleaned up the code a bit. I'll mark it Solved! Thanks to you all..have a great weekend....

Please Login or Register  to view this content.

Don

[xlnitwit]

I doubt it- Excel would simply parse the first paste to numerous columns. A dummy TTC routine is the simplest way to go, in my opinion.