disable sheet while processing

I have a sort sub() that takes some time to complete.  Running it
requires that I remove protection for the sheet, unhide rows and blah
blah.

Is there anyway to keep a user's hands off the workbook while this
runs?  My impression is that the mouse is on one leg while all this
happens, but I'm not sure just what it can get away with.  Que up
clicks and make a mess?  This sheet is so busy I'd keep it all locked
up if I could.

Thank you.
0
cate
11/23/2009 11:06:17 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
567 Views

Similar Articles

[PageSpeed] 44

You can use
application.screenupdating=false
code
application.sceenupdating=true
OR, it may NOT be necessary to unprotect>unhide rows>select,etc
show your code.

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"cate" <catebekensail@yahoo.com> wrote in message 
news:d9b24803-6916-4c69-b7bb-4ddeb1bc2ba3@l35g2000vba.googlegroups.com...
>I have a sort sub() that takes some time to complete.  Running it
> requires that I remove protection for the sheet, unhide rows and blah
> blah.
>
> Is there anyway to keep a user's hands off the workbook while this
> runs?  My impression is that the mouse is on one leg while all this
> happens, but I'm not sure just what it can get away with.  Que up
> clicks and make a mess?  This sheet is so busy I'd keep it all locked
> up if I could.
>
> Thank you. 

0
Don
11/23/2009 11:31:55 PM
On Nov 23, 5:31=A0pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> You can use
> application.screenupdating=3Dfalse
> code
> application.sceenupdating=3Dtrue
> OR, it may NOT be necessary to unprotect>unhide rows>select,etc
> show your code.

    Set mySheet =3D Application.ThisWorkbook.Worksheets("ChiralV")
    mySheet.Unprotect
    mySheet.Range("F:J").EntireColumn.Hidden =3D False

    With mySheet
        Set rg =3D .Range(.Range("n_rfpx_datarowfirst"), .Range
("n_rfpx_datarowlast"))
    End With

    If country_direction =3D 1 Then
        With rg
             .Sort Key1:=3D.Columns(8), Order1:=3DxlAscending, _
                 Header:=3DxlGuess, OrderCustom:=3D1, _
                 MatchCase:=3DFalse, Orientation:=3DxlTopToBottom, _
                 DataOption1:=3DxlSortNormal
        End With
    Else
        With rg
             .Sort Key1:=3D.Columns(9), Order1:=3DxlDescending, _
                 Header:=3DxlGuess, OrderCustom:=3D1, _
                 MatchCase:=3DFalse, Orientation:=3DxlTopToBottom, _
                 DataOption1:=3DxlSortNormal
        End With
    End If

    mySheet.Range("F:J").EntireColumn.Hidden =3D True
    mySheet.Protect DrawingObjects:=3DTrue, Contents:=3DTrue,
Scenarios:=3DTrue _
        , AllowFormattingCells:=3DTrue
0
cate
11/24/2009 12:04:58 AM
About the only thing a user could do while the code is running that would 
cause a problem would be to press Alt + Ctrl + Delete, or Ctrl + Break to 
stop the code.  I don't believe that clicking the mouse or pressing keyboard 
keys, other than those mentioned would interfere once the code is running. 
Although the sheet is unprotected, the macro has control until it completes. 
Since you protect the sheet again before exiting the macro, you should not 
have a problem.  But if you have idiots in the area who are malicious in 
nature, no amount of protection will keep them from sabotaging your work.



"cate" <catebekensail@yahoo.com> wrote in message 
news:8c275a4a-794a-48d4-9fac-634650771150@r31g2000vbi.googlegroups.com...
On Nov 23, 5:31 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> You can use
> application.screenupdating=false
> code
> application.sceenupdating=true
> OR, it may NOT be necessary to unprotect>unhide rows>select,etc
> show your code.

    Set mySheet = Application.ThisWorkbook.Worksheets("ChiralV")
    mySheet.Unprotect
    mySheet.Range("F:J").EntireColumn.Hidden = False

    With mySheet
        Set rg = .Range(.Range("n_rfpx_datarowfirst"), .Range
("n_rfpx_datarowlast"))
    End With

    If country_direction = 1 Then
        With rg
             .Sort Key1:=.Columns(8), Order1:=xlAscending, _
                 Header:=xlGuess, OrderCustom:=1, _
                 MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal
        End With
    Else
        With rg
             .Sort Key1:=.Columns(9), Order1:=xlDescending, _
                 Header:=xlGuess, OrderCustom:=1, _
                 MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal
        End With
    End If

    mySheet.Range("F:J").EntireColumn.Hidden = True
    mySheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
        , AllowFormattingCells:=True 


