macros and protection

[SYBS]

Hi, hope somebody can throw some light on this problem I am having with macros.

I have a scoresheet with 60 contestants. Each contestant takes up 7 rows,
the first six of which are hidden to start with and I have put macros in the
adjoining column so that when they are clicked, the full 7 rows open up and
the table of scores can be entered, When entry is complete for that
contestant, a further macro when clicked will close up the 6 rows, leaving
just the main line (line 14) with the No, Name, “OPEN” macro and other Totals
in adjoining columns.

The sheet works fine, but as many people will use this programme, I need to
protect the sheets against mistaken entries etc., and as soon as I protect
it, the macros wont work and throw up a “unable to set the property of a
hidden range class, run time error 1004. I don’t want to leave the sheet
unprotected, can anyone advise me where I am going wrong.

I am also trying to find a way to validate “time taken” entries so that they
can only be input as minutes and seconds in the format of 09.56, within a
range of 00.01 – 10.00. Not having any success with this as it keeps
converting the data into something like a date.

I would be very grateful for any help you can give me.

Thanks in anticipation.

Sybs

[Dave Peterson]

How about having your macro unprotect the sheet, do the work and then reprotect
the sheet?

SYBS wrote:
>
> Hi, hope somebody can throw some light on this problem I am having with macros.
>
> I have a scoresheet with 60 contestants. Each contestant takes up 7 rows,
> the first six of which are hidden to start with and I have put macros in the
> adjoining column so that when they are clicked, the full 7 rows open up and
> the table of scores can be entered, When entry is complete for that
> contestant, a further macro when clicked will close up the 6 rows, leaving
> just the main line (line 14) with the No, Name, “OPEN” macro and other Totals
> in adjoining columns.
>
> The sheet works fine, but as many people will use this programme, I need to
> protect the sheets against mistaken entries etc., and as soon as I protect
> it, the macros wont work and throw up a “unable to set the property of a
> hidden range class, run time error 1004. I don’t want to leave the sheet
> unprotected, can anyone advise me where I am going wrong.
>
> I am also trying to find a way to validate “time taken” entries so that they
> can only be input as minutes and seconds in the format of 09.56, within a
> range of 00.01 – 10.00. Not having any success with this as it keeps
> converting the data into something like a date.
>
> I would be very grateful for any help you can give me.
>
> Thanks in anticipation.
>
> Sybs

--

Dave Peterson

[Pete_UK]

When you say that several people will use the programme, do you mean
they will share it on a network, or will it be on individual,
non-connected PCs? If shared, then it will be difficult - I understand
that the first User to open the file grabs the read/write rights and
subsequent Users have read-only rights (but I have no direct experience
of this).

On the data input side, couldn't you force the User to enter a 4
character string of digits (without any delimiter) and then split this
yourself in the macro into minutes and seconds, checking for invalid
entries of 6 to 9 in the third character?

Hope this helps.

Pete

[SYBS]

No, seperate p.c.'s not connected,my worry being that the more people who use
it for 'local scoring' the more important it is to protect the formulas from
user error.

reference the macro for 4 digits, I'm not very clever at this I'm afraid,
just learning as I go along and unfortunately dont know how to write that
type of macro, although I will try and see if I can work it out.

On the protection side of things, I have tried ActiveSheet.unprotect
(password)....ActiveSheet.Protect(password) and it keeps coming up with an
incorrect password error. I'm a bit stumped here really.

sybs

"Pete_UK" wrote:

> When you say that several people will use the programme, do you mean
> they will share it on a network, or will it be on individual,
> non-connected PCs? If shared, then it will be difficult - I understand
> that the first User to open the file grabs the read/write rights and
> subsequent Users have read-only rights (but I have no direct experience
> of this).
>
> On the data input side, couldn't you force the User to enter a 4
> character string of digits (without any delimiter) and then split this
> yourself in the macro into minutes and seconds, checking for invalid
> entries of 6 to 9 in the third character?
>
> Hope this helps.
>
> Pete
>
>

[SYBS]

Thanks Dave, it didnt seem to work very well, kept throwing up a incorrect
password error. not sure what I am doing wrong.

"Dave Peterson" wrote:

