Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Command Button
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 76
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 76
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 76
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default 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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 76
Default 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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change from a Command Button to a Form Button Ben in CA[_2_] Excel Programming 4 December 11th 08 10:05 PM
Userform Command Button not available until another command buttonhas been used [email protected] Excel Programming 4 September 4th 08 04:35 PM
Deselect Command Button by Selecting another Command Button gmcnaugh[_2_] Excel Programming 3 September 2nd 08 05:59 PM
Wanting to Create A Command Button Command bumper338 Excel Programming 3 May 7th 07 06:53 PM
VB's Command Button vs Form's Command Button Ronald Dodge Excel Programming 3 May 24th 06 02:23 PM


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement