Please can anybody help me check why this script is not perform the right
task. whenever i run and i select OFC Then it run DOWNSTRM information. will
appreciate your advice. thanks


Private Sub CommandButton1_Click()
ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
If ask = 7 Then Exit Sub

Select Case Range("D21")
Case "DOWNSTRM":
Set TrainingSht = Sheets("Training_Progress_By_Employee")
Set PersonnelSht = Sheets("Personnel Roster")
Set PersonnelSht = Sheets("Lookup")

Case "OFC":
Set TrainingSht = Sheets("Training_Progress_By_Employee2")
Set PersonnelSht = Sheets("Personnel Roster2")
Set PersonnelSht = Sheets("Lookup2")

End Select



BuildDetailReport
MsgBox "Done", , BoxTitle
End Sub

Hi,

Looking at your code it would work but I think you have an error here:-

Set PersonnelSht = Sheets("Personnel Roster2")
Set PersonnelSht = Sheets("Lookup2")

You set PersonnelSht then immediatly set it to another worksheet, do you
really mean to do that?

The other thing I would note is that it's going to call 'BuildDetailReport'
no matter what happens in the select case statement so PersonnelSht etc may
not be set.


I have no idea what the Sub BuildDetailReport does so can't comment on
whether it will work.

Mike

"Kanmi" wrote:

> Please can anybody help me check why this script is not perform the right
> task. whenever i run and i select OFC Then it run DOWNSTRM information. will
> appreciate your advice. thanks
>
>
> Private Sub CommandButton1_Click()
> ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
> If ask = 7 Then Exit Sub
>
> Select Case Range("D21")
> Case "DOWNSTRM":
> Set TrainingSht = Sheets("Training_Progress_By_Employee")
> Set PersonnelSht = Sheets("Personnel Roster")
> Set PersonnelSht = Sheets("Lookup")
>
> Case "OFC":
> Set TrainingSht = Sheets("Training_Progress_By_Employee2")
> Set PersonnelSht = Sheets("Personnel Roster2")
> Set PersonnelSht = Sheets("Lookup2")
>
> End Select
>
>
>
> BuildDetailReport
> MsgBox "Done", , BoxTitle
> End Sub

New Error when i change it "Compile Error Expected End Sub" Below is the new
code:

Private Sub CommandButton1_Click()
ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
If ask = 7 Then Exit Sub

Select Case Range("D21")
Case "DOWNSTRM":
Set TrainingSht = Sheets("Training_Progress_By_Employee")
Set PersonnelSht = Sheets("Personnel Roster")
Set LookupSht = Sheets("Lookup")

Case "OFC":
Set TrainingSht = Sheets("Training_Progress_By_Employee2")
Set PersonnelSht = Sheets("Personnel Roster2")
Set LookupSht = Sheets("Lookup2")

End Select
Sub BuildDetailReport(TrainingSht, PersonnelSht, LookupSht)
MsgBox "Done", , BoxTitle
End Sub




"Kanmi" wrote:

> Please can anybody help me check why this script is not perform the right
> task. whenever i run and i select OFC Then it run DOWNSTRM information. will
> appreciate your advice. thanks
>
>
> Private Sub CommandButton1_Click()
> ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
> If ask = 7 Then Exit Sub
>
> Select Case Range("D21")
> Case "DOWNSTRM":
> Set TrainingSht = Sheets("Training_Progress_By_Employee")
> Set PersonnelSht = Sheets("Personnel Roster")
> Set PersonnelSht = Sheets("Lookup")
>
> Case "OFC":
> Set TrainingSht = Sheets("Training_Progress_By_Employee2")
> Set PersonnelSht = Sheets("Personnel Roster2")
> Set PersonnelSht = Sheets("Lookup2")
>
> End Select
>
>
>
> BuildDetailReport
> MsgBox "Done", , BoxTitle
> End Sub

after " end select" you need to have an "END SUB" since the next line
starts a new procedure -- see below where I marked it...

"Kanmi" <(E-Mail Removed)> wrote in message
news:F9E45440-1DE2-4737-B45B-(E-Mail Removed)...
> New Error when i change it "Compile Error Expected End Sub" Below is the
> new
> code:
>
> Private Sub CommandButton1_Click()
> ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
> If ask = 7 Then Exit Sub
>
> Select Case Range("D21")
> Case "DOWNSTRM":
> Set TrainingSht = Sheets("Training_Progress_By_Employee")
> Set PersonnelSht = Sheets("Personnel Roster")
> Set LookupSht = Sheets("Lookup")
>
> Case "OFC":
> Set TrainingSht = Sheets("Training_Progress_By_Employee2")
> Set PersonnelSht = Sheets("Personnel Roster2")
> Set LookupSht = Sheets("Lookup2")
>
> End Select

