Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 07-19-2017, 08:41 PM
JohnGanymede JohnGanymede is offline Problems with protected worksheet and macros Windows 7 64bit Problems with protected worksheet and macros Office 2010 64bit
Novice
Problems with protected worksheet and macros
 
Join Date: Jul 2017
Posts: 9
JohnGanymede is on a distinguished road
Default Problems with protected worksheet and macros

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



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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:29 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft