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

posted this earlier really need help!

Simo

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

Sorry to post this again!

Really need help on this, i have also pasted this in to the Thi
workbook module to try to get it to work on all sheets.....of cours
with out it working in the first place i cant test it!

Simo

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

I'm not the greatest at VBA by any means, but I see
Dim dropdown4 As String
and
'If dropdown4.Value = "REF:E-Mail" Then

If dropdown4 is a String, it doesn't have a Value property. If dropdown4 is
a control on a form and you want to take the value of it, then get rid of
the String declaration. It can't be both. If you want to set the value to
a string, well, I guess you could, but I'd just use the value property.

My 3/4-cents worth.
Ed

"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/

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/