Good morning from COB Speicher, Iraq. I have a workbook that I use to
track my Soldiers leave requests to determine how many can be gone at
any given time and still allow us to complete our missions. It has
four worksheets: leave request, calendar, mission capability, and
personnel. How can I create a dialog box that pops up when an Excel
workbook is opened? It should ask if I want to add a leave request
and if answer is yes, open a data form based on the leave request
sheet. If answer is no, it should ask "Which worksheet do you wish to
view?" and open the sheet that is input. Thanks for the help.

Private Sub Workbook_Open()
Dim ans As String

If MsgBox("Add a leave request", vbYesNo, "Soldiers Leave") = vbYes Then

LeaveForm.Show
Else

ans = InputBox("Show which sheet?", "Soldiers Leve")
On Error Resume Next
Worksheets(ans).Activate
End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


BTW, what does COB stand for?

--
__________________________________
HTH

Bob

"Anthony" wrote in message
...
Good morning from COB Speicher, Iraq. I have a workbook that I use to
track my Soldiers leave requests to determine how many can be gone at
any given time and still allow us to complete our missions. It has
four worksheets: leave request, calendar, mission capability, and
personnel. How can I create a dialog box that pops up when an Excel
workbook is opened? It should ask if I want to add a leave request
and if answer is yes, open a data form based on the leave request
sheet. If answer is no, it should ask "Which worksheet do you wish to
view?" and open the sheet that is input. Thanks for the help.

On Jan 4, 1:49*am, "Bob Phillips" wrote:
Private Sub Workbook_Open()
Dim ans As String

* * If MsgBox("Add a leave request", vbYesNo, "Soldiers Leave") = vbYes Then

* * * * LeaveForm.Show
* * Else

* * * * ans = InputBox("Show which sheet?", "Soldiers Leve")
* * * * On Error Resume Next
* * * * Worksheets(ans).Activate
* * End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

BTW, what does COB stand for?

--
__________________________________
HTH

Bob

"Anthony" wrote in message

...



Good morning from COB Speicher, Iraq. *I have a workbook that I use to
track my Soldiers leave requests to determine how many can be gone at
any given time and still allow us to complete our missions. *It has
four worksheets: leave request, calendar, mission capability, and
personnel. *How can I create a dialog box that pops up when an Excel
workbook is opened? *It should ask if I want to add a leave request
and if answer is yes, open a data form based on the leave request
sheet. *If answer is no, it should ask "Which worksheet do you wish to
view?" and open the sheet that is input. *Thanks for the help.- Hide quoted text -

- Show quoted text -

Thanks Bob for the quick response. I will add the code later today
and repost the results. COB stands for contigency operating base.

On Jan 4, 1:49*am, "Bob Phillips" wrote:
Private Sub Workbook_Open()
Dim ans As String

* * If MsgBox("Add a leave request", vbYesNo, "Soldiers Leave") = vbYes Then

* * * * LeaveForm.Show
* * Else

* * * * ans = InputBox("Show which sheet?", "Soldiers Leve")
* * * * On Error Resume Next
* * * * Worksheets(ans).Activate
* * End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

BTW, what does COB stand for?

--
__________________________________
HTH

Bob

"Anthony" wrote in message

...



Good morning from COB Speicher, Iraq. *I have a workbook that I use to
track my Soldiers leave requests to determine how many can be gone at
any given time and still allow us to complete our missions. *It has
four worksheets: leave request, calendar, mission capability, and
personnel. *How can I create a dialog box that pops up when an Excel
workbook is opened? *It should ask if I want to add a leave request
and if answer is yes, open a data form based on the leave request
sheet. *If answer is no, it should ask "Which worksheet do you wish to
view?" and open the sheet that is input. *Thanks for the help.- Hide quoted text -

- Show quoted text -

I pasted the code in the VB editor and it gave me the following error
message:
Compile Error: Expected function or Variable
Also, the text "LeaveForm" from the code you provided was highlighted
in blue.

Anthony,

You need to replace LeaveForm in the code with the real name of your form.

--
__________________________________
HTH

Bob

"Anthony" wrote in message
...
On Jan 4, 1:49 am, "Bob Phillips" wrote:
Private Sub Workbook_Open()
Dim ans As String

If MsgBox("Add a leave request", vbYesNo, "Soldiers Leave") = vbYes Then

LeaveForm.Show
Else

ans = InputBox("Show which sheet?", "Soldiers Leve")
On Error Resume Next
Worksheets(ans).Activate
End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

BTW, what does COB stand for?

--
__________________________________
HTH

Bob

"Anthony" wrote in message

...



Good morning from COB Speicher, Iraq. I have a workbook that I use to
track my Soldiers leave requests to determine how many can be gone at
any given time and still allow us to complete our missions. It has
four worksheets: leave request, calendar, mission capability, and
personnel. How can I create a dialog box that pops up when an Excel
workbook is opened? It should ask if I want to add a leave request
and if answer is yes, open a data form based on the leave request
sheet. If answer is no, it should ask "Which worksheet do you wish to
view?" and open the sheet that is input. Thanks for the help.- Hide
quoted text -

- Show quoted text -

I pasted the code in the VB editor and it gave me the following error
message:
Compile Error: Expected function or Variable
Also, the text "LeaveForm" from the code you provided was highlighted
in blue.

On Jan 5, 1:22*am, "Bob Phillips" wrote:
Anthony,

You need to replace LeaveForm in the code with the real name of your form..

--
__________________________________
HTH

Bob

"Anthony" wrote in message

...
On Jan 4, 1:49 am, "Bob Phillips" wrote:





Private Sub Workbook_Open()
Dim ans As String

If MsgBox("Add a leave request", vbYesNo, "Soldiers Leave") = vbYes Then

LeaveForm.Show
Else

ans = InputBox("Show which sheet?", "Soldiers Leve")
On Error Resume Next
Worksheets(ans).Activate
End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

BTW, what does COB stand for?

--
__________________________________
HTH

Bob

"Anthony" wrote in message

....

Good morning from COB Speicher, Iraq. I have a workbook that I use to
track my Soldiers leave requests to determine how many can be gone at
any given time and still allow us to complete our missions. It has
four worksheets: leave request, calendar, mission capability, and
personnel. How can I create a dialog box that pops up when an Excel
workbook is opened? It should ask if I want to add a leave request
and if answer is yes, open a data form based on the leave request
sheet. If answer is no, it should ask "Which worksheet do you wish to
view?" and open the sheet that is input. Thanks for the help.- Hide
quoted text -

- Show quoted text -

I pasted the code in the VB editor and it gave me the following error
message:
Compile Error: Expected function or Variable
Also, the text "LeaveForm" from the code you provided was highlighted
in blue.- Hide quoted text -

- Show quoted text -

I have done this and I get the same error message. Is there something
that I need to do with regards to the data form. If I run the macro
without the VB code you provided, it opens the data form without any
issues. I created the data form using the record macro function in
Excel 2007 if that makes a difference; it is named "Leave". Sorry for
all the questions. Thanks again.

I think Bob assumes you created a UserForm named Leave

Did you do that?

I don't think you can do that by recording a macro.


Gord Dibben MS Excel MVP

On Mon, 5 Jan 2009 10:55:13 -0800 (PST), Anthony
wrote:

I have done this and I get the same error message. Is there something
that I need to do with regards to the data form. If I run the macro
without the VB code you provided, it opens the data form without any
issues. I created the data form using the record macro function in
Excel 2007 if that makes a difference; it is named "Leave". Sorry for
all the questions. Thanks again.