Simon,

Not a direct answer to your question, but this line:

If sh.Name = ("Hidden") And sh.Name = ("dialog1") Then Exit Sub

will never be true since sh.name will only be equal to one of them. Maybe
you mean Or?

hth,

Doug Glancy

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

Can you help?

Simon

Heres the code!

Public Sub Worksheet_SheetSelectionChange(ByVal sh As Object, ByVal
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 Corn
Packing", _
"2 & 3 Coating", "Crispix", "Feed",
"Flavour", _
"Jet Zones", "Manpower Tasks", "MPD", "Plant
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 Sub


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