Interupted Scrollarea

[ZipCurs]

I have a worksheet that I am setting a variable ScrollArea, depending on how
much information is on the worksheet. This works fine. The problem is that
I have a couple of Check Boxes and a List Box in the field, and I do not want
their linked cells to be accessable.

The only reason for expanding the ScrollArea is so that I can see the
ListBox contents all at once, which I actually need.

I can move the linked cells out of the ScrollArea field, but this is a
painful solution, since these are referenced by multiple macros.

Thanks for any help.

[Ron de Bruin]

Why not lock the cells and protect your sheet ?

--
Regards Ron De Bruin
http://www.rondebruin.nl



"ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:2547CCF3-7399-44B4-AD99-BBBC92D9786A@microsoft.com...
>I have a worksheet that I am setting a variable ScrollArea, depending on how
> much information is on the worksheet. This works fine. The problem is that
> I have a couple of Check Boxes and a List Box in the field, and I do not want
> their linked cells to be accessable.
>
> The only reason for expanding the ScrollArea is so that I can see the
> ListBox contents all at once, which I actually need.
>
> I can move the linked cells out of the ScrollArea field, but this is a
> painful solution, since these are referenced by multiple macros.
>
> Thanks for any help.

[ZipCurs]

Thanks for the quick response.

I just reconfirmed this. If I lock the linked cells, the Check Boxes
indicate that the sheet must be unprotected first. I need to use the Check
Boxes and List Box with the sheet protected.

"Ron de Bruin" wrote:

> Why not lock the cells and protect your sheet ?
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:2547CCF3-7399-44B4-AD99-BBBC92D9786A@microsoft.com...
> >I have a worksheet that I am setting a variable ScrollArea, depending on how
> > much information is on the worksheet. This works fine. The problem is that
> > I have a couple of Check Boxes and a List Box in the field, and I do not want
> > their linked cells to be accessable.
> >
> > The only reason for expanding the ScrollArea is so that I can see the
> > ListBox contents all at once, which I actually need.
> >
> > I can move the linked cells out of the ScrollArea field, but this is a
> > painful solution, since these are referenced by multiple macros.
> >
> > Thanks for any help.
>
>
>

[Ron de Bruin]

Try this

You can protect your sheet with code like this
All code will run then on the protected cells

Copy this in the thisworkbook module and save /close/reopen the workbook

Private Sub Workbook_Open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub




--
Regards Ron De Bruin
http://www.rondebruin.nl



"ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:F8BDB054-3CDE-4DCE-B86B-2B2E2019EA72@microsoft.com...
> Thanks for the quick response.
>
> I just reconfirmed this. If I lock the linked cells, the Check Boxes
> indicate that the sheet must be unprotected first. I need to use the Check
> Boxes and List Box with the sheet protected.
>
> "Ron de Bruin" wrote:
>
>> Why not lock the cells and protect your sheet ?
>>
>> --
>> Regards Ron De Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:2547CCF3-7399-44B4-AD99-BBBC92D9786A@microsoft.com...
>> >I have a worksheet that I am setting a variable ScrollArea, depending on how
>> > much information is on the worksheet. This works fine. The problem is that
>> > I have a couple of Check Boxes and a List Box in the field, and I do not want
>> > their linked cells to be accessable.
>> >
>> > The only reason for expanding the ScrollArea is so that I can see the
>> > ListBox contents all at once, which I actually need.
>> >
>> > I can move the linked cells out of the ScrollArea field, but this is a
>> > painful solution, since these are referenced by multiple macros.
>> >
>> > Thanks for any help.
>>
>>
>>

[ZipCurs]

This did not work. I suspect that I am not making myself clear on this.

The issue is not with code running on the locked cells, since the applicable
macros unprotect the sheet before running any operations. The issue is with
the Check Box and linked cell, which will not work if the cell is locked and
the sheet is protected. If it were possible to split up the Scroll Area to
avoid the linked cells or make it so these could not be selected, that would
satisfy my requirements.

"Ron de Bruin" wrote:

> Try this
>
> You can protect your sheet with code like this
> All code will run then on the protected cells
>
> Copy this in the thisworkbook module and save /close/reopen the workbook
>
> Private Sub Workbook_Open()
> With Worksheets("sheet1")
> .Protect Password:="hi", userinterfaceonly:=True
> End With
> End Sub
>
>
>
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:F8BDB054-3CDE-4DCE-B86B-2B2E2019EA72@microsoft.com...
> > Thanks for the quick response.
> >
> > I just reconfirmed this. If I lock the linked cells, the Check Boxes
> > indicate that the sheet must be unprotected first. I need to use the Check
> > Boxes and List Box with the sheet protected.
> >
> > "Ron de Bruin" wrote:
> >
> >> Why not lock the cells and protect your sheet ?
> >>
> >> --
> >> Regards Ron De Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:2547CCF3-7399-44B4-AD99-BBBC92D9786A@microsoft.com...
> >> >I have a worksheet that I am setting a variable ScrollArea, depending on how
> >> > much information is on the worksheet. This works fine. The problem is that
> >> > I have a couple of Check Boxes and a List Box in the field, and I do not want
> >> > their linked cells to be accessable.
> >> >
> >> > The only reason for expanding the ScrollArea is so that I can see the
> >> > ListBox contents all at once, which I actually need.
> >> >
> >> > I can move the linked cells out of the ScrollArea field, but this is a
> >> > painful solution, since these are referenced by multiple macros.
> >> >
> >> > Thanks for any help.
> >>
> >>
> >>
>
>
>

