Microsoft Office Forums - View Single Post - Problems with protected worksheet and macros

JohnGanymede · 07-19-2017, 08:41 PM

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