Dialog box upon opening workbook

[Anthony]

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]

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]

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]

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]

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]

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]

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]

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]

Have a look here at Debra Dalgleish's site.

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


Gord

 

[Anthony]

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]

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]

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]

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]

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.