I hae created a dialog box which has a dropdown list on it, i am tryin
to get the worksheet selection change to bring up the dialog box when
cell in a range is selcted, here's my code so far but it has troubl
with "dropdown4" and says its variable undefined!

Can you help?

Simon

Heres the code!

Public Sub Worksheet_SheetSelectionChange(ByVal sh As Object, ByVa
Target As Range)

Dim myrange As Range
'
Dim I1 As Integer
Dim res As Variant
Dim arySheets
Dim dropdown4 As String

If sh.Name = ("Hidden") And sh.Name = ("dialog1") Then Exit Sub
Set myrange = sh.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

'If dropdown4.Value = "REF:E-Mail" Then
MsgBox "Send E-mail to training now!"
With sh.Name("Hidden") And sh.Name("Dialog1")
res = Application.Match(dropdown4, .Range(.Range("A2"), _
.Range("A2").End(xlDown)), 0)
End With
If Not IsError(res) Then
ActiveCell = DialogBox.dropdown4_change.Value
Range("A" & ActiveCell.Row).Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Alpha Packing").Select
Exit Sub
sh.Name ("hidden") And sh.Name("Dialog1").Visible = False
End If



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

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