Help! need some adjustments!
 

You have all been helpful in the past...but i need a little more!. Her
is some code i have sorted out, how do i get it to run on which eve
sheet i switch to and only the one i switch to and if you read what
have so far you will see a comment half way down advice on this as wel
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
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 o
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 no
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 yo
want to play around with it just give me your addres

--
Message posted from http://www.ExcelForum.com

Help! need some adjustments!
 

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/

Help! need some adjustments!
 

Thanks Bob for your suggestion, i moved it but it didnt work at all, an
of course when i went back to macro''s to check it it wasnt there bu
luckily it was when i opened VB editor!

If anyone else has some suggestions i would be happy to recieve them.

Simo

--
Message posted from http://www.ExcelForum.com