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.

On Jan 6, 3:06*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.- Hide quoted text -

- Show quoted text -

Oh, I have not done a Userform before. I will have to find a tutorial
that teaches me how to create one. Thanks for the info.

Have a look here at Debra Dalgleish's site.

http://www.contextures.on.ca/xlUserForm01.html


Gord

On Tue, 6 Jan 2009 03:08:08 -0800 (PST), Anthony
wrote:

On Jan 6, 3:06*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.- Hide quoted text -

- Show quoted text -

Oh, I have not done a Userform before. I will have to find a tutorial
that teaches me how to create one. Thanks for the info.

On Jan 6, 8:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Have a look here at Debra Dalgleish's site.

http://www.contextures.on.ca/xlUserForm01.html

Gord

On Tue, 6 Jan 2009 03:08:08 -0800 (PST), Anthony



wrote:
On Jan 6, 3:06*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.- Hide quoted text -

- Show quoted text -

Oh, I have not done a Userform before. *I will have to find a tutorial
that teaches me how to create one. *Thanks for the info.- Hide quoted text -

- Show quoted text -

I found a tutorial online at http://exceltip.com/st/Create_User_F...Excel/629.html
and it helped me quite a bit. I created a UserForm that I called
frmLeave however I did get a runtime error when I opened the workbook
and clicked yes to the proposed question. The error message said that
an object was expected. Here is the code...can someone please
troubleshoot it for me.


Private Sub UserForm_Click()
txtLastName.Value = " "
txtLeaveStartDate.Value = " "
txtLeaveEndDate.Value = " "

