Dialog box upon opening workbook

Anthony · Jan 3, 2009

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.

Bob Phillips · Jan 3, 2009

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?

Anthony · Jan 4, 2009

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

- 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.

Anthony · Jan 4, 2009

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

- 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.

Bob Phillips · Jan 4, 2009

Anthony,

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

--
__________________________________
HTH

Bob

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

- 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 · Jan 5, 2009

Anthony,

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

--
__________________________________
HTH

Bob

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.

Gord Dibben · Jan 6, 2009

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

Anthony · Jan 6, 2009

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

- 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.

Gord Dibben · Jan 6, 2009

Have a look here at Debra Dalgleish's site.

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

Gord

Anthony · Jan 6, 2009

Have a look here at Debra Dalgleish's site.

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

Gord

- Show quoted text -

I found a tutorial online at http://exceltip.com/st/Create_User_Forms_in_Microsoft_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

Gord Dibben · Jan 6, 2009

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

Anthony · Jan 7, 2009

Have a look here at Debra Dalgleish's site.

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

Gord

- 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

Anthony · Jan 7, 2009

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.

Anthony · Jan 7, 2009

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.

Military leave tracking	1	Jan 3, 2009
Macro to launch the Activate dialog box?	2	Dec 5, 2008
Input Dialog Box - Simple Question	3	Dec 15, 2005
input box upon opening workbook	3	Mar 29, 2005
Cursor in A2 upon opening sheet	1	Apr 6, 2004
Save workbook with preview in open file dialog box	2	Apr 21, 2004
how to add a dialog box when you first open excel workbook	1	Jun 22, 2005
input box upon workbook open	1	Mar 29, 2005

Dialog box upon opening workbook

Anthony

Bob Phillips

Anthony

Anthony

Bob Phillips

Anthony

Gord Dibben

Anthony

Gord Dibben

Anthony

Gord Dibben

Anthony

Anthony

Anthony

Ask a Question

Similar Threads