MSExcel - can't see macro button

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
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
 

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?
 
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
 

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??
 
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
 
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
 
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
 

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
 

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.
 
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top