1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MSExcel - can't see macro button

Discussion in 'Business Applications' started by cgjoker, Sep 15, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. cgjoker

    cgjoker Thread Starter

    Joined:
    Aug 13, 2003
    Messages:
    205
    Hello.

    I have created a macro on a worksheet and created a button on the toolbar that runs the macro.

    I have saved this worksheet to a server but when others open the worksheet, they cannot see the button but the macro is there.

    2 questions.

    1. Is there any way to make the button visible to everyone when they open the worksheet.

    2. What or how could I have the macro run automatically when the worksheet is closed; this would eliminate the need of the button on the tool bar.

    Thanks.... cg.
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> have the macro run automatically
    >> when the worksheet is closed

    Use an event procedure in the This Worbook module. Right-click the XL icon next to File in the menu bar, choose View Code to access it. You'll need something like -

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    (do stuff)

    ActiveWorkbook.Save

    End Sub

    HTH,
    Andy
     
  3. cgjoker

    cgjoker Thread Starter

    Joined:
    Aug 13, 2003
    Messages:
    205
    Thanks... excuse my ignorance... but what exactly goes in the section that you put (do stuff).... do I place my macro here?
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Yes, replace (do stuff) with your code.

    Soz,
    Andy
     
  5. RandyG

    RandyG

    Joined:
    Jun 26, 2000
    Messages:
    7,762
    To get into the VB Module, press Alt+F11 while the workbook is open, paste the code there
     
  6. cgjoker

    cgjoker Thread Starter

    Joined:
    Aug 13, 2003
    Messages:
    205
    don't know why but my code is now not working properly....

    can you take a look at it ?

    Dim xlPath As String
    Dim csvPath As String
    Dim myExcel As Object
    Set myExcel = CreateObject("Excel.Application")

    xlPath = "E:\DAS\XML\Worksheets\" + Dir("\*.xls") '
    Do While xlPath <> ""
    csvPath = Mid(xlPath, 1, Len(xlPath) - 4) & ".csv" 'create new file name with .csv extension
    myExcel.Visible = True '
    myExcel.Workbooks.Open xlPath 'open file
    myExcel.ActiveWorkbook.SaveAs Filename:=csvPath, FileFormat:=xlCSV, CreateBackup:=False 'save as csv file
    myExcel.ActiveWorkbook.Close False 'close file
    xlPath = "E:\DAS\XML\Worksheets" + Dir
    Loop
    myExcel.Quit
    Set myExcel = Nothing

    can you see what is wrong with it??
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Are you getting an error message? On what line?
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Ditto DB.

    The main purpose seems to be to open all files in the specified dir & save them as CSV. It fell over for me at

    ActiveWorkbook.SaveAs Filename ...

    since I had a file open that wasn't in the specified dir. Did you hit the same wall?

    Rgds,
    Andy
     
  9. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    OK, I found where whoever cribbed your code got it from --

    http://www.vbcity.com/forums/topic.asp?tid=6480

    *but* you'll have to register with vbcity.com to access it fully (takes 5 mins). The original request was "I would like to Open an Excel Workbook with the .xls extension and save that same Workbook as a xlCSV (.csv) type document". OP's rejoinder was that SaveAs method failed, a follow-up stated that

    Const xlCSV = 6

    had been omitted from the declarations. So basically, it's still a case of (a) what are *you* trying to do? (b) where's it going wrong?

    Rgds,
    Andy
     
  10. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    As to the first question:

    I have created a macro on a worksheet and created a button on the toolbar that runs the macro.I have saved this worksheet to a server but when others open the worksheet, they cannot see the button but the macro is there.


    This may be simplistic and I have only just started my coffee but, did you mean you placed the button on your TOOLBAR or in the WORKSHEET?

    If it is on the WORKSHEET they should be able to see it!

    - Castleheart
     
  11. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  12. cgjoker

    cgjoker Thread Starter

    Joined:
    Aug 13, 2003
    Messages:
    205
    well... at first I had a button on the tool bar of the worksheet that executed this macro.

    it worked just fine but it seemed that the button only appeared on the toolbar for myself and no one else, although the macro was still there.

    then i decided it would be more beneficial for me to just have it run automatically when the user closes the worksheet.

    basically id like the following to happen.

    1. a user uses the template worksheet that has the macro in it.
    2. the user saves the worksheet under a different name as .xls...
    3. when the user closes the worksheet the macro is executed and converts all files in the specified directory (in the macro) to .csv format.

    I seem to be getting an error on :

    myExcel.Workbooks.Open xlPath 'open file
     
  13. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    >>template worksheet

    Is it an XLT file?
     
  14. cgjoker

    cgjoker Thread Starter

    Joined:
    Aug 13, 2003
    Messages:
    205
    hey there.... just to let you know i resolved all my issues regarding this macro.

    thanks to all of you for your time and suggestions.

    sincerely, cg.
     
  15. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Care to share? That's the whole idea of tech support sites...sharing what worked.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/164953

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice