Solved: Help:Switch automatic changing case ON/OFF?

[icegg]

I need words put into an active sheet to be in proper case on and off. I only know I can use Workbook_SheetChange() in "thisworkbook". But it is always on, plus I do not like to put any macro in the workbook.
I am trying to make an add-in to do this.
Is that possible to use a toggle button in a userform to turn a "Sub Workbook_SheetChange()" on and off?
Thanks.


Sub Workbook_SheetChange(ByVal ActiveSheet As Object, ByVal Target As Range)
If Not Intersect(Target, Range(Cells(15, 1), Cells.SpecialCells(xlCellTypeLastCell))) Is Nothing Then
If Not Target.HasFormula Then
Target.Value = Application.Proper(Target.Value)
End If
End If

End Sub

[matthewspatrick]

Not sure what you mean by toggling. Please explain what you mean about having the event code be "on" or "off".

[icegg]

Sorry for the confusion. I knew I did not make it clear :( .
Here is what I want:
I want a toggle button or whatever control in a userform, hopefully in an add-in. When I turn it on, every word I put into the sheet is converted into proper case automatically. When I trun it off, every word is as is.
Is this possible?

Thanks.

[icegg]

I've made one add-in.
I borrowed some codes from DRJ's : h0t0tp://vbaexpress.com/kb/getarticle.php?kb_id=298
Also inspired by lenze's code:
h0t0tp://vbaexpress.com/kb/getarticle.php?kb_id=882

Could anybody please check the errors for me?

I have found one so far and no clue how to solve it:
The code is not supposed to change anything above row 15, but sometimes it does, especially when I open a new blank workbook or add a blank sheet with AlwaysTitle Off and turn it on before type anyting.

Another problem is:
If AlwaysTitle is On before I open a new workbook, I have to turn the switch off and on again for the code to be effective on that new workbook. Can this be improved?

Thanks a lot.

[mdmackillop]

Add a custom document property "Status" of type YesNo
Sub Toggle()
ActiveWorkbook.CustomDocumentProperties("Status").Value _
= Not ActiveWorkbook.CustomDocumentProperties("Status").Value
End Sub



Sub Workbook_SheetChange(ByVal ActiveSheet As Object, ByVal Target As Range)
If ActiveWorkbook.CustomDocumentProperties("Status").Value = False Then Exit Sub
If Not Intersect(Target, Range(Cells(15, 1), Cells.SpecialCells(xlCellTypeLastCell))) Is Nothing Then
If Not Target.HasFormula Then
Target.Value = Application.Proper(Target.Value)
End If
End If
End Sub

[Bob Phillips]

Sorry for the confusion. I knew I did not make it clear :( .
Here is what I want:
I want a toggle button or whatever control in a userform, hopefully in an add-in. When I turn it on, every word I put into the sheet is converted into proper case automatically. When I trun it off, every word is as is.
Is this possible?

Thanks. I would do this by adding a custom button to a toolbar. That button would have a caption stating whether it was proper-case transforming or leaving alone. Clicking the button would toggle its state.

[icegg]

Thanks guys. mdmackillop's idea is pretty inspiring.

However, I do not want to put any macros into my clients workbooks. I borrowed some of DRJ's codes and used "Application.EnableEvents = False/True" to make an add-in. To be honest, I just started using VBA recently and I do not understand most of the codes, especially the "ClassInitialize()" procedure and "App" Class Module. Although the add-in "works", I am afraid sometime it will blow up. I have alse reported some problems above. Could you guys take a look at the code of the add-in above and give some correction and suggestion? Appreciate it.

[Bob Phillips]

Try this

[icegg]

Although I have no clue how you make it, it works well, XLD. Appreciated it. :)
One more favor, how to tell whether the last cell is above row 15 or not?