Excel VBA. Improve the code

Let say there is a code. I need to make button that run the code in the sheet "data" that will be located in the cell I7

Option Explicit


Sub ConvertToTabDelimited()
    Dim TempSht As Worksheet
    Dim rRng As Range
    Dim sFullPath As String
    sFullPath = ThisWorkbook.Path & Application.PathSeparator
    With Application
        .ScreenUpdating = False
        On Error GoTo err_quit
        With Sheet1
            Set rRng = .Range(.Cells(8, 2), .Cells(.Rows.Count, 7).End(xlUp))
        End With
        Set TempSht = Sheets.Add
        rRng.Copy TempSht.Range("A1")
        TempSht.Copy
        ActiveWorkbook.SaveAs Filename:=sFullPath & "testing.txt", FileFormat:=xlText, CreateBackup:=False
        .DisplayAlerts = False
        TempSht.Delete
        ActiveWorkbook.Close True
        .DisplayAlerts = True

err_quit:
        .ScreenUpdating = True
    End With
End Sub

Open in new window

For-forum.xlsx
SunnyXAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I've attached a workbook with a button that executes your code.
28689311.xlsm
0
Martin LissOlder than dirtCommented:
To be a "B" or not to be a "B":) In any case Sunny please see this EE article on grading which says the following, and consider changing the grades to A's. If you Request attention a moderator will help you do that.

B is the grade given for acceptable solutions, or a link to an acceptable solution. A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.
0
Roy CoxGroup Finance ManagerCommented:
Martin

I could have added a button easily, but surely it's better for the OP to learn how to do it himself.
0
Roy CoxGroup Finance ManagerCommented:
The following instructions apply to Excel 2007 onwards, first you need to make sure that the Developer Tab is available
•Click the Microsoft Office Button, and then click Excel Options.
•Click Popular, and then select the Show Developer tab in the Ribbon check box. Probably the most commonly created control object for worksheets is the button. This is because the most common use of buttons is to run macros which you have associated with a workbook or display a UserForm. You can insert buttons in your worksheet by use of the Button tool on the Forms toolbar:

If you are using Microsoft Excel 2007 or later you need to make sure that the Developer Tab is available. There is no Forms toolbar, instead, you must use the Developer tab of the ribbon and then click Insert in the Controls group. The resulting palette shows a number of different controls. In earlier versions you need to have the Forms Toolbar visible. To do this from the View menu click ToolBars and select Forms

The next requirement is the same for all versions of MS Excel

You should click the Button icon, and then follow the four steps listed below.

1. Click on the Button tool.

2. Click in your workbook where you want a corner of the button to appear, but don't release the mouse button.

3. Move the mouse to specify the size of the button you want.

4. Release the mouse button.

Excel immediately displays the Assign Macro dialog box, offering you the opportunity to assign a macro to the button. You will see that the dialog box shows a list of previously defined macros, along with a suggested name for the macro to be assigned to this button. The suggested name is comprised of the default name of the button itself (something like Button1) combined with the action that will start the macro (Click). This macro name (Button1_Click) will appear very familiar to people that have programmed in Visual Basic before, since it conforms to the standard way of naming event handlers. Event handlers are nothing but programming code designed to handle a specific event, such as an object like a button being clicked with the mouse.

To finish with the Assign Macro dialog box, select a macro you want assigned to this new button and then click on OK. You can then change the title appearing on the button by clicking your mouse within the button text and entering a new title.

Once the button is finished in this manner, the macro associated with this button will be run whenever anyone clicks on it with the left mouse button. If you use the right mouse button instead, you will see a menu that allows you to delete the button or change the macro assigned to the button.

ActiveX controls can be added from the Controls ToolBox or the Excel 2007 Developer tab as above. These are more complex than Forms Controls and have more properties and can contain code.
For-forum.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.