 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2007Script to make text in uppercase, on workbook level, but not for other opened workbooks.
Hi, (A) I do have undermentioned script, but I cant't get it worked. I want to have cells changed to uppercase after Enter-command. (B) How to have this script for all sheets, so to have it on workbook level? And, how to avoid that other excel documents that are opened also have the text capitalized? Thank you. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Error_handler With Target If Not .HasFormula Then Application.EnableEvents = False If Target.Row = 10 Then Target.Value = UCase(Target.Value) Target.Value = UCase(Target.Value) Application.EnableEvents = True End If End With Error_handler: Resume Next Const WS_RANGE As String = "D4:AJ380" On Error GoTo Error_handler Application.EnableEvents = False With Target If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Not Target.HasFormula Then Target.Value = UCase(Target.Value) End If End If End With Error_handler: Application.EnableEvents = True This should be all that you need Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Const WS_RANGE As String = "D4:AJ380" On Error GoTo Error_handler Application.EnableEvents = False With Target If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Not Target.HasFormula Then Target.Value = UCase(Target.Value) End If End If End With Error_handler: Application.EnableEvents = True End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi, > [quoted text clipped - 36 lines] > Error_handler: > Application.EnableEvents = True > This should be all that you need > [quoted text clipped - 68 lines] > > Error_handler: > > Application.EnableEvents = True I get an error message: Method or data member not found at .Range in: If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then > I get an error message: Method or data member not found > > at .Range in: If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing > Then If looks like it will work if you change the 'Me' object to 'ActiveSheet'... If Not Intersect(Target, ActiveSheet.Range(WS_RANGE)) Is Nothing Then Rick Remember that this is a worksheet event and the code goes in a worksheet module. Read Bob's instructions one more time. <<snipped>> > > 'This is workbook event code. > > 'To input this code, right click on the Excel icon on the worksheet > > '(or next to the File menu if you maximise your workbooks), > > 'select View Code from the menu, and paste the code <<snipped>> > I get an error message: Method or data member not found > > at .Range in: If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing > Then SignatureDave Peterson > > > (B) How to have this script for all sheets, so to have it on workbook > > > level? And, how to avoid that other excel documents that are opened [quoted text clipped - 3 lines] > Remember that this is a worksheet event and the code goes in a worksheet > module. ??? Bob said it was workbook event code and the OP said he wanted it to work on all sheets in his open workbook. Rick Oops. You (and Bob) are right. The line should be changed to: If Not Intersect(Target, Sh.Range(WS_RANGE)) Is Nothing Then > > > > (B) How to have this script for all sheets, so to have it on workbook > > > > level? And, how to avoid that other excel documents that are opened [quoted text clipped - 10 lines] > > Rick SignatureDave Peterson > Oops. You (and Bob) are right. > > The line should be changed to: > If Not Intersect(Target, Sh.Range(WS_RANGE)) Is Nothing Then And Bob is wrong (as you noted). I could have sworn I posted a correction to that. But Bob was correct about how to install the macro. (Sometimes, when someone (me) only glances at the code, it can all look the same. I saw the me reference, so I thought it was a worksheet_change event. I should have glanced up!) > > Oops. You (and Bob) are right. > > [quoted text clipped - 3 lines] > And Bob is wrong (as you noted). I could have sworn I posted a correction to > that. SignatureDave Peterson
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.