0
JLGWhiz
11/24/2009 5:43:45 PM
Reply:

Similar Artilces:

Index Function acceptig data from another sheet
I have posted a file at: https://filesanywhere.com I can enter cell referances in the arguments for the index function and it will return the value in the intersecting cell, but when I enter the values for the arguments by pointing to cells on another sheet I receive errors, even though the value in the cells I pointed to is identical to the values entered manually. I think that the posted file explains the problem better. I would appreciate any help. Thanks. Not able to open the link "Gulfman100" wrote: > I have posted a file at: > https://filesanywhere.com > &g...

CFormView plus Splitter = Disabled Controls
Does anyone know what is needed to get the buttons and controls on the form view to be enabled again. I created a test app (In Visual Studio.Net 2003, MFC/SDI App) then verified that the buttons on the formview were enabled. So far, so good. Then I added the splitter bars and that's when the form controls became disabled permanently. Adding the ON_COMMAND_UI handlers for the buttons did not fix the problem. 1) After adding the CMDUI support manually and after verifying that all the CMDUI handlers were called, the buttons still were disabled. EnableWindow() does not unlock the buttons eit...

summary sheet?
I am using Excel 2K and Windows 2K. I have a set of worksheets in a workbook that are identically formatted. Is it possible to create a summary sheet that will automatically place the worksheet name in one column and information next to it from say cells A1,C1,F30 from all worksheets. I'm not a great VB whizz. Any help greatly appreciated. TIA Ket This will add a new sheet and create the list. Press ALT+F11, go to Insert > Module, and paste in the code. Then run the macro: Sub Summary() Dim ws As Worksheet Dim i As Integer i = 2 Sheets(1).Activate Sheets.Add With Sheets(1) ...

How to create a calendar from date information in XL sheet.
I have an XL spreadsheet that contains multiple columns, one of which contains data in date & time format. Is there a way to export this data and associated information from the record to another application, for example Word, and create a calendar? Any suggestions how to manipulate this XL data are welcomed. Thanks! ...

How to wait for a process to finish?
Hi I am new to Windows. I created a process with CreateProcess http://msdn.microsoft.com/library/en-us/dllproc/base/createprocess.asp How do I wait for the process to finish? I do not want to busy-loop. I am looking for the Windows equivalant for "wait" system call in Linux. thanks, Sudhakar. sudhakarg79 wrote: >Hi > > >I am new to Windows. I created a process with CreateProcess > >http://msdn.microsoft.com/library/en-us/dllproc/base/createprocess.asp > >How do I wait for the process to finish? I do not want to busy-loop. I am >looking for th...

3M SandBlaster 423-120 Flexible Sanding Pad, 3.7 x 9.25-Inch, 120-Grit, Rectangle, 2-Sheet
Price:$7.30 Image: http://discountadvisors.info/image.php?id=B000GLHW3Q Best deal: http://discountadvisors.info/index.php?id=B000GLHW3Q Sandblaster, 2 Pack, 120G 3.7" x 9" Large Flexible Abrasives Detailed Sandpaper, Lasts 10 Times Longer Than Normal Sandpaper, Extreme Durability, Ultimate Comfort, Sandpaper Can Also Be Used On Detailed Flexible Abrasives Tool True Value #630-485. SIMILAR PRODUCTS: 3M SandBlaster 423-180 Flexible Sanding Pad, 3.7 x 9.25-Inch, 180-Grit, Rectangle, 2-Sheet:http://discountadvisors.info/index.php?id=B000GLE1OO 3M SandBlaster 413-220 Flexib...

Problem with Recovery Mail Store Process
Running Exchange and windows server 2003; Trying to recover a lost email from backup. have created the Recovery mail store, and database. Run the backup restore from a windows server that does not have exchange on it. Backup appears to run ok, no errors in report, Get to running the hard recovery from a command prompt and get the message, " Eseutil /cc not recognized" I'm at a loss what to do. Have the various documents on using the recovery mail store process and admin of Exchange server but am obviously missing something very basic. Help is greatly appreciated. Thank...

How do I remove the outline protection in Excel sheet?
When I use the sheet protection feature of Excel, I can not unprotect the outlining feature of the spreadsheet. I would like to protect some cells in the spreadsheet, but not disable the outlining expanding and collapsing feature. I am not asking to edit the outline. I just want to use the outlining features as a means to navigate large sheets. Currently, I have to unprotect the sheet in order to expand or collapse the outline. Is there a way to view a protected sheet with the outlining features enables? If you already have the outline applied, you can protect the worksheet in code...

