Bob,

I dont think i can merge the code as 1 is a workbook event and works o
the same range on each sheet but the worksheet event works on differen
ranges on each sheet, here is the work book code it's out of the tes
book and not the working program but you will get the gist!

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVa
Target As Range)
Dim myrange As Range
Dim isect As Range
If Sh.Name = "Hidden" Then Exit Sub
Set myrange = Sh.Range("E1:G20")


Set isect = Application.Intersect(myrange, Target)
If isect Is Nothing Then

Exit Sub
End If
Sheets("Sheet1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Dim t1 As String
Dim I1 As Integer
Dim res As Variant

t1 = InputBox("Only Valid Skill Titles Will Be Allowed!", "Skil
Addition Box", "")
With Worksheets("Hidden")
res = Application.Match(t1, .Range(.Range("A2"), _
.Range("A2").End(xlDown)), 0)
End With
If Not IsError(res) Then
ActiveCell = t1
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

Exit Sub



Worksheets("hidden").Visible = False
End If

I1 = MsgBox("Please try again " & Chr(13) & "Skill " & " Entry no
recognised " & "Please Contact Training Dept to Add Skill Title!!")
If ActiveCell = "shift " Then
Exit Sub
Else
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

End If
End Sub

if you would like to have all the code i have for all macro's in th
working program i can either paste them into an e-mail or if you want
can attatch the whole program its 5.5 meg and when it updates o
shutdown (thats the way i have designed it)it takes about 5 mins to d
20 sheets!

E-mail me and let me know.

p.s are you a professional programmer or just have a great intrest i
VB?

Simon

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

Bob,

I dont think i can merge the code as 1 is a workbook event and works o
the same range on each sheet but the worksheet event works on differen
ranges on each sheet, here is the work book code it's out of the tes
book and not the working program but you will get the gist!

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVa
Target As Range)
Dim myrange As Range
Dim isect As Range
If Sh.Name = "Hidden" Then Exit Sub
Set myrange = Sh.Range("E1:G20")


Set isect = Application.Intersect(myrange, Target)
If isect Is Nothing Then

Exit Sub
End If
Sheets("Sheet1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Dim t1 As String
Dim I1 As Integer
Dim res As Variant

t1 = InputBox("Only Valid Skill Titles Will Be Allowed!", "Skil
Addition Box", "")
With Worksheets("Hidden")
res = Application.Match(t1, .Range(.Range("A2"), _
.Range("A2").End(xlDown)), 0)
End With
If Not IsError(res) Then
ActiveCell = t1
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

Exit Sub



Worksheets("hidden").Visible = False
End If

I1 = MsgBox("Please try again " & Chr(13) & "Skill " & " Entry no
recognised " & "Please Contact Training Dept to Add Skill Title!!")
If ActiveCell = "shift " Then
Exit Sub
Else
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

End If
End Sub

if you would like to have all the code i have for all macro's in th
working program i can either paste them into an e-mail or if you want
can attatch the whole program its 5.5 meg and when it updates o
shutdown (thats the way i have designed it)it takes about 5 mins to d
20 sheets!

E-mail me and let me know.

p.s are you a professional programmer or just have a great intrest i
VB?

Simon

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

Simon,

Wing it over (the workbook), and I will take a look (probably not until the
weekend though I am afraid, I have a couple of deadlines).

No, I am not a programer, was once back in the days of Assembler, Pascal,
and Apple IIe,, but haven't been for over 20 years ( before anyone else
says, I know it probably shows).

--

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
...
Bob,

I dont think i can merge the code as 1 is a workbook event and works on
the same range on each sheet but the worksheet event works on different
ranges on each sheet, here is the work book code it's out of the test
book and not the working program but you will get the gist!

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Dim myrange As Range
Dim isect As Range
If Sh.Name = "Hidden" Then Exit Sub
Set myrange = Sh.Range("E1:G20")


Set isect = Application.Intersect(myrange, Target)
If isect Is Nothing Then

Exit Sub
End If
Sheets("Sheet1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Dim t1 As String
Dim I1 As Integer
Dim res As Variant

t1 = InputBox("Only Valid Skill Titles Will Be Allowed!", "Skill
Addition Box", "")
With Worksheets("Hidden")
res = Application.Match(t1, .Range(.Range("A2"), _
Range("A2").End(xlDown)), 0)
End With
If Not IsError(res) Then
ActiveCell = t1
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

Exit Sub



Worksheets("hidden").Visible = False
End If

I1 = MsgBox("Please try again " & Chr(13) & "Skill " & " Entry not
recognised " & "Please Contact Training Dept to Add Skill Title!!")
If ActiveCell = "shift " Then
Exit Sub
Else
Range("A" & ActiveCell.Row).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet3")).Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Name = "Sheet1"
Sheets("Sheet1").Select

End If
End Sub

if you would like to have all the code i have for all macro's in the
working program i can either paste them into an e-mail or if you want i
can attatch the whole program its 5.5 meg and when it updates on
shutdown (thats the way i have designed it)it takes about 5 mins to do
20 sheets!

E-mail me and let me know.

p.s are you a professional programmer or just have a great intrest in
VB?

Simon.


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

I will send it Bob but i need your e-mail address as i cant find yo
under members!

A couple of words....the workbook has an Auto_open which turns of
excel toolbars (all of them!) but dont panic (as if you would!) whe
you shut the program down it does its updating (currently takes 5min
unless you know a way of speeding it up!) it turns the worksheet men
bar back on. because most of the people who will work with this progra
are technophobes i have only allowed them to do the "click" the red
and then the book updates, saves and shutsdown...by the way i a
working with excel xp and saving it down to 97 as nearly all my plac
of work runs this at the moment. Anyway i'm having to implement th
workbook tomorrow and will be online all day but if you get to look a
it later than tomorrow i will cut n paste all the info that will b
entered after tomorrow and let you know how i got on!

You're a generous sole aint ya? when im down that way next i'll brin
you a bottle of the good stuff!

Simo

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

You won't find me under members as I never go on ExcelForum, I read the NGs
direct from the NG server.

My address is

bob . phillips @ tiscali . co . uk

remove the spaces - it's a spam thing.

--

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
...
I will send it Bob but i need your e-mail address as i cant find you
under members!

A couple of words....the workbook has an Auto_open which turns off
excel toolbars (all of them!) but dont panic (as if you would!) when
you shut the program down it does its updating (currently takes 5mins
unless you know a way of speeding it up!) it turns the worksheet menu
bar back on. because most of the people who will work with this program
are technophobes i have only allowed them to do the "click" the red X
and then the book updates, saves and shutsdown...by the way i am
working with excel xp and saving it down to 97 as nearly all my place
of work runs this at the moment. Anyway i'm having to implement the
workbook tomorrow and will be online all day but if you get to look at
it later than tomorrow i will cut n paste all the info that will be
entered after tomorrow and let you know how i got on!

You're a generous sole aint ya? when im down that way next i'll bring
you a bottle of the good stuff!

Simon


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