VBA Code problems!

[Simon Lloyd]

Hi all!,

I have a workbook (6meg) which has some auto open/close code also
workbook selection change i have a coulpe of glitches in my code, in
one part im trying to get a dialog box pop up when a certain cell in a
certain range is clicked and which ever choice is made from the dialog
box it is to be entered in the same cell on al sheets, thats problem 1
next problem is my auto close i have tried to paste my updating VBA in
to the autoclose and now if im not getting a block end if without block
if its some other similar problem.......i've been hitting a brick wall
on this for ages i really need help....I can mail you the workbook or
send a word document tat conatins all the code i have!

Can anyone help????????????????


Simon

 

[Bob Phillips]

Post the code to the newsgroup in text format with explanation.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Simon Lloyd]

Hi all,
Here's all the code in my workbook (Bob of cousre most of this will b
familiar to you!) i have made some comments
in the text.

The comments are denoted by '((.....))

Simon

Code is attatched in txt forma

Attachment filename: code.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=55413

 

[Bob Phillips]

'((This now will not work after worksheet selection change is added to
module as below))
to agree with everything else but it doesn't work at all!))

That's because you are trying tgo add 2 procedures of the same name. You
either need to incorporate one in the other, or if one only applies to one
particular worksheet, move it into thatworksheet module and change to event

Private Sub WorkSheet_SelectionChange( ByVal Target As Range)

Fix this and see if the dialog works okay then

This line won't work

Worksheets("Hidden"),("dialog1").Visible = False

try

Worksheets(Array("Hidden", "dialog1")).Visible = False

or

Sheets("Hidden"),("dialog1").Visible = False

if the dialog1 is a dialog sheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Simon Lloyd]

Thanks for the reply bob, as you can see i am yet modifying that bleedi
program ( i got the spot change to work in workbook selection with th
advent of numbercell in the code u have) well, i have tried th
worksheet selection change with the code for the dialog sheet i hav
created, it compiles ok but does nothing on selection of a cell in th
range!

Heres the code!

Simon

Private 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

If sh.Name = ("Hidden") Or ("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
t1 = DialogSheets("Ref:E-mail") 'InputBox("Only Valid Skil
Titles Will Be Allowed!", "Skill Addition Box", "")
End If
If dropdown4.Value = "Ref:E-mail" Then
MsgBox "send email to training"


With Worksheets("Hidden")
res = Application.Match(t1, .Range(.Range("A2"), _
.Range("A2").End(xlDown)), 0)
End With
End If
If Not IsError(res) Then
ActiveCell
DialogSheets.Application.Dialogs("dropdown4").Show
Range("A" & ActiveCell.Row).Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Alpha Packing").Select
Exit Sub
Worksheets("hidden").Visible = False
DialogSheets("Dialog1").Visible = False
End If

I1 = MsgBox("Please try again " & vbCrLf & _
"Skill " & " Entry not recognised " & _
"Please Contact Training Dept to Add Skil
Title!!")
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]

All,

I have tried this code in a module of its own and still it doe
nothing.....i have even put a msgbox in to return a message when i
gets so far but its not even doing that.


Im still at a loss with this!

Simo