UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Worksheet Module?    
 
   
zocker
post Jun 29 2006, 03:36 AM
Post #1

Utterly Eccentric and Moderator
Posts: 3,663
From: Bristol / Ipswich / Spain
Greetings all!

I have just written a cunning little routine that finds Cells which have certain data..This is it:

CODE
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error GoTo errSub:
Dim strSearch As String
strSearch = InputBox("What Property? ", "Find a Property or Owner  ")

'Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows).Activate

Cells.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
'MsgBox Target.Address
errSub:
    If Err.Number = 91 Or Err.Number = 13 Then
        MsgBox "Not found...Try again", , "Co Name"
        Exit Sub
    End If
End Sub


This works really well but (and this is the Question) I have 12 sheets that I want to apply it to...so is there some way I can use it 'globally' and call it also 'globally' from each worksheet?

Many thanks in advance!
Go to the top of the page
 
+
StuKiel
post Jun 29 2006, 04:57 AM
Post #2

UtterAccess Ruler
Posts: 1,182
From: Norfolk UK
Hi James,

You could move this code into the workbook module in the event:

Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)

As you can see this has the additional reference for the sheet from which the event was triggered.
Go to the top of the page
 
+
zocker
post Jun 29 2006, 05:27 AM
Post #3

Utterly Eccentric and Moderator
Posts: 3,663
From: Bristol / Ipswich / Spain
Thx.....I'll have a go at that!

Z
Go to the top of the page
 
+
StuKiel
post Jun 29 2006, 05:48 AM
Post #4

UtterAccess Ruler
Posts: 1,182
From: Norfolk UK
Glad to help.
Go to the top of the page
 
+
zocker
post Jun 29 2006, 10:03 AM
Post #5

Utterly Eccentric and Moderator
Posts: 3,663
From: Bristol / Ipswich / Spain
Thx for your help.........tho it throws this error:

Procedure declaration does not match description of event or procedure having same name

Got any ideas? I dont have any other 'same names' and this error pops on an attempted compile!

Clearly in right direction....event fires any sheet, any cell!

Cheers
Z
Go to the top of the page
 
+
Luceze
post Jun 29 2006, 12:52 PM
Post #6

UtterAccess VIP
Posts: 2,601
From: Dallas, Texas USA
Hey Z,

Make sure you use Stu's event in the ThisWorkbook module rather than the sheet or standard module.

HTH,
Go to the top of the page
 
+
zocker
post Jul 1 2006, 06:14 AM
Post #7

Utterly Eccentric and Moderator
Posts: 3,663
From: Bristol / Ipswich / Spain
Thx, got distracted by other projects, i'll start on it again Monday.
Z
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 10:40 PM