View Single Post
 
Old 07-20-2017, 07:59 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

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
Reply With Quote