END SUB


> Sub BuildDetailReport(TrainingSht, PersonnelSht, LookupSht)
> MsgBox "Done", , BoxTitle
> End Sub
>
>
>
>
> "Kanmi" wrote:
>
>> Please can anybody help me check why this script is not perform the right
>> task. whenever i run and i select OFC Then it run DOWNSTRM information.
>> will
>> appreciate your advice. thanks
>>
>>
>> Private Sub CommandButton1_Click()
>> ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
>> If ask = 7 Then Exit Sub
>>
>> Select Case Range("D21")
>> Case "DOWNSTRM":
>> Set TrainingSht = Sheets("Training_Progress_By_Employee")
>> Set PersonnelSht = Sheets("Personnel Roster")
>> Set PersonnelSht = Sheets("Lookup")
>>
>> Case "OFC":
>> Set TrainingSht = Sheets("Training_Progress_By_Employee2")
>> Set PersonnelSht = Sheets("Personnel Roster2")
>> Set PersonnelSht = Sheets("Lookup2")
>>
>> End Select
>>
>>
>>
>> BuildDetailReport
>> MsgBox "Done", , BoxTitle
>> End Sub

Thanks if i put End Sub there then this end the procedure. Not working

"Patrick Molloy" wrote:

> after " end select" you need to have an "END SUB" since the next line
> starts a new procedure -- see below where I marked it...
>
> "Kanmi" <(E-Mail Removed)> wrote in message
> news:F9E45440-1DE2-4737-B45B-(E-Mail Removed)...
> > New Error when i change it "Compile Error Expected End Sub" Below is the
> > new
> > code:
> >
> > Private Sub CommandButton1_Click()
> > ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
> > If ask = 7 Then Exit Sub
> >
> > Select Case Range("D21")
> > Case "DOWNSTRM":
> > Set TrainingSht = Sheets("Training_Progress_By_Employee")
> > Set PersonnelSht = Sheets("Personnel Roster")
> > Set LookupSht = Sheets("Lookup")
> >
> > Case "OFC":
> > Set TrainingSht = Sheets("Training_Progress_By_Employee2")
> > Set PersonnelSht = Sheets("Personnel Roster2")
> > Set LookupSht = Sheets("Lookup2")
> >
> > End Select
>
> END SUB
>
>
> > Sub BuildDetailReport(TrainingSht, PersonnelSht, LookupSht)
> > MsgBox "Done", , BoxTitle
> > End Sub
> >
> >
> >
> >
> > "Kanmi" wrote:
> >
> >> Please can anybody help me check why this script is not perform the right
> >> task. whenever i run and i select OFC Then it run DOWNSTRM information.
> >> will
> >> appreciate your advice. thanks
> >>
> >>
> >> Private Sub CommandButton1_Click()
> >> ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
> >> If ask = 7 Then Exit Sub
> >>
> >> Select Case Range("D21")
> >> Case "DOWNSTRM":
> >> Set TrainingSht = Sheets("Training_Progress_By_Employee")
> >> Set PersonnelSht = Sheets("Personnel Roster")
> >> Set PersonnelSht = Sheets("Lookup")
> >>
> >> Case "OFC":
> >> Set TrainingSht = Sheets("Training_Progress_By_Employee2")
> >> Set PersonnelSht = Sheets("Personnel Roster2")
> >> Set PersonnelSht = Sheets("Lookup2")
> >>
> >> End Select
> >>
> >>
> >>
> >> BuildDetailReport
> >> MsgBox "Done", , BoxTitle
> >> End Sub
>
>

you have changed the code I see.

your original had this

BuildDetailReport
MsgBox "Done", , BoxTitle
End Sub

so put the two lines back in before the end sub

