Hi all, i have this code in around 20 sheets and for each sheet it
changes slightly to allow the activesheet to stay in view....it is
supposed to select all sheets allow a selected text from a combo box to
be entered in the activecell across all sheets and then deselect all
sheets....it used to work but now it skips the range on which it should
select and goes straight to the Userform.show.....however if i put
msgboxes in prior to the userform.show these boxes appear so its not
skipping it entirely. Can anyone help? The only thing i have added to
this is in the This workbook module and its code for making a monthly
back up in the before close event

Here is the code for the Worksheet selection change on each sheet along
with the userform code....

Dim sh As Object
Dim myrange As Range
Dim ComboBox1
Dim I1 As Integer
Dim res As Variant
Dim arysheets
On Error Resume Next

Application.EnableEvents = True
With arysheets

Set myrange = Range("E3:H641")
If Not Intersect(myrange, Target) Is Nothing Then

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
arysheets = Array("Bulk & H&I", "Alpha Process", "Alpha
Packing", _
"Corn Process", "33 Bldg Packing", "Ctd Corn
Packing", _
"2 & 3 Coating", "Crispix", "Feed&Lab",
"Flavour", _
"Jet Zones", "Quality & Others", "MPD",
"Plant Awareness", _
"Rice Cooking", "Vehicle Drivers (plant)",
"VIP", _
"15-21 & 22", "4&5 Coating", "Tank Floor 15 &
33 Bldg", "FSP's")

Sheets(arysheets).Select


For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
Next
End If

If ActiveCell.Column = 5 And ActiveCell.Column <= 8 And
ActiveCell.Row = 3 And ActiveCell.Row <= 641 Then
UserForm1.Show
If Not IsError(res) Then

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Worksheets("hidden").Visible = False
Me.Select

End If

If ActiveCell < "shift " Then
Range("A" & ActiveCell.Row).Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

End If
End If

End With


End Sub

Private Sub ComboBox1_Change()
On Error Resume Next
Dim msgentry As String
ActiveCell.Select
ActiveCell.Value = ComboBox1.Value
Unload UserForm1
If ActiveCell.Text = "Ref:E-mail" Then
msgentry = "Send E-mail to Training to Have Skill Added!" &
Chr(13) & "Lotus notes will now start up!" & Chr(13) & "Send E-mail,
then close Lotus notes as normal"
MsgBox msgentry, vbOKOnly, "E-mail Prompt For New Skill"
Shell "C:\notes\notes.exe"

End If
'UserForm1.Hide
Range("A" & ActiveCell.Row).Select
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=265034