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

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