"Kanmi" <(E-Mail Removed)> wrote in message
news:42E77F27-EB9D-4498-90A0-(E-Mail Removed)...
> Thanks if i put End Sub there then this end the procedure. Not working
>
> "Patrick Molloy" wrote:
>
>> after " end select" you need to have an "END SUB" since the next line
>> starts a new procedure -- see below where I marked it...
>>
>> "Kanmi" <(E-Mail Removed)> wrote in message
>> news:F9E45440-1DE2-4737-B45B-(E-Mail Removed)...
>> > New Error when i change it "Compile Error Expected End Sub" Below is
>> > the
>> > new
>> > code:
>> >
>> > Private Sub CommandButton1_Click()
>> > ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
>> > If ask = 7 Then Exit Sub
>> >
>> > Select Case Range("D21")
>> > Case "DOWNSTRM":
>> > Set TrainingSht = Sheets("Training_Progress_By_Employee")
>> > Set PersonnelSht = Sheets("Personnel Roster")
>> > Set LookupSht = Sheets("Lookup")
>> >
>> > Case "OFC":
>> > Set TrainingSht = Sheets("Training_Progress_By_Employee2")
>> > Set PersonnelSht = Sheets("Personnel Roster2")
>> > Set LookupSht = Sheets("Lookup2")
>> >
>> > End Select
>>
>> END SUB
>>
>>
>> > Sub BuildDetailReport(TrainingSht, PersonnelSht, LookupSht)
>> > MsgBox "Done", , BoxTitle
>> > End Sub
>> >
>> >
>> >
>> >
>> > "Kanmi" wrote:
>> >
>> >> Please can anybody help me check why this script is not perform the
>> >> right
>> >> task. whenever i run and i select OFC Then it run DOWNSTRM
>> >> information.
>> >> will
>> >> appreciate your advice. thanks
>> >>
>> >>
>> >> Private Sub CommandButton1_Click()
>> >> ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
>> >> If ask = 7 Then Exit Sub
>> >>
>> >> Select Case Range("D21")
>> >> Case "DOWNSTRM":
>> >> Set TrainingSht = Sheets("Training_Progress_By_Employee")
>> >> Set PersonnelSht = Sheets("Personnel Roster")
>> >> Set PersonnelSht = Sheets("Lookup")
>> >>
>> >> Case "OFC":
>> >> Set TrainingSht = Sheets("Training_Progress_By_Employee2")
>> >> Set PersonnelSht = Sheets("Personnel Roster2")
>> >> Set PersonnelSht = Sheets("Lookup2")
>> >>
>> >> End Select
>> >>
>> >>
>> >>
>> >> BuildDetailReport
>> >> MsgBox "Done", , BoxTitle
>> >> End Sub
>>
>>

Same thing. Not working. It higlighting "Private Sub CommandButton1_Click()
" at the top first line in yellow colour.

"Patrick Molloy" wrote:

> you have changed the code I see.
>
> your original had this
>
> BuildDetailReport
> MsgBox "Done", , BoxTitle
> End Sub
>
> so put the two lines back in before the end sub
>
> "Kanmi" <(E-Mail Removed)> wrote in message
> news:42E77F27-EB9D-4498-90A0-(E-Mail Removed)...
> > Thanks if i put End Sub there then this end the procedure. Not working
> >
> > "Patrick Molloy" wrote:
> >
> >> after " end select" you need to have an "END SUB" since the next line
> >> starts a new procedure -- see below where I marked it...
> >>
> >> "Kanmi" <(E-Mail Removed)> wrote in message
> >> news:F9E45440-1DE2-4737-B45B-(E-Mail Removed)...
> >> > New Error when i change it "Compile Error Expected End Sub" Below is
> >> > the
> >> > new
> >> > code:
> >> >
> >> > Private Sub CommandButton1_Click()
> >> > ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
> >> > If ask = 7 Then Exit Sub
> >> >
> >> > Select Case Range("D21")
> >> > Case "DOWNSTRM":
> >> > Set TrainingSht = Sheets("Training_Progress_By_Employee")
> >> > Set PersonnelSht = Sheets("Personnel Roster")
> >> > Set LookupSht = Sheets("Lookup")
> >> >
> >> > Case "OFC":
> >> > Set TrainingSht = Sheets("Training_Progress_By_Employee2")
> >> > Set PersonnelSht = Sheets("Personnel Roster2")
> >> > Set LookupSht = Sheets("Lookup2")
> >> >
> >> > End Select
> >>
> >> END SUB
> >>
> >>
> >> > Sub BuildDetailReport(TrainingSht, PersonnelSht, LookupSht)
> >> > MsgBox "Done", , BoxTitle
> >> > End Sub
> >> >
> >> >
> >> >
> >> >
> >> > "Kanmi" wrote:
> >> >
> >> >> Please can anybody help me check why this script is not perform the
> >> >> right
> >> >> task. whenever i run and i select OFC Then it run DOWNSTRM
> >> >> information.
> >> >> will
> >> >> appreciate your advice. thanks
> >> >>
> >> >>
> >> >> Private Sub CommandButton1_Click()
> >> >> ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
> >> >> If ask = 7 Then Exit Sub
> >> >>
> >> >> Select Case Range("D21")
> >> >> Case "DOWNSTRM":
> >> >> Set TrainingSht = Sheets("Training_Progress_By_Employee")
> >> >> Set PersonnelSht = Sheets("Personnel Roster")
> >> >> Set PersonnelSht = Sheets("Lookup")
> >> >>
> >> >> Case "OFC":
> >> >> Set TrainingSht = Sheets("Training_Progress_By_Employee2")
> >> >> Set PersonnelSht = Sheets("Personnel Roster2")
> >> >> Set PersonnelSht = Sheets("Lookup2")
> >> >>
> >> >> End Select
> >> >>
> >> >>
> >> >>
> >> >> BuildDetailReport
> >> >> MsgBox "Done", , BoxTitle
> >> >> End Sub
> >>
> >>
>

