Problems with protected worksheet and macros

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

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