![]() |
#1
|
|||
|
|||
![]()
Hi.
I'm working with Excel 2010 to make a game tool for calculating stats. The idea is to have most of the worksheet protected with unprotected blocks of cells for inputting values, with separate protected blocks of cells for calculated returns and compiling. Everything was going very well until I decided that copypasting csv text was necessary. Importing is not an option. I recorded macros that did the job, but they don't function on a password protected sheet. I tried adding password logging to the macros, but they are hit and miss, firing incorrectly and leaving the sheet sometimes protected, sometimes not, and sometimes not recognizing it's own password. The second problem I'm having is that when the button that fires the macro is clicked, if there is no data in the selected cells, it dumps into debug and leaves the worksheet unprotected. I tried various way to correct these problems with the results above. This tool is intended to be distributed to fellow gamers, most of whom will have little idea how to treat it delicately. Currently, I'm saving it as a template, hoping that I could make it bulletproof, but that really didn't seem to accomplish much or stop me from saving. Help would be appreciated. Here's the macro I'm having trouble with. I have seven or so (named differently, of course) that do the exact same job of handling csv copypaste in different areas of the form. Code:
Sub CSVarmor() ' ' CSVarmor Macro ' paste and click csv to armor ' ' Application.ScreenUpdating = False Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets sh.Unprotect Password:=jtls Next sh Range("C5:C14").Select Selection.TextToColumns Destination:=Range("C5"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _ False Range("E15").Select For Each sh In ActiveWorkbook.Worksheets sh.Protect Password:=jtls Next sh Application.ScreenUpdating = True End Sub |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sorting in protected worksheet | john64 | Excel | 2 | 12-31-2016 12:04 PM |
![]() |
AHB | Excel Programming | 2 | 02-27-2012 10:25 AM |
![]() |
udea | Excel | 1 | 02-06-2012 07:43 PM |
![]() |
Platform | Mail Merge | 10 | 09-28-2011 05:12 AM |