Problems with protected worksheet and macros

JohnGanymede · #1 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

NoSparks · #2 07-20-2017, 07:59 AM

Quote:

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.

Check if the range is empty prior to unprotecting, something like this

Code:

If WorksheetFunction.CountA(.Range("C5:C14")) = 0 Then Exit Sub

Quote:

they are hit and miss, firing incorrectly and leaving the sheet sometimes protected, sometimes not, and sometimes not recognizing it's own password.

Where is/are your button(s) located that fire the macro(s) and where are the macros located ?

I would only unprotect and re-protect the sheet being worked on unless there's more to this than what I' seeing.
Along the lines of this

Code:

Sub CSVarmor()
'
' CSVarmor Macro
' paste and click csv to armor
'
Application.ScreenUpdating = False

With ActiveSheet
    If WorksheetFunction.CountA(.Range("C5:C14")) = 0 Then Exit Sub
    .Unprotect Password:="jtls"
    .Range("C5:C14").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
    .Protect Password:="jtls"
End With

Application.ScreenUpdating = True

End Sub

JohnGanymede · #3 07-20-2017, 12:05 PM

This looks exceptional. Code is much cleaner than the editor generated spaghetti and the copypaste code I spliced in there. Protecting and unprotecting just the worksheet seems more sensible.

The macros were done by "recording" and are saved to the worksheet. Buttons were created from the developer tab, controls menu, "insert" dropdown, and are placed in a relatively handy spot on the same worksheet as the whole operation is happening. Unprotecting just the worksheet will be fine. If there is ever a second sheet, it'll be different tools.

A quick question about:
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)),

This was recording generated and I'm not sure of its function. This "may" correspond to 4 cells where i input delimated junk data in the first column of the intended array, so the function would work as I recorded it. My concern is that this array should actually be (overall) 3 columns by 10 rows, and that my other similar arrays (and the associated macros) all should be various numbers of columns by 10 rows... but the array statements in the macros all seem to have no logical correlation UNLESS it was defined by which cells had junk data.

Should I edit the array statements up to (10, 1), (the column I intend to paste the csv data)? Should I define the multiple columns of the input array for csv pasting, as well? Will this prevent attempts to paste too many csv values?

Thanks for your help...

JohnGanymede · #4 07-20-2017, 12:18 PM

...also

Code:

If WorksheetFunction.CountA(.Range("C5:C14")) = 0 Then Exit Sub

This is way more elegant that what I tried, which involved a conditional goto statement about the debugger, after the unprotect, with the goto target dropped into the macro just before the "protect sheet" statement at the end. Something I copied off the internets... Yours will stop the debugger from launching at all, before the sheet is unprotected.

NoSparks · #5 07-20-2017, 01:03 PM

Quote:

A quick question about:
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)),

This was recording generated and I'm not sure of its function.

Me neither. It definitely has something to do with the number of delimitation's the macro recorder was looking at
but if I reduce the generated line to just this things still seem to work as I would expect so I wouldn't be too concerned.

Code:

.Range("C5:C14").TextToColumns Destination:=Range("C5"), DataType:=xlDelimited, Semicolon:=True

The macro recorder tends to include everything associated with what it's doing even when most of it may be default settings.

If you run into issues, attach a sample workbook to investigate.

JohnGanymede · #6 07-20-2017, 01:52 PM

So it's (row, column) rather than (column, row)? As in: (Array(row, column), ? My junk data had too many semicolons. Interesting. If that's the case I might fill it out for each macro to indicate each specific number of columns per array. It might be messier, but I'm trying to stop things from breaking in creative ways.

You seem to have solved all my problems. Thank you. I "do" have one other question!

As part of this function:

Code:

.Range("C5:C14").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

I have an extra semicolon set as a null value in the csv pasting, so a user can skip a cell if that stat doesn't apply.

IE: 100;;100; = 100 (null cell) 100

I'd rather idiot proof it by having the function see a particular character as a null value and skip the cell.

IE: 100;z;100; = 100 (null cell) 100

This would allow me to turn back on the preference to see extra semicolons as just "fatfingering" and ensure that the user truly intended to skip over a value. Is a parameter like this possible?

NoSparks · #7 07-20-2017, 03:11 PM

The text to column dialogue has a check box next to the delimiter choices that is Treat consecutive delimiters as one
When checked it adds ConsecutiveDelimiter:=True into the line of code.

JohnGanymede · #8 07-20-2017, 04:55 PM

Thanks for all the help! I have to relearn everything for every occasional project. Cheers, sir.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sorting in protected worksheet	john64	Excel	2	12-31-2016 12:04 PM
Cell formating on Protected worksheet	AHB	Excel Programming	2	02-27-2012 10:25 AM
Insert/Paste Picture into Protected Worksheet	udea	Excel	1	02-06-2012 07:43 PM
Word mail merge from a protected Excel worksheet	Platform	Mail Merge	10	09-28-2011 05:12 AM