Dialog box help!

[Simon Lloyd]

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

 

[Simon Lloyd]

posted this earlier really need help!

Simo

 

[Simon Lloyd]

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

 

[Ed]

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

 

[Doug Glancy]

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