Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Code comonr to all worksheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 343
Default Code comonr to all worksheets
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 QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 703
Default Code comonr to all worksheets
On 2 Maj, 02:06, "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

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 QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,942
Default Code comonr to all worksheets
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 QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,069
Default Code comonr to all worksheets
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 QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Code comonr to all worksheets
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 QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add VBA code to multiple WorkSheets Al Excel Programming 0 May 24th 05 04:40 PM
Code behind worksheets Mark Excel Programming 2 February 11th 05 01:00 PM
Adding code to worksheets mgs[_2_] Excel Programming 0 January 4th 04 11:33 PM
Need code to protect worksheets - amount of worksheets varies Sandy[_3_] Excel Programming 1 September 9th 03 02:17 AM


All times are GMT +1. The time now is 03:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement