Command Button

[Kanmi]

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

 

[Mike H]

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]

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

 

[Patrick Molloy]

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

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

 

[Kanmi]

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

 

[Patrick Molloy]

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]

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

 

[Patrick Molloy]

please show your code as it is now

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