PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Code comonr to all worksheets

Reply
Thread Tools Rate Thread

Code comonr to all worksheets

 
 
Patrick C. Simonds
Guest
Posts: n/a
 
      2nd May 2008
I want the code below to work on all worksheets in the workbook so I placed
it in "ThisWorkbook" but it does not work. It does work if I copy the code
to each worksheet, so what am I missing?


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler:

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("G7:P2041"), Target) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase$(Target.Value)
End If


If Target.Count = 1 And Not Application.Intersect( _
Me.Range("N7:N2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("R7:R2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("T7:T2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("V7:V2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

ErrHandler:
Application.EnableEvents = True
End Sub
 
Reply With Quote
 
 
 
 
Per Jessen
Guest
Posts: n/a
 
      2nd May 2008
On 2 Maj, 02:06, "Patrick C. Simonds" <ordnan...@comcast.net> wrote:
> I want the code below to work on all worksheets in the workbook so I placed
> it *in *"ThisWorkbook" but it does not work. It does work if I copy the code
> to each worksheet, so what am I missing?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> * On Error GoTo ErrHandler:
>
> * If Target.Count = 1 And Not Application.Intersect( _
> * * * *Me.Range("G7:P2041"), Target) Is Nothing Then
> * * Application.EnableEvents = False
> * * * Target.Value = UCase$(Target.Value)
> * * End If
>
> * If Target.Count = 1 And Not Application.Intersect( _
> * * * *Me.Range("N7:N2041"), Target) Is Nothing Then
> * * Application.EnableEvents = False
> * * If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> * * * * * * * * * * * * * * * *And Len(Target.Value) < 5 Then
> * * * Target.Value = Format$(Target.Value, "00\:00")
> * * Else
> * * * Target.Value = UCase$(Target.Value)
> * * End If
> * * End If
>
> * * If Target.Count = 1 And Not Application.Intersect( _
> * * * *Me.Range("R7:R2041"), Target) Is Nothing Then
> * * Application.EnableEvents = False
> * * If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> * * * * * * * * * * * * * * * *And Len(Target.Value) < 5 Then
> * * * Target.Value = Format$(Target.Value, "00\:00")
> * * Else
> * * * Target.Value = UCase$(Target.Value)
> * * End If
> * * End If
>
> * * If Target.Count = 1 And Not Application.Intersect( _
> * * * *Me.Range("T7:T2041"), Target) Is Nothing Then
> * * Application.EnableEvents = False
> * * If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> * * * * * * * * * * * * * * * *And Len(Target.Value) < 5 Then
> * * * Target.Value = Format$(Target.Value, "00\:00")
> * * Else
> * * * Target.Value = UCase$(Target.Value)
> * * End If
> * * End If
>
> * * If Target.Count = 1 And Not Application.Intersect( _
> * * * *Me.Range("V7:V2041"), Target) Is Nothing Then
> * * Application.EnableEvents = False
> * * If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> * * * * * * * * * * * * * * * *And Len(Target.Value) < 5 Then
> * * * Target.Value = Format$(Target.Value, "00\:00")
> * * Else
> * * * Target.Value = UCase$(Target.Value)
> * * End If
> * * End If
>
> ErrHandler:
> * Application.EnableEvents = True
> End Sub

Hi

It's a worksheet event code, so it will only work if it's placed under
each worksheet.

Otherwise put the code in an ordinary module, and call the sub from
each sheet with a worksheet_change event code. But then you will also
need to rewrite the code as you can not use the Target object and Me
which is an object refering to the worksheet.

regards,
Per
 
Reply With Quote
 
FSt1
Guest
Posts: n/a
 
      2nd May 2008
hi
"what am i missing"
first, this is worksheet code. it works on worksheet. it is NOT workbook
code. it does not work in a this workbook module. i don't even work in a
standard module.
go back to the person who wrote this for you and explain you delema.

also see this site
http://www.mvps.org/dmcritchie/excel/getstarted.ht
http://msdn.microsoft.com/office/und.../odc_super.asp
http://www.exceltip.com/excel_links.html

regards
FSt1

"Patrick C. Simonds" wrote:

> I want the code below to work on all worksheets in the workbook so I placed
> it in "ThisWorkbook" but it does not work. It does work if I copy the code
> to each worksheet, so what am I missing?
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ErrHandler:
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("G7:P2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> Target.Value = UCase$(Target.Value)
> End If
>
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("N7:N2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("R7:R2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("T7:T2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("V7:V2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
>
 
Reply With Quote
 
Tom Hutchins
Guest
Posts: n/a
 
      2nd May 2008
You can't use a Worksheet event in the ThisWorkbook module; it is for
Workbook events. The following code for ThisWorkbook works okay for me:

Public OldSht As Worksheet, OldCell As Range

Private Sub Workbook_Activate()
Set OldSht = ActiveSheet
Set OldCell = ActiveCell
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set OldSht = Nothing
Set OldCell = Nothing
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Call ChgToUpper(OldSht, OldCell)
Set OldSht = ActiveSheet
Set OldCell = ActiveCell
End Sub

Private Sub ChgToUpper(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ErrHandler:

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("G7:P2041"), Target) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase$(Target.Value)
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("N7:N2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("R7:R2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("T7:T2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("V7:V2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

ErrHandler:
Application.EnableEvents = True
End Sub

Target is the cell just selected. You want to process the cell you just
left, so we use variables (OldSht and OldCell) to keep track of where we just
were. We assign them a value when the workbook is activated, update them
every time a different cell is selected, and free the memory they use when
the workbook is closed.

Hope this helps,

Hutch

"Patrick C. Simonds" wrote:

> I want the code below to work on all worksheets in the workbook so I placed
> it in "ThisWorkbook" but it does not work. It does work if I copy the code
> to each worksheet, so what am I missing?
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ErrHandler:
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("G7:P2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> Target.Value = UCase$(Target.Value)
> End If
>
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("N7:N2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("R7:R2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("T7:T2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("V7:V2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
>
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      2nd May 2008
There is no event under thisworkbook called Worksheet_Change--and you can't just
make them up.

But there is a workbook_sheetchange event that you can tie into.

Notice that the me that used to point a worksheet (when it was under a worksheet
module) has changed to sh (and is passed to the subroutine.

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ErrHandler:

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("G7:P2041"), Target) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase$(Target.Value)
End If


If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("N7:N2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("R7:R2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("T7:T2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("V7:V2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

ErrHandler:
Application.EnableEvents = True
End Sub



"Patrick C. Simonds" wrote:
>
> I want the code below to work on all worksheets in the workbook so I placed
> it in "ThisWorkbook" but it does not work. It does work if I copy the code
> to each worksheet, so what am I missing?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ErrHandler:
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("G7:P2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> Target.Value = UCase$(Target.Value)
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("N7:N2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("R7:R2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("T7:T2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> If Target.Count = 1 And Not Application.Intersect( _
> Me.Range("V7:V2041"), Target) Is Nothing Then
> Application.EnableEvents = False
> If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
> And Len(Target.Value) < 5 Then
> Target.Value = Format$(Target.Value, "00\:00")
> Else
> Target.Value = UCase$(Target.Value)
> End If
> End If
>
> ErrHandler:
> Application.EnableEvents = True
> End Sub

--

Dave Peterson
 
Reply With Quote
 
 
 
Reply

« macro to run IF condition met | How to AutoLoad Macro »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Code won't run in all worksheets crysclayton@gmail.com Microsoft Excel Programming 6 18th Jul 2006 10:29 PM
Apply code to other worksheets Metrazal Microsoft Excel Programming 4 24th Feb 2006 06:57 AM
Code behind worksheets =?Utf-8?B?TWFyaw==?= Microsoft Excel Programming 2 11th Feb 2005 01:00 PM
Merging worksheets with VBA Code -- HELP! =?Utf-8?B?ZGl2YXJlZWQ=?= Microsoft Excel Misc 7 6th Oct 2004 11:40 PM
Need code to protect worksheets - amount of worksheets varies Sandy Microsoft Excel Programming 1 9th Sep 2003 02:17 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 09:17 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top