End Sub

Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Leave Request").Activate
Range("D1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtLastName.Value
ActiveCell.Offset(0, 1) = txtLeaveStartDate.Value
ActiveCell.Offset(0, 2) = txtLeaveEndDate.Value

End Sub

Private Sub cmdAdd_Click()
Call UserForm_Initialize
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClearForm_Click()
Call UserForm_Initialize
End Sub

Private Sub UserForm_Initialize()
txtLastName.Value = ""
txtLeaveStartDate.Value = ""
txtLeaveEndDate.Value = ""
txtName.SetFocus
End Sub

Without testing, looks to me like you should remove the spaces between " "
in these lines.

txtLastName.Value = " "
txtLeaveStartDate.Value = " "
txtLeaveEndDate.Value = " "

You want the initial values to be nothing ""


Gord

On Tue, 6 Jan 2009 13:06:09 -0800 (PST), Anthony
wrote:

On Jan 6, 8:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Have a look here at Debra Dalgleish's site.

http://www.contextures.on.ca/xlUserForm01.html

Gord

On Tue, 6 Jan 2009 03:08:08 -0800 (PST), Anthony



wrote:
On Jan 6, 3:06*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.- Hide quoted text -

- Show quoted text -

Oh, I have not done a Userform before. *I will have to find a tutorial
that teaches me how to create one. *Thanks for the info.- Hide quoted text -

- Show quoted text -

I found a tutorial online at http://exceltip.com/st/Create_User_F...Excel/629.html
and it helped me quite a bit. I created a UserForm that I called
frmLeave however I did get a runtime error when I opened the workbook
and clicked yes to the proposed question. The error message said that
an object was expected. Here is the code...can someone please
troubleshoot it for me.


Private Sub UserForm_Click()
txtLastName.Value = " "
txtLeaveStartDate.Value = " "
txtLeaveEndDate.Value = " "

End Sub

Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Leave Request").Activate
Range("D1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtLastName.Value
ActiveCell.Offset(0, 1) = txtLeaveStartDate.Value
ActiveCell.Offset(0, 2) = txtLeaveEndDate.Value

End Sub

Private Sub cmdAdd_Click()
Call UserForm_Initialize
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClearForm_Click()
Call UserForm_Initialize
End Sub

Private Sub UserForm_Initialize()
txtLastName.Value = ""
txtLeaveStartDate.Value = ""
txtLeaveEndDate.Value = ""
txtName.SetFocus
End Sub

On Jan 6, 8:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Have a look here at Debra Dalgleish's site.

http://www.contextures.on.ca/xlUserForm01.html

Gord

On Tue, 6 Jan 2009 03:08:08 -0800 (PST), Anthony



wrote:
On Jan 6, 3:06*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.- Hide quoted text -

- Show quoted text -

Oh, I have not done a Userform before. *I will have to find a tutorial
that teaches me how to create one. *Thanks for the info.- Hide quoted text -

- Show quoted text -

I changed a little of the code and got it to work ALMOST. When it
enters the data into the worksheet, it fails to write the leave end
date.

Private Sub UserForm_Click()
txtLastName.Value = " "
txtLeaveStartDate.Value = " "
txtLeaveEndDate.Value = " "
txtLastName.SetFocus

End Sub

Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Leave Request").Activate
Range("D1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtLastName.Value
ActiveCell.Offset(0, 1) = txtLeaveStartDate.Value
ActiveCell.Offset(0, 3) = txtLeaveEndDate.Value

End Sub
Private Sub cmdAdd_Click()
Call UserForm_Click
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClearForm_Click()
Call UserForm_Click
End Sub

On Jan 7, 7:19*am, Anthony wrote:
On Jan 6, 8:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:





Have a look here at Debra Dalgleish's site.

http://www.contextures.on.ca/xlUserForm01.html

Gord

On Tue, 6 Jan 2009 03:08:08 -0800 (PST), Anthony

wrote:
On Jan 6, 3:06*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.- Hide quoted text -

- Show quoted text -

Oh, I have not done a Userform before. *I will have to find a tutorial
that teaches me how to create one. *Thanks for the info.- Hide quoted text -

- Show quoted text -

I changed a little of the code and got it to work ALMOST. *When it
enters the data into the worksheet, it fails to write the leave end
date.

Private Sub UserForm_Click()
*txtLastName.Value = " "
*txtLeaveStartDate.Value = " "
*txtLeaveEndDate.Value = " "
*txtLastName.SetFocus

End Sub

Private Sub cmdOK_Click()
* * ActiveWorkbook.Sheets("Leave Request").Activate
* * Range("D1").Select
* * Do
* * If IsEmpty(ActiveCell) = False Then
* * * * ActiveCell.Offset(1, 0).Select
* * End If
* * Loop Until IsEmpty(ActiveCell) = True
* * ActiveCell.Value = txtLastName.Value
* * ActiveCell.Offset(0, 1) = txtLeaveStartDate.Value
* * ActiveCell.Offset(0, 3) = txtLeaveEndDate.Value

End Sub
Private Sub cmdAdd_Click()
* * Call UserForm_Click
End Sub

Private Sub cmdCancel_Click()
* * Unload Me
End Sub

Private Sub cmdClearForm_Click()
* * Call UserForm_Click
End Sub- Hide quoted text -

- Show quoted text

Thanks to everyone who helped me. I think I figured it out...I did
not name the text box "txtLeaveEndDate". It is fixed and working
now. One last thing though, is there a quick way to have this
worksheet protected but to unprotect it when OK is clicked and then
add protection again once the information is written into the
worksheet.

On Jan 7, 7:44*am, Anthony wrote:
On Jan 7, 7:19*am, Anthony wrote:





On Jan 6, 8:03*pm, Gord Dibben <gorddibbATshawDOTca wrote:

Have a look here at Debra Dalgleish's site.

http://www.contextures.on.ca/xlUserForm01.html

Gord

On Tue, 6 Jan 2009 03:08:08 -0800 (PST), Anthony

wrote:
On Jan 6, 3:06*am, Gord Dibben <gorddibbATshawDOTca wrote:
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.- Hide quoted text -

- Show quoted text -

Oh, I have not done a Userform before. *I will have to find a tutorial
that teaches me how to create one. *Thanks for the info.- Hide quoted text -

- Show quoted text -

I changed a little of the code and got it to work ALMOST. *When it
enters the data into the worksheet, it fails to write the leave end
date.

Private Sub UserForm_Click()
*txtLastName.Value = " "
*txtLeaveStartDate.Value = " "
*txtLeaveEndDate.Value = " "
*txtLastName.SetFocus

End Sub

Private Sub cmdOK_Click()
* * ActiveWorkbook.Sheets("Leave Request").Activate
* * Range("D1").Select
* * Do
* * If IsEmpty(ActiveCell) = False Then
* * * * ActiveCell.Offset(1, 0).Select
* * End If
* * Loop Until IsEmpty(ActiveCell) = True
* * ActiveCell.Value = txtLastName.Value
* * ActiveCell.Offset(0, 1) = txtLeaveStartDate.Value
* * ActiveCell.Offset(0, 3) = txtLeaveEndDate.Value

End Sub
Private Sub cmdAdd_Click()
* * Call UserForm_Click
End Sub

Private Sub cmdCancel_Click()
* * Unload Me
End Sub

Private Sub cmdClearForm_Click()
* * Call UserForm_Click
End Sub- Hide quoted text -

- Show quoted text

Thanks to everyone who helped me. *I think I figured it out...I did
not name the text box "txtLeaveEndDate". *It is fixed and working
now. *One last thing though, is there a quick way to have this
worksheet protected but to unprotect it when OK is clicked and then
add protection again once the information is written into the
worksheet.- Hide quoted text -

- Show quoted text -

I found something by using th search group button. Thanks for all
your help.