> How about having your macro unprotect the sheet, do the work and then reprotect
> the sheet?
>
> SYBS wrote:
> >
> > Hi, hope somebody can throw some light on this problem I am having with macros.
> >
> > I have a scoresheet with 60 contestants. Each contestant takes up 7 rows,
> > the first six of which are hidden to start with and I have put macros in the
> > adjoining column so that when they are clicked, the full 7 rows open up and
> > the table of scores can be entered, When entry is complete for that
> > contestant, a further macro when clicked will close up the 6 rows, leaving
> > just the main line (line 14) with the No, Name, “OPEN” macro and other Totals
> > in adjoining columns.
> >
> > The sheet works fine, but as many people will use this programme, I need to
> > protect the sheets against mistaken entries etc., and as soon as I protect
> > it, the macros wont work and throw up a “unable to set the property of a
> > hidden range class, run time error 1004. I don’t want to leave the sheet
> > unprotected, can anyone advise me where I am going wrong.
> >
> > I am also trying to find a way to validate “time taken” entries so that they
> > can only be input as minutes and seconds in the format of 09.56, within a
> > range of 00.01 – 10.00. Not having any success with this as it keeps
> > converting the data into something like a date.
> >
> > I would be very grateful for any help you can give me.
> >
> > Thanks in anticipation.
> >
> > Sybs
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I'd say you're not using the correct password (watch upper/lower case) or you're
trying to unprotect the wrong worksheet.

SYBS wrote:
>
> Thanks Dave, it didnt seem to work very well, kept throwing up a incorrect
> password error. not sure what I am doing wrong.
>
> "Dave Peterson" wrote:
>
> > How about having your macro unprotect the sheet, do the work and then reprotect
> > the sheet?
> >
> > SYBS wrote:
> > >
> > > Hi, hope somebody can throw some light on this problem I am having with macros.
> > >
> > > I have a scoresheet with 60 contestants. Each contestant takes up 7 rows,
> > > the first six of which are hidden to start with and I have put macros in the
> > > adjoining column so that when they are clicked, the full 7 rows open up and
> > > the table of scores can be entered, When entry is complete for that
> > > contestant, a further macro when clicked will close up the 6 rows, leaving
> > > just the main line (line 14) with the No, Name, “OPEN” macro and other Totals
> > > in adjoining columns.
> > >
> > > The sheet works fine, but as many people will use this programme, I need to
> > > protect the sheets against mistaken entries etc., and as soon as I protect
> > > it, the macros wont work and throw up a “unable to set the property of a
> > > hidden range class, run time error 1004. I don’t want to leave the sheet
> > > unprotected, can anyone advise me where I am going wrong.
> > >
> > > I am also trying to find a way to validate “time taken” entries so that they
> > > can only be input as minutes and seconds in the format of 09.56, within a
> > > range of 00.01 – 10.00. Not having any success with this as it keeps
> > > converting the data into something like a date.
> > >
> > > I would be very grateful for any help you can give me.
> > >
> > > Thanks in anticipation.
> > >
> > > Sybs
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[SYBS]

Hi Dave, thanks for that, you are absolutely right, wrong password not to
mention not putting it in quotes or brackets, got it working now but further
problem to help with if you can. I now have 60 macros on the 60 relevant
lines to open it up individually, and 60 more to close it individually, (all
working !!), but.... I want one simple macro to "open all" or close all" can
you help please. Any ideas about min:secs formula or validations.

Cheers

sybs


"Dave Peterson" wrote:

