Please can anybody help me check why this give Compile Error higlighting "
Private Sub CommandButton1_Click()" in yellow. the first line of the code.
please advice and appreciate your time. 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 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 Select


End Sub

You are missing and End Sub after the End Select

"Kanmi" wrote:

Please can anybody help me check why this give Compile Error higlighting "
Private Sub CommandButton1_Click()" in yellow. the first line of the code.
please advice and appreciate your time. 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 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 Select


End Sub

read my response in the original thread. Please stay on the original
thread - it saves time wasting for others who may not realise we have been
down this route already

In the code below you have inserted a procedure before the end sub

so please change FROM this

Set LookupSht = Sheets("Lookup2")

End Select

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


End Sub

TO THIS
Set LookupSht = Sheets("Lookup2")

End Select

BuildDetailReport ' add your 3 parameters

End Sub

Sub BuildDetailReport(TrainingSht, PersonnelSht, LookupSht)


End Sub


In summary
your original code was missing the END SUB for the first procedure, which we
indicated that you should add. In doing so, you omitted the call to the
procedure to build the report. We suggested you do that

in the code below you MUST HAVE an End Sub AFTER the END SELECT and BEFORE
the definition for teh sub BuildDetailReport
Also , you initially had a call to this as I tried to indicate






"Mike" wrote in message
...
You are missing and End Sub after the End Select

"Kanmi" wrote:

Please can anybody help me check why this give Compile Error higlighting
"
Private Sub CommandButton1_Click()" in yellow. the first line of the
code.
please advice and appreciate your time. 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 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 Select


End Sub