Creating New Workbook from Sheet
Hi, any help with the following would be really appreciated, I have some VB Code, which works well, that for each change in a value in column A creates a new sheet. However what I now need to do is to either; a) create a new workbook for each of the newly created workshets, or b) instead of creating a new sheet to directly create a workbook, the ultimate end goal is to automatically email these workbooks or sheets. my code for creating a new worksheet is Sub create_new_sheets() Dim CalcMode As Long Dim ws1 As Worksheet Dim WSNew As Worksheet Dim rng As R...

How to create groups of several rows in sheet (excel)?
How to create groups of several rows in sheet (excel)? I am trying to create groups form several rows such as: +1 -2 -3 +4 -5 -6 That I can expand or collapse. Select the rows to group, then click Data > Group & Outline > Group -- Max Singapore http://savefile.com/projects/236895 Downloads:17,800 Files:359 Subscribers:55 xdemechanik --- "MIB" wrote: > How to create groups of several rows in sheet (excel)? > I am trying to create groups form several rows such as: > > +1 > -2 > -3 > +4 > -5 > -6 > > That I can expand or...

disable excel except to "view"
Hello folks! I have a protected excel file that I make available to several computers from our server, just to read. But I need even more protection: I want patrons to have access to read this file but NOT to be able to then click the "new" icon to go on and create any new excel files. Can I disable this? i.e. like an "excel viewer" only, not the ability to create further excel sheets, but on a computer that does have the excel program. (the computer also has Fortres, but if I include excel in it's list of prohibited programs, they can't view the file anymore). tha...

