Combo box wont change selection?

[Simon Lloyd]

I have created a userform which i call on in the code below, when
select from the drop down it then enters the selection in th
activecell on all sheets and the selects the 1st sheet, my problem i
this.....if i choose the 1st or last sheet and select a cell there i
no problem and i can make an entry anywhere but if i choose 1 of th
others and select a cell as soon as the combo box pops up it select
the cell that was previously used in a selection on the first or las
sheet.....is there anyway around this?....all sheets contain Workshee
selection change code (which is the same on every sheet)....heres th
code!

Simon

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim sh As Object
Dim myrange As Range
Dim ComboBox1
Dim I1 As Integer
Dim res As Variant
Dim arySheets
On Error Resume Next

'If sh.Range = ("Hidden") Then
'Exit Sub
'End If

With arySheets
Set myrange = Range("E3:H641")
If Not Intersect(myrange, Target) Is Nothing Then
Sheets("Alpha Packing").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
arySheets = Array("Alpha Packing", "Alpha Process", "Bulk
H&I", _
"Corn Process", "33 Bldg Packing", "Ctd Cor
Packing", _
"2 & 3 Coating", "Crispix", "Feed"
"Flavour", _
"Jet Zones", "Manpower Tasks", "MPD", "Plan
Awareness", _
"Rice Cooking", "Vehicle Drivers (plant)"
"VIP", _
"15-21 & 22", "4&5 Coating", "Tank Floor 15
33 Bldg")
Sheets(arySheets).Select
Sheets("Alpha Packing").Activate
End If
UserForm1.Show


If ActiveCell.Text = "Ref:E-mail" Then
MsgBox "send email to training"
'Shell ("cmd /k C:\notes\notes.exe")


'Range("A" & ActiveCell.Row).Select
End If
If Not IsError(res) Then


ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Alpha Packing").Select
Exit Sub
Worksheets("hidden").Visible = False

End If

I1 = MsgBox("Please try again " & vbCrLf & _
"Skill " & " Entry not recognised " & _
"Please Contact Training Dept to Add Skil
Title!!")
If ActiveCell <> "shift " Then
Range("A" & ActiveCell.Row).Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Alpha Packing").Select
End If

End With
End Su

 

[Simon Lloyd]

Sorry all i forgot to add the userform code......so here it is!

Simon

Private Sub ComboBox1_Change()
On Error Resume Next
ActiveCell.Select
ActiveCell.Value = ComboBox1.Value

UserForm1.Hide
End Su

 

[Simon Lloyd]

I posted this a couple f days ago any ideas??

simo

 

[Simon Lloyd]

Sorry about re-posting this but it is causing quite a headache a
information newly entered is being overwritten because its acting o
the same cell every time!

Simo