UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Remove Row & Column Headings from spreadsheet...    
 
   
adaytay
post Feb 10 2009, 12:36 PM
Post #1

UtterAccess VIP
Posts: 3,656
From: Yorkshire, England.
Hi all,

Long story short, I have a key combination that will lock down or unlock all sheets on demand for specified users.

As part of this code, when the sheets are unlocked, the row and column headings are being displayed.

Here's the code:

CODE
        If ActiveSheet.ProtectContents Then
            'Current Worksheet is protected, so unlock them all
            For Each ws In ActiveWorkbook.Worksheets
                ws.Unprotect strWSPassword
                'Show Headings etc
                ws.Activate
                With ActiveWindow
                    .DisplayHeadings = True
                End With
            Next
        Else
            For Each ws In ActiveWorkbook.Worksheets
                ws.Protect strWSPassword
                'Hide Headings etc
                ws.Activate
                With ActiveWindow
                    .DisplayHeadings = False
                End With
            Next ws
        End If


Now, despite me disabling the screen updating, when I hit the key combo do do the work, the system is flicking through each sheet visibly to unlock it. I guess it's because I'm activating the worksheet and then displaying headings. Is it possible to do this on the sheet level?

Thanks in advance,

Ad
Go to the top of the page
 
+
norie
post Feb 10 2009, 01:12 PM
Post #2

UtterAccess VIP
Posts: 4,297
Adam

Where are turning off screen updating?

I can't see that happening in the posted code.(IMG:http://www.utteraccess.com/forum/style_emoticons/default/dazed.gif)
Go to the top of the page
 
+
adaytay
post Feb 11 2009, 04:44 AM
Post #3

UtterAccess VIP
Posts: 3,656
From: Yorkshire, England.
I'm turning it off earlier in the procedure by doing application.screenupdating = false

Cheers,

Adam

Edited by: adaytay on Wed Feb 11 4:44:39 EST 2009.
Go to the top of the page
 
+
adaytay
post Feb 11 2009, 05:26 AM
Post #4

UtterAccess VIP
Posts: 3,656
From: Yorkshire, England.
Resolved.

Setting Application.EnableEvents=False immediately after locking the screen resolved the issue. (And then setting it to true again before unlocking it again, obviously).

Ad
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 26th May 2013 - 12:13 AM