ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Command Button (https://www.excelbanter.com/excel-programming/431546-command-button.html)

Kanmi
Command Button
 
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
Command Button
 
Hi,

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

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

Kanmi
Command Button
 
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

Patrick Molloy
Command Button
 
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" wrote in message
...
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


Kanmi
Command Button
 
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" wrote in message
...
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



Patrick Molloy
Command Button
 
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" wrote in message
...
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" wrote in message
...
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

Kanmi
Command Button
 
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" wrote in message
...
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" wrote in message
...
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

Patrick Molloy
Command Button
 
please show your code as it is now

"Kanmi" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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


All times are GMT +1. The time now is 10:17 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com