please show your code as it is now

"Kanmi" <(E-Mail Removed)> wrote in message
news:31052FBF-09F2-4845-B2B1-(E-Mail Removed)...
> Same thing. Not working. It higlighting "Private Sub
> CommandButton1_Click()
> " at the top first line in yellow colour.
>
> "Patrick Molloy" wrote:
>
>> you have changed the code I see.
>>
>> your original had this
>>
>> BuildDetailReport
>> MsgBox "Done", , BoxTitle
>> End Sub
>>
>> so put the two lines back in before the end sub
>>
>> "Kanmi" <(E-Mail Removed)> wrote in message
>> news:42E77F27-EB9D-4498-90A0-(E-Mail Removed)...
>> > Thanks if i put End Sub there then this end the procedure. Not working
>> >
>> > "Patrick Molloy" wrote:
>> >
>> >> after " end select" you need to have an "END SUB" since the next line
>> >> starts a new procedure -- see below where I marked it...
>> >>
>> >> "Kanmi" <(E-Mail Removed)> wrote in message
>> >> news:F9E45440-1DE2-4737-B45B-(E-Mail Removed)...
>> >> > New Error when i change it "Compile Error Expected End Sub" Below is
>> >> > the
>> >> > new
>> >> > code:
>> >> >
>> >> > Private Sub CommandButton1_Click()
>> >> > ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
>> >> > If ask = 7 Then Exit Sub
>> >> >
>> >> > Select Case Range("D21")
>> >> > Case "DOWNSTRM":
>> >> > Set TrainingSht = Sheets("Training_Progress_By_Employee")
>> >> > Set PersonnelSht = Sheets("Personnel Roster")
>> >> > Set LookupSht = Sheets("Lookup")
>> >> >
>> >> > Case "OFC":
>> >> > Set TrainingSht = Sheets("Training_Progress_By_Employee2")
>> >> > Set PersonnelSht = Sheets("Personnel Roster2")
>> >> > Set LookupSht = Sheets("Lookup2")
>> >> >
>> >> > End Select
>> >>
>> >> END SUB
>> >>
>> >>
>> >> > Sub BuildDetailReport(TrainingSht, PersonnelSht, LookupSht)
>> >> > MsgBox "Done", , BoxTitle
>> >> > End Sub
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Kanmi" wrote:
>> >> >
>> >> >> Please can anybody help me check why this script is not perform the
>> >> >> right
>> >> >> task. whenever i run and i select OFC Then it run DOWNSTRM
>> >> >> information.
>> >> >> will
>> >> >> appreciate your advice. thanks
>> >> >>
>> >> >>
>> >> >> Private Sub CommandButton1_Click()
>> >> >> ask = MsgBox("Build safety report?", vbYesNo, BoxTitle)
>> >> >> If ask = 7 Then Exit Sub
>> >> >>
>> >> >> Select Case Range("D21")
>> >> >> Case "DOWNSTRM":
>> >> >> Set TrainingSht = Sheets("Training_Progress_By_Employee")
>> >> >> Set PersonnelSht = Sheets("Personnel Roster")
>> >> >> Set PersonnelSht = Sheets("Lookup")
>> >> >>
>> >> >> Case "OFC":
>> >> >> Set TrainingSht =
>> >> >> Sheets("Training_Progress_By_Employee2")
>> >> >> Set PersonnelSht = Sheets("Personnel Roster2")
>> >> >> Set PersonnelSht = Sheets("Lookup2")
>> >> >>
>> >> >> End Select
>> >> >>
>> >> >>
>> >> >>
>> >> >> BuildDetailReport
>> >> >> MsgBox "Done", , BoxTitle
>> >> >> End Sub
>> >>
>> >>
>>