Change if from a worksheet event to a workbook event. Put the code in
Thisworkbook code module and replace
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
with
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Look at Find in help for nthe other bit.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Simon Lloyd " wrote in message
...
You have all been helpful in the past...but i need a little more!. Here
is some code i have sorted out, how do i get it to run on which ever
sheet i switch to and only the one i switch to and if you read what i
have so far you will see a comment half way down advice on this as well
please!
thanks, Simon.
Option Explicit
Dim myrange As Range
Dim isect As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set myrange = Range("A1:D20")
Set isect = Application.Intersect(myrange, Target)
If isect Is Nothing Then
MsgBox "Select Core, 1st, 2nd, 3rd or 4th Skill" & " or Enter a
Skill Competency Spot!"
Exit Sub
End If
Dim t1 As String
Dim I1 As Integer
Dim I2 As Integer
I1 = MsgBox("This Will Allow You To Enter a Skill, Continue?",
292, "Skills input")
If I1 = 6 Then
For I2 = 1 To 3
t1 = InputBox("name", "input", "")
'this next line i would like to get it to look up a range on
another sheet and check for 1
'of the values entered in t1
If t1 = "mark" Or t1 = "paul" Or t1 = "eric" Or t1 = "john"
Then
ActiveCell = t1
Exit Sub
Else
Worksheets("hidden").Visible = True
Worksheets("hidden").Select
ActiveSheet.Range("a2").Select
Selection.Insert Shift:=xlDown
Selection = t1
Worksheets("hidden").Visible = False
End If
Next 'I2
MsgBox "Please try again " & Chr(13) & "Skill " & t1 & " Entry not
recognised "
MsgBox "Please Contact Training Dept to Add Skill Title!!"
End If
End Sub
p.s your quite welcome to have the test workbook e-mailed to u if you
want to play around with it just give me your address
---
Message posted from http://www.ExcelForum.com/