[Ron de Bruin]

hi ZipCurs

You can use the selection change event maybe
This example select the cell next to the cells in the Range("A1,C3,D5") if you select them

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("A1,C3,D5"), Target) Is Nothing Then
Target.Offset(0, 1).Select
End If
End Sub


--
Regards Ron De Bruin
http://www.rondebruin.nl



"ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:F0E63DEB-ABDB-4C62-8D51-26AF1C68ADFC@microsoft.com...
> This did not work. I suspect that I am not making myself clear on this.
>
> The issue is not with code running on the locked cells, since the applicable
> macros unprotect the sheet before running any operations. The issue is with
> the Check Box and linked cell, which will not work if the cell is locked and
> the sheet is protected. If it were possible to split up the Scroll Area to
> avoid the linked cells or make it so these could not be selected, that would
> satisfy my requirements.
>
> "Ron de Bruin" wrote:
>
>> Try this
>>
>> You can protect your sheet with code like this
>> All code will run then on the protected cells
>>
>> Copy this in the thisworkbook module and save /close/reopen the workbook
>>
>> Private Sub Workbook_Open()
>> With Worksheets("sheet1")
>> .Protect Password:="hi", userinterfaceonly:=True
>> End With
>> End Sub
>>
>>
>>
>>
>> --
>> Regards Ron De Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:F8BDB054-3CDE-4DCE-B86B-2B2E2019EA72@microsoft.com...
>> > Thanks for the quick response.
>> >
>> > I just reconfirmed this. If I lock the linked cells, the Check Boxes
>> > indicate that the sheet must be unprotected first. I need to use the Check
>> > Boxes and List Box with the sheet protected.
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Why not lock the cells and protect your sheet ?
>> >>
>> >> --
>> >> Regards Ron De Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >>
>> >> "ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:2547CCF3-7399-44B4-AD99-BBBC92D9786A@microsoft.com...
>> >> >I have a worksheet that I am setting a variable ScrollArea, depending on how
>> >> > much information is on the worksheet. This works fine. The problem is that
>> >> > I have a couple of Check Boxes and a List Box in the field, and I do not want
>> >> > their linked cells to be accessable.
>> >> >
>> >> > The only reason for expanding the ScrollArea is so that I can see the
>> >> > ListBox contents all at once, which I actually need.
>> >> >
>> >> > I can move the linked cells out of the ScrollArea field, but this is a
>> >> > painful solution, since these are referenced by multiple macros.
>> >> >
>> >> > Thanks for any help.
>> >>
>> >>
>> >>
>>
>>
>>

[ZipCurs]

Ron,

Thanks for your help. I did not try your last suggestion. Instead, I just
bit the bullet and moved the linked cells outside of the ScrollArea, which
although a bit of a pain worked just fine. Thanks again.

"Ron de Bruin" wrote:

> hi ZipCurs
>
> You can use the selection change event maybe
> This example select the cell next to the cells in the Range("A1,C3,D5") if you select them
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Not Application.Intersect(Range("A1,C3,D5"), Target) Is Nothing Then
> Target.Offset(0, 1).Select
> End If
> End Sub
>
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:F0E63DEB-ABDB-4C62-8D51-26AF1C68ADFC@microsoft.com...
> > This did not work. I suspect that I am not making myself clear on this.
> >
> > The issue is not with code running on the locked cells, since the applicable
> > macros unprotect the sheet before running any operations. The issue is with
> > the Check Box and linked cell, which will not work if the cell is locked and
> > the sheet is protected. If it were possible to split up the Scroll Area to
> > avoid the linked cells or make it so these could not be selected, that would
> > satisfy my requirements.
> >
> > "Ron de Bruin" wrote:
> >
> >> Try this
> >>
> >> You can protect your sheet with code like this
> >> All code will run then on the protected cells
> >>
> >> Copy this in the thisworkbook module and save /close/reopen the workbook
> >>
> >> Private Sub Workbook_Open()
> >> With Worksheets("sheet1")
> >> .Protect Password:="hi", userinterfaceonly:=True
> >> End With
> >> End Sub
> >>
> >>
> >>
> >>
> >> --
> >> Regards Ron De Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:F8BDB054-3CDE-4DCE-B86B-2B2E2019EA72@microsoft.com...
> >> > Thanks for the quick response.
> >> >
> >> > I just reconfirmed this. If I lock the linked cells, the Check Boxes
> >> > indicate that the sheet must be unprotected first. I need to use the Check
> >> > Boxes and List Box with the sheet protected.
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> Why not lock the cells and protect your sheet ?
> >> >>
> >> >> --
> >> >> Regards Ron De Bruin
> >> >> http://www.rondebruin.nl
> >> >>
> >> >>
> >> >>
> >> >> "ZipCurs" <ZipCurs@discussions.microsoft.com> wrote in message news:2547CCF3-7399-44B4-AD99-BBBC92D9786A@microsoft.com...
> >> >> >I have a worksheet that I am setting a variable ScrollArea, depending on how
> >> >> > much information is on the worksheet. This works fine. The problem is that
> >> >> > I have a couple of Check Boxes and a List Box in the field, and I do not want
> >> >> > their linked cells to be accessable.
> >> >> >
> >> >> > The only reason for expanding the ScrollArea is so that I can see the
> >> >> > ListBox contents all at once, which I actually need.
> >> >> >
> >> >> > I can move the linked cells out of the ScrollArea field, but this is a
> >> >> > painful solution, since these are referenced by multiple macros.
> >> >> >
> >> >> > Thanks for any help.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>