Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro Glitch Causing Crash
Hello all! I’m running into some problems with a spreadsheet I’m making. I’m also fairly new to Excel, so I have no idea how to fix them. Now, the big goal right now is to get a spreadsheet I’ve made to save itself automatically as an HTML file whenever the worksheet is saved conventionally with ctrl+s or file-save, etc. I currently have the following code in the ThisWorkbook file. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\g9002741\My Documents\System\EngineeringHealthGauges.htm" _ , FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False Application.DisplayAlerts = True End Sub Now, whenever I save, excel crashes and the changes are lost. Anyone know how to fix my code or just get it to do what I want? Also, I’ve also been wanting to put text in charts that changes based on changes to some sort of source cell in the worksheet. That is, I’d like titles and other text boxes to change when some cell in the worksheet does. -- Danimagus |
#2
|
|||
|
|||
If you surround your code with:
application.enableevents = false 'your code application.enableevents = true Does it work the way you want? If you want to save the original .xls workbook AND save a copy as .htm, you could try this: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim tempWkbk As Workbook Dim myStr As String myStr = Environ("temp") & "\somename.xls" ActiveWorkbook.SaveCopyAs Filename:=myStr Application.EnableEvents = False Set tempWkbk = Workbooks.Open(Filename:=myStr) Application.DisplayAlerts = False tempWkbk.SaveAs _ Filename:="c:\my documents\excel\EngineeringHealthGauges.htm", _ FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False Application.DisplayAlerts = True tempWkbk.Close savechanges:=False Application.EnableEvents = True Cancel = False 'save the workbook. End Sub It saves a copy of the workbook to your %temp% folder. Then it opens that workbook, saves it as .htm and closes the .htm workbook. Then the original .xls is saved as a .xls file. I changed the folder name for my testing. Change it back to what you need. Danimagus wrote: Hello all! I’m running into some problems with a spreadsheet I’m making. I’m also fairly new to Excel, so I have no idea how to fix them. Now, the big goal right now is to get a spreadsheet I’ve made to save itself automatically as an HTML file whenever the worksheet is saved conventionally with ctrl+s or file-save, etc. I currently have the following code in the ThisWorkbook file. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\g9002741\My Documents\System\EngineeringHealthGauges.htm" _ , FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False Application.DisplayAlerts = True End Sub Now, whenever I save, excel crashes and the changes are lost. Anyone know how to fix my code or just get it to do what I want? Also, I’ve also been wanting to put text in charts that changes based on changes to some sort of source cell in the worksheet. That is, I’d like titles and other text boxes to change when some cell in the worksheet does. -- Danimagus -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Charts causing program to crash | Charts and Charting in Excel | |||
Macro and If Statement | Excel Discussion (Misc queries) |