User must agree to User Agreement

Please be advised that I am a bit new to the "more advanced" programming.

I am seeking help with programing a workbook where a user must agree with a
disclaimer before the workbook will either:

1) File to open to the disclaimer worksheet only, and then only allow user
to enter other worksheets within the workbook after the user agrees to a
disclaimer. -Preferred-

2) File will not open until they click to agree to a disclaimer.

Thanks in advance

You can accomplish #1 in the following manner. First, put your
disclaimer text on a worksheet named "Disclaimer". Then, hide all
other worksheets, so that only Disclaimer is visible. In the
ThisWorkbook code module, put the following code:

Private Sub Worksheet_Open()
If MsgBox("Do you agree to the disclaimer?",vbYesNo) = vbNo
Then
ThisWorkbook.Close savechanges:=False
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.Name <> "Disclaimer"
WS.Visible = xlVeryHidden
End If
Next WS
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Peepster" <[email protected]> wrote in message
news:[email protected]...
> Please be advised that I am a bit new to the "more advanced"
> programming.
>
> I am seeking help with programing a workbook where a user must
> agree with a
> disclaimer before the workbook will either:
>
> 1) File to open to the disclaimer worksheet only, and then only
> allow user
> to enter other worksheets within the workbook after the user
> agrees to a
> disclaimer. -Preferred-
>
> 2) File will not open until they click to agree to a
> disclaimer.
>
> Thanks in advance
>
>

Hi, Chip:

In the Open event procedure, didn't you omit the lines to unhide the
worksheets if the user agrees?

Myrna

On Sun, 20 Feb 2005 12:19:19 -0600, "Chip Pearson" <[email protected]> wrote:

>You can accomplish #1 in the following manner. First, put your
>disclaimer text on a worksheet named "Disclaimer". Then, hide all
>other worksheets, so that only Disclaimer is visible. In the
>ThisWorkbook code module, put the following code:
>
>Private Sub Worksheet_Open()
> If MsgBox("Do you agree to the disclaimer?",vbYesNo) = vbNo
>Then
> ThisWorkbook.Close savechanges:=False
> End If
>End Sub
>
>Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Dim WS As Worksheet
> For Each WS In ThisWorkbook.Worksheets
> If WS.Name <> "Disclaimer"
> WS.Visible = xlVeryHidden
> End If
> Next WS
>End Sub

Yeah, I forgot those.

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Visible = True
Next WS


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Myrna Larson" <[email protected]> wrote in
message news:[email protected]...
> Hi, Chip:
>
> In the Open event procedure, didn't you omit the lines to
> unhide the
> worksheets if the user agrees?
>
> Myrna
>
> On Sun, 20 Feb 2005 12:19:19 -0600, "Chip Pearson"
> <[email protected]> wrote:
>
>>You can accomplish #1 in the following manner. First, put your
>>disclaimer text on a worksheet named "Disclaimer". Then, hide
>>all
>>other worksheets, so that only Disclaimer is visible. In the
>>ThisWorkbook code module, put the following code:
>>
>>Private Sub Worksheet_Open()
>> If MsgBox("Do you agree to the disclaimer?",vbYesNo) =
>> vbNo
>>Then
>> ThisWorkbook.Close savechanges:=False
>> End If
>>End Sub
>>
>>Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> Dim WS As Worksheet
>> For Each WS In ThisWorkbook.Worksheets
>> If WS.Name <> "Disclaimer"
>> WS.Visible = xlVeryHidden
>> End If
>> Next WS
>>End Sub
>

I have one sheet named "Data" that I want to keep hidden. All others should
become visible except the "Data" worksheet. What is the code for that?

I am obviously very new to this type programming. I have to ask, "Does this
code go into a Macro?" Sorry to prove my ignorance.

I will play with it until I get it figured out.
Thanks,
Paul
[email protected]

"Chip Pearson" wrote:

> Yeah, I forgot those.
>
> Dim WS As Worksheet
> For Each WS In ThisWorkbook.Worksheets
> WS.Visible = True
> Next WS
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Myrna Larson" <[email protected]> wrote in
> message news:[email protected]...
> > Hi, Chip:
> >
> > In the Open event procedure, didn't you omit the lines to
> > unhide the
> > worksheets if the user agrees?
> >
> > Myrna
> >
> > On Sun, 20 Feb 2005 12:19:19 -0600, "Chip Pearson"
> > <[email protected]> wrote:
> >
> >>You can accomplish #1 in the following manner. First, put your
> >>disclaimer text on a worksheet named "Disclaimer". Then, hide
> >>all
> >>other worksheets, so that only Disclaimer is visible. In the
> >>ThisWorkbook code module, put the following code:
> >>
> >>Private Sub Worksheet_Open()
> >> If MsgBox("Do you agree to the disclaimer?",vbYesNo) =
> >> vbNo
> >>Then
> >> ThisWorkbook.Close savechanges:=False
> >> End If
> >>End Sub
> >>
> >>Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >> Dim WS As Worksheet
> >> For Each WS In ThisWorkbook.Worksheets
> >> If WS.Name <> "Disclaimer"
> >> WS.Visible = xlVeryHidden
> >> End If
> >> Next WS
> >>End Sub
> >
>
>
>

The modify that section to:

For Each WS In ThisWorkbook.Worksheets
If WS.Name <> "Data" Then WS.Visible = True
Next WS

All of the code goes into the Worksheet_Open event in the module named
ThisWorkbook.

On Mon, 21 Feb 2005 10:35:05 -0800, "Peepster"
<[email protected]> wrote:

>I have one sheet named "Data" that I want to keep hidden. All others should
>become visible except the "Data" worksheet. What is the code for that?
>
>I am obviously very new to this type programming. I have to ask, "Does this
>code go into a Macro?" Sorry to prove my ignorance.
>
>I will play with it until I get it figured out.
>Thanks,
>Paul
>[email protected]
>
>"Chip Pearson" wrote:
>
>> Yeah, I forgot those.
>>
>> Dim WS As Worksheet
>> For Each WS In ThisWorkbook.Worksheets
>> WS.Visible = True
>> Next WS
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>> "Myrna Larson" <[email protected]> wrote in
>> message news:[email protected]...
>> > Hi, Chip:
>> >
>> > In the Open event procedure, didn't you omit the lines to
>> > unhide the
>> > worksheets if the user agrees?
>> >
>> > Myrna
>> >
>> > On Sun, 20 Feb 2005 12:19:19 -0600, "Chip Pearson"
>> > <[email protected]> wrote:
>> >
>> >>You can accomplish #1 in the following manner. First, put your
>> >>disclaimer text on a worksheet named "Disclaimer". Then, hide
>> >>all
>> >>other worksheets, so that only Disclaimer is visible. In the
>> >>ThisWorkbook code module, put the following code:
>> >>
>> >>Private Sub Worksheet_Open()
>> >> If MsgBox("Do you agree to the disclaimer?",vbYesNo) =
>> >> vbNo
>> >>Then
>> >> ThisWorkbook.Close savechanges:=False
>> >> End If
>> >>End Sub
>> >>
>> >>Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> >> Dim WS As Worksheet
>> >> For Each WS In ThisWorkbook.Worksheets
>> >> If WS.Name <> "Disclaimer"
>> >> WS.Visible = xlVeryHidden
>> >> End If
>> >> Next WS
>> >>End Sub
>> >
>>
>>
>>