Changing from sheet to sheet
Hi there! Is there a function that would enable me to navigate from sheet to sheet in an Excel document (instead of using the mouse and click on the sheet I want to work with)? Many thanks! Hi Ctrl PgUp and Ctrl PgDwn is one way. Insert > Hyperlink (and after that clicking it) is another. HTH. Best wishes Harald "Greywolf" <anonymous@discussions.microsoft.com> skrev i melding news:5bfc01c42d6b$c2a26630$a101280a@phx.gbl... > Hi there! > > Is there a function that would enable me to navigate from > sheet to sheet in an Excel document (instead of using the &...

Process does not contain any programs (???)
A fascinating new bug in VS2003. Anyone seen this little gem? I just converted a project from VS6 to VS.NET 2003. Added a bunch of new features. Built it. New feature didn't work. Wanted to set a breakpoint. Breakpoint did not occur. Weird. OK, finally add ASSERT(FALSE) to get into the debugger, can single-step. Found problem (sillly typo). Add another new feature. Make similar silly error. This one puts it into an infinite loop. I see the problem instantly. Do Debug>Break All to enter debugger, get strange message: "Unable to break execution. The process does not c...

saving excel with multiple sheets, but won't save to proper sheet
I have a shared worksheet in Excel that several people save to throughout the day. It has several sheets in it and just in the past couple of days, when people save, they either lose their changes, or the information is saved to a blank sheet that has not been used yet. Has anyone else had this problem and if so, what is the solution. ...

How can I find automatic links in an Excel-sheet?
I have an Excel-sheet with "automatic links" to other workbooks. At least according to Excel, so every time when I open the document I have to click 'No' to the questions whether I want to update these. The problem is I can not find this/these automatic link/s. Is there a way to find all automatic links so that I can delete them? Tobias wrote: > I have an Excel-sheet with "automatic links" to other workbooks. At > least according to Excel, so every time when I open the document I > have to click 'No' to the questions whether I want to update th...

copying charts into new sheets, data is pulled from old sheet
i make a sheet with a load of data and some graphs, then i want to copy the whole thing into a new sheet to process some similar but different data, all the cell references translate cleanly, and refer to the new sheet however, the graphs reflec tthe previous sheets ranges and data. how can i copy the graphs so they refer to cells on the new sheet? btw i know excel pretty well, and i could go through and drag new data on, but i would like about 30 graphs per sheet, so its tedious. thanks instead of copying just the data, use the Move or Copy Sheet option under Edit and copy the whol...

Does system.xml have any way to transofrm data with an xswl style sheet using strings like MSXML2 does?
Does system.xml have any way to transofrm data with an xswl style sheet using strings like MSXML2 does? how to convert this to use System.XML so i do not depend on MSXML2 interop? static public string XslTransform(string style, string strdata) { MSXML2.IXMLDOMDocument pDoc = new MSXML2.DOMDocumentClass(); pDoc.async = false; pDoc.loadXML(strdata); MSXML2.IXMLDOMDocument pTemplate = new MSXML2.DOMDocumentClass(); pTemplate.async = false; pTemplate.loadXML(style); strdata = pDoc.transformNode(pTemplate); return strdata; } ...

Transferring data between worksheets using Sheet Command?
Hi for an assignment i have to enter grades for 200 students in 4 subjects. there is a front summary sheet that contains all the subjects and all the students and their overall grade GPA etc.. anyway this summary sheet has to be populated automatically from the individual math, english etc.. worksheets. The guide says to do it using the 'sheet command' any help greatly appreciated. Thanks ...

Disable hotkeys
Hello haw I disable the hotkeys in Excel 2002 or customize it. Thanks a lot. You could use a small macro. For example, this disables the F1 (help) key: Option Explicit Sub disableF1() Application.OnKey "{f1}", "" End Sub And this would toggle it back to normal. Sub enableF1() Application.OnKey "{f1}" End Sub If you look at sendkeys in VBA's help, you'll see how to override special keys. (Like ctrl-c for copy.) Sheriff wrote: > > Hello haw I disable the hotkeys in Excel 2002 or customize it. > > Thanks a lot. -- Dave Pet...

moving data from one sheet to another
I have records in sheet1 as follows: A B C D E 1 NAME SEX SUBJECT COLLEGE WHETHER SELECTED 2 A MALE MATHS XAVIER YES 3 B FEMALE ENGLISH SEBASTIAN 4 C FEMALE MATHS PAULS 5 D MALE PHYSICS XAVIER YES 6 E MALE CHEMISTRY PAULS YES 7 F MALE PHYSICS SEBASTIAN YES 8 G FEMALE MATHS XAVIER YES 9 H MALE PHYSICS PAULS 10 I MALE MATHS SEBASTIAN Now, I want to scan entire data upto last cell of the range and move(cut and paste) those of the selected candidates, Sheet2 of the same book.After moving, the name o...

Distributions
I have an issue with purchase order receipt distributions not calculating correctly. Here's a summary of the situation: GP Dynamics 9.0 Manufacturing is installed and we purchase and make the same items. We use standard costing with FIFO periodic valuation. The item with the problem is an item we make and we also purchase from Germany in Euros. The PO is setup to purchase the inventory in Euros for a specified amount. This amount is lower than our standard cost (standard cost is our cost to make the inventory via a BOM and Routings). When we receive the inventory, inventory is debi...

Enable and Disable an USB port dynamically
Hello Group I'd like to enable and disable an USB port during operation of an application to prevent certain users to use the USB port for corrupting the system (USB sticks, viruses,...). Of course I don't want to reboot the system after a change. Is this possible? Regards Richie "Eunet Uhser" <eunet_uhser@hotmail.com> wrote in message news:eboLu2sUIHA.4696@TK2MSFTNGP05.phx.gbl... > Hello Group > > I'd like to enable and disable an USB port during operation of an > application to prevent certain users to use the USB port for corrupting > t...

Coloured sheet tabs
How do I colour my sheet tabs in Excel 2000 Hi not possible. This feature was introduced with Excel 2002 -- Regards Frank Kabel Frankfurt, Germany "Shortmatbowler" <Shortmatbowler@discussions.microsoft.com> schrieb im Newsbeitrag news:781380A6-B5F1-463F-86E9-65960024B7F9@microsoft.com... > How do I colour my sheet tabs in Excel 2000 Hi Sorry you can't. Not available until later version, XP I believe "Shortmatbowler" wrote: > How do I colour my sheet tabs in Excel 2000 Sorry - Excel 2002 not XP "mzehr" wrote: > Hi > Sorry you can&#...

Help
Hi I have produced an Excel workbook for gathering exam results and giving a score to them, counting the number of passes over a certain level etc. Each sheet is one student and I have used a little macro to name the sheet according to the name entered at the top of the sheet. My summary sheet requires certain formulae to analyse results from each worksheet. The problem is, I can easily enter the row of formulae for one student - but there could be up to 50 in a workbook! The fill action does not increment through the sheet names either when they are re-named or when they have original na...

Searching Multiple Sheets
Is there a way to search multiple Excel Sheets (same workbook), other than clicking on each sheet and using the Find feature? Thank you, in advance. With XL2000 and higher, you can select multiple sheets by holding down the Ctrl key and then do Edit > Find. Or select all sheets by clicking the first sheet, hold down the SHIFT key, and click the last sheet. HTH Jason Atlanta, GA >-----Original Message----- >Is there a way to search multiple Excel Sheets (same workbook), other than clicking on each sheet and using the Find feature? Thank you, in advance. >. > Thank you ...