|
macros and protection
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 dont 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 |
macros and protection
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 dont 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 |
macros and protection
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 |
macros and protection
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 |
macros and protection
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 |
macros and protection
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 |
macros and protection
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 |
macros and protection
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 |
macros and protection
Brilliant, works a dream thanks very much, I will look at the other
mins:secs as well. If you can be bothered, when I have ranked by score, if there are ties can I then subsequently rank by time (different column) ? Sybs "Dave Peterson" wrote: 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 |
macros and protection
Chip Pearson has some ranking tips:
http://cpearson.com/excel/rank.htm If that doesn't do what you want, you may want to post in a new thread. Sometimes new subjects in old threads are ignored by others. SYBS wrote: Brilliant, works a dream thanks very much, I will look at the other mins:secs as well. If you can be bothered, when I have ranked by score, if there are ties can I then subsequently rank by time (different column) ? Sybs "Dave Peterson" wrote: 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 -- Dave Peterson |
| All times are GMT +1. The time now is 11:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com