> I'd say you're not using the correct password (watch upper/lower case) or you're
> trying to unprotect the wrong worksheet.
>
> SYBS wrote:
> >
> > Thanks Dave, it didnt seem to work very well, kept throwing up a incorrect
> > password error. not sure what I am doing wrong.
> >
> > "Dave Peterson" wrote:
> >
> > > How about having your macro unprotect the sheet, do the work and then reprotect
> > > the sheet?
> > >
> > > SYBS wrote:
> > > >
> > > > Hi, hope somebody can throw some light on this problem I am having with macros.
> > > >
> > > > I have a scoresheet with 60 contestants. Each contestant takes up 7 rows,
> > > > the first six of which are hidden to start with and I have put macros in the
> > > > adjoining column so that when they are clicked, the full 7 rows open up and
> > > > the table of scores can be entered, When entry is complete for that
> > > > contestant, a further macro when clicked will close up the 6 rows, leaving
> > > > just the main line (line 14) with the No, Name, “OPEN” macro and other Totals
> > > > in adjoining columns.
> > > >
> > > > The sheet works fine, but as many people will use this programme, I need to
> > > > protect the sheets against mistaken entries etc., and as soon as I protect
> > > > it, the macros wont work and throw up a “unable to set the property of a
> > > > hidden range class, run time error 1004. I don’t want to leave the sheet
> > > > unprotected, can anyone advise me where I am going wrong.
> > > >
> > > > I am also trying to find a way to validate “time taken” entries so that they
> > > > can only be input as minutes and seconds in the format of 09.56, within a
> > > > range of 00.01 – 10.00. Not having any success with this as it keeps
> > > > converting the data into something like a date.
> > > >
> > > > I would be very grateful for any help you can give me.
> > > >
> > > > Thanks in anticipation.
> > > >
> > > > Sybs
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Personally, I think the benefits of entering time as time outweigh the benefits
of using a decimal point when entering data.

And depending on how the rows are laid out, you could use:

Option Explicit
Sub testme()
Dim wks As Worksheet
Set wks = Worksheets("sheet1")
wks.Rows.Hidden = False
End Sub
Sub testme02()
Dim wks As Worksheet
Dim StartRow As Long
Dim HowManyRows As Long
Dim iRow As Long

Set wks = Worksheets("sheet1")

StartRow = 5
HowManyRows = 6

For iRow = StartRow To 100 Step HowManyRows + 1
wks.Rows(iRow).Offset(1, 0).Resize(HowManyRows).Hidden = True
Next iRow
End Sub

Chip Pearson has some routines for quick entry of dates/times:
http://www.cpearson.com/excel/DateTimeEntry.htm


SYBS wrote:
>
> Hi Dave, thanks for that, you are absolutely right, wrong password not to
> mention not putting it in quotes or brackets, got it working now but further
> problem to help with if you can. I now have 60 macros on the 60 relevant
> lines to open it up individually, and 60 more to close it individually, (all
> working !!), but.... I want one simple macro to "open all" or close all" can
> you help please. Any ideas about min:secs formula or validations.
>
> Cheers
>
> sybs
>
> "Dave Peterson" wrote:
>
> > I'd say you're not using the correct password (watch upper/lower case) or you're
> > trying to unprotect the wrong worksheet.
> >
> > SYBS wrote:
> > >
> > > Thanks Dave, it didnt seem to work very well, kept throwing up a incorrect
> > > password error. not sure what I am doing wrong.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > How about having your macro unprotect the sheet, do the work and then reprotect
> > > > the sheet?
> > > >
> > > > SYBS wrote:
> > > > >
> > > > > Hi, hope somebody can throw some light on this problem I am having with macros.
> > > > >
> > > > > I have a scoresheet with 60 contestants. Each contestant takes up 7 rows,
> > > > > the first six of which are hidden to start with and I have put macros in the
> > > > > adjoining column so that when they are clicked, the full 7 rows open up and
> > > > > the table of scores can be entered, When entry is complete for that
> > > > > contestant, a further macro when clicked will close up the 6 rows, leaving
> > > > > just the main line (line 14) with the No, Name, “OPEN” macro and other Totals
> > > > > in adjoining columns.
> > > > >
> > > > > The sheet works fine, but as many people will use this programme, I need to
> > > > > protect the sheets against mistaken entries etc., and as soon as I protect
> > > > > it, the macros wont work and throw up a “unable to set the property of a
> > > > > hidden range class, run time error 1004. I don’t want to leave the sheet
> > > > > unprotected, can anyone advise me where I am going wrong.
> > > > >
> > > > > I am also trying to find a way to validate “time taken” entries so that they
> > > > > can only be input as minutes and seconds in the format of 09.56, within a
> > > > > range of 00.01 – 10.00. Not having any success with this as it keeps
> > > > > converting the data into something like a date.
> > > > >
> > > > > I would be very grateful for any help you can give me.
> > > > >
> > > > > Thanks in anticipation.
> > > > >
> > > > > Sybs
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson