1

I have a file with a few functions. One of them divides text to columns in selected range:

    Sub txt2clmns()
    Dim rng As Range
    Set rng = [i5]
    Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
    rng.TextToColumns _
            Destination:=Range("J5"), _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=True, _
            Semicolon:=True, _
            Comma:=False, _
            Space:=False, _
            Other:=False
    rng.TextToColumns _
            Destination:=rng, _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=True, _
            Semicolon:=False, _
            Comma:=False, _
            Space:=False, _
            Other:=False
End Sub

Is there a simpler way to refresh the settings for text-to-column function without applying it second time with standard parameters (only tab check box is checked)?

Because in case I call it only once, Excel automatically divide all strings with semicolon when I paste them directly in the formula tab.

Thank you for any help.

BangBoy
  • 45
  • 6

1 Answers1

0

I think simple message box at the beginning of your macro, allowing user to choose whether Tab should be set to true or not, would do the trick:

Dim TabBool As Boolean
'if user selects Yes on the form, then result value is 6
TabBool = ( MsgBox("Should Tab parameter be set to True?", vbYesNo) = 6 )

Then use it: Tab:=TabBool

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • Michał, the issue is, that the setting should be reset to default anyways. I wanted to know whether it can be done without calling the text to columns function second time – BangBoy Aug 13 '17 at 14:11