Disable user interaction in VBA

Hi All,

How can you disable user interaction with a workbook while awaiting 
completion of running of a few macros?  These macros depend on specific 
selection of worksheets and if users change the worksheet tabs, they will 
break the macros.   How can I disable their user interaction with workbook 
while macros are running?

I tried the followings which failed:
- changing the cursor shape to xlwait
-   Application.ScreenUpdating = False
    Application.EnableEvents = False

thanks for your help.
0
Utf
1/21/2010 3:21:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
2854 Views

Similar Articles

[PageSpeed] 1

Write your macro code to be independent of selection:

Instead of

Worksheets("Sheet1").Select
Range("A12").Select
MyVal = Selection.Value

use

MyVal = Worksheets("Sheet1").Range("A12").Value

Post your code if you need help doing so.

HTH,
Bernie
MS Excel MVP


"LABKHAND" <LABKHAND@discussions.microsoft.com> wrote in message 
news:4C85F2C7-2456-46EE-8717-D2D1E7B9FD52@microsoft.com...
> Hi All,
>
> How can you disable user interaction with a workbook while awaiting
> completion of running of a few macros?  These macros depend on specific
> selection of worksheets and if users change the worksheet tabs, they will
> break the macros.   How can I disable their user interaction with workbook
> while macros are running?
>
> I tried the followings which failed:
> - changing the cursor shape to xlwait
> -   Application.ScreenUpdating = False
>    Application.EnableEvents = False
>
> thanks for your help. 


0
Bernie
1/21/2010 3:34:36 PM
Reply:

Similar Artilces:

Alternative for user instance login flag ?
I have SQL Server 2008 Developer Edition only installed on development PC, have 2008 Standard Edition on the production server. The DBAs have the production environemnt locked-down. I created a VS.NET 2008 web application and setup a MDF to use my Dev Edition DB instance. When I run the app, I try to connect and get " The user instance login flag is not supported on this version of SQL Server. The connection will be closed. " I understand that only SqlExpress supports this functionality. The professional DBAs are not going to let me just install another instance o...

How to disable OUTLOOK?
I use Outlook Express to handle my email. Recently I have installed Outlook 2003. I use MailWasher as a check programme prior to downloading to OE, and since installing Outlook, it appoints itself as the chosen programme. How can I convince it otherwise? Thanks AJ Try going to control panel, internet options, programs, and choose OE as your default mail program. AJ wrote: > I use Outlook Express to handle my email. Recently I have installed > Outlook 2003. > I use MailWasher as a check programme prior to downloading to OE, and > since installing Outlook, it appoints itself as...

Printing Issues when distributing a form to multiple users
I am distributing a spreadsheet to multiple users. The spreadsheet is being used as a form for data entry. The problem i am having is that when it is being printed, the users are getting varying page layouts where the page breaks have changed, due the changes to the length of the document growing from the free text they are entering. The page breaks that excel chooses don't seem to be spaced well, leading to one or two of the pages being very short (i.e., leaving a lot of free white space), whereas the other pages are taking up the entire page. Is there a particular page layout selection...

Transfer Files Using VBA
Hi, I'd like to transfer files from several drives on my pc to one cetnral location. From A:\Office1\ B:\Office2\ C:\Office3\ To D:\HeadQuarters\ My routine now is the following 1) Copy the files from drives: A, B, and C; and then 2) Paste the filesto the D drive 3) Delete the files permanently from drives A, B, and C. As you see this is a tedious routine, which I'd liket o avoid by using VBA. Can someone start me off with some code on how to accomplish this. Thanks so much in advance! Jrew Jrew, You could create a batch file (from the olden days) using XCOPY and desired bel...

VBA problems
Hello, I just started messing around with VBA and I got something approaching what I want (what I want is to highlight a row based on a value in the 1st column). This is highlighting the wrong row though! It looks like all the numbers are correct... for instance, when I step through it, I'll get the correct row selected but a different row highlighted! Please shed some light on this! It's quite frustrating. ' first column has dates from 12/2003 to 12/2006 Private Sub Worksheet_Activate() Const Yellow = 6 Dim row As String Range("A1").Select Do While...

Limited User Account
I am thinking of buying Microsoft Money Plus 2008 to use on a computer with Windows XP. I read in the user guide that I must install it with an administrator account, which I would expect. After it is installed, can I import data from Quicken 2006 and use MS Money Plus from a limited user account? The reason I ask is I do all my routine work with a limited user account and only use the administrator account when absolutely necessary. Gerry In microsoft.public.money, Geber wrote: >I am thinking of buying Microsoft Money Plus 2008 to use on a computer >with Windows XP. I read in the...

How to disable F5 of CHtmlView ?
Hello there I want to disable F5 of CHtmlView (web browser). How could I do this ? I found that I could do it by overriding CHtmlView::OnTranslateAccelerator and returning S_OK. I did so , but it didn;t work. Now what can I do so ? Can you tell me what's wrong in my code ? Otherwise suggest me some different method to do so. This is my code ... HRESULT CTabHtmlView::OnTranslateAccelerator(LPMSG lpMsg, const GUID* pguidCmdGroup, DWORD nCmdID) { if(lpMsg->message == WM_KEYDOWN && GetAsyncKeyState(VK_F5) == 0 ) { // Disable F5 if feed is displayed to avoid side affects return...

How to force the user to fill in values for yes/no fields ?
Hi ! I'm using MS Access 2003. I have a table with a number of yes/no fields. When a new record is started, I want the user to explicitly specify a value for each of these fields, e.g. by choosing one of two radio buttons associated with the field. Now, it was no problem for me to arrange an option group with two suitable radio buttons for each of these fields. The user can then choose explicitly between yes and no. However, all fields seem to have implicit values immediately after a new record has been started. Therefore, the new record can be stored in the db without the user making...

Workbook with macros hangs up when opening, only with certain user
Chip Pearson has some notes about startup problems at: http://www.cpearson.com/excel/StartupErrors.htm Ron516 wrote: -- Dave Peterson ec35720@msn.com ...

How to control what tabs are displayed in Active Directory Users and Computers
I do not see the value to allow the delegate to change the City, State, Country, Office. What am I doing wrong. (see info below) Also is there a way to limit the view of what is shown in Active Directory users and computers, so the Delegate only sees the General, Address, Telephone and Organization tabs? I know the step below lock them out of the other tabs, but there would be less confusion..even if it is registry hack on that local pc. Here are the steps I have done so far: Step 1. Create a Security Group Called 'XYZ' (call it what ever you want Step 2. Open Active Directory User...

Disabling a CButton in a dialog
Hallo, I want to disable a button directly after clicking on it with this code: void CElternDialog::OnBnClickedEdit() { CButton* pbEdit = (CButton*)GetDlgItem( IDC_EDIT ); pbEdit->EnableWindow( FALSE ); ... GetDlgItem( IDC_NAME1 )->SetFocus(); } At runtime the button text looks 'grayed' and the button is disabled. The focus is in the desired control, but the clicked button has a bold black frame like it is if it were the BS_DEFPUSHBUTTON style. Before clicking the button hadn't the black frame. Is it necessary to 'repaint' the disabled button to prevent...

user can only do send as
I currently have a user running OfficeXP Standard SP1, Win2K SP3 and the server running Win2K Exchange 2000 SP3. The user has been given permission to send on behalf of, but not send as. When they send something trying to use the send on behalf of, the messages sends as if it were sent as send as (i.e. it appears to come directly from the user and doesn't have the additional message attachement sent of behalf of). Any idea of how to get the send on behalf of working properly? I have manually removed them and it still happens, as if it is stuck on somewhere. I allowed lots of time for re...

I want to send full contacts list to another user
I have Microsoft Outlook 2000, and My Contacts are set up in a Hierarchy. I want to send the whole group to another user. Is there any way of sending the contacts, and keeping the existing Hierarchy. Thanks, Gerry. ...

VBA
I having problems using the My Outlook Calendar.dot template from the Slipstick website (sorry I can't attach it, file type not supported). I downloaded the .dot file and with the help of someone in IT we modified the code to pull event details from outlook onto the resulting calendar report. I was able to use it successfully for a few weeks and then all of a sudden the .dot stopped working. It gives me a runtime error whenever I try to launch it. I'm not familiar with VBA at all but I used the debug tool to see where the code "breaks". It seems to happen at L...

Fetch user login information using JScript
Hi.. I am new to MS CRM. I am trying add custom HTML pages to the CRM which requires the user login information. I would like to know how to go about this and solve my problem Any help regarding this would be appreciated NN, I am not 100% sure of this but could you not just have the page use integrated authentication? Jeff Loucks 888-474-2237 MVP "NN" <NN@discussions.microsoft.com> wrote in message news:0E6BA8D5-2F2D-492B-8CB8-84284D04FF59@microsoft.com... > Hi.. > > I am new to MS CRM. I am trying add custom HTML pages to the CRM which > requires the user...

how to hide dropdown items according to users ?
Hi, I have created an dropdown control in contacts form. and i have 2 items in the dropdown list. now when user1 logs in i want to show only the 1st item in the dropdown control or i should able to disable the control. but when user2 logs in i should show all the items in the dropdown control. Now how to hide dropdown items according to users ? Regards, Richard, There is not supported way to do what you are asking to do. You might be able to do this by somt customizing of the XSLT's, but what happens when User 1 pulls up a record that User 2 previously set the value to the 2nd option?...

Separating parts of a string in VBA
Hello, I have a string in cells(1,1). The value of the cell is "DATE" 1/1/10 THRU 3/1/10" I want to separate this string into the two new strings such that one string would read "1/10/10" and the other would read "3/1/10". In C programming, this would be fairly easy. But in VBA I don't know any string functions which can read and compare individual characters. Does anyone know how to accomplish what I am trying to do? thanks IF the DATE and THRU parts of your example text (along with their adjacent blank spaces) appear in all the text s...

Disable the "New Lead" button on the home_leads.aspx
Hi All, How can I disable the following 1.- "New Lead" button on the grid menu bar. Prioror to the print button? 2.- Disable / alter the menuitems in the "New" menu bar 3.- Disable / alter the menuitems in the "Actions" menu on the grid menu bar One way is setting the rights for the users. Is there any other way to acheive this say by HttpModule handler??? and how to do this.. Thanks in advance.... Prasad ...

vba excel project
im not sure if this is possible in excel... but here's what my boss wants... one excel file (master file) contains 10 command buttons (sheet 1). when a user clicks on a button (button 1), an excel template file (template 1) is automatically opened... now this template file will be filled up by the user, and once she/he saves the template (template 1) with a new filename, the date and time (of saving) will be listed on the master file (sheet 2) together with the specified filename of the user. now if the user clicks on button 2, template 2 is opened. and then once saved, the date,time a...

Disable viewing of macros
Hi, 1. In general, how can i disable users from accessing macros for a particular workbook. I guess i should be putting some code in the file open event and close event. What is the command to disable the option. 2. I have a workbook with many worksheets. Each worksheet is protected but for some cells that are allowed to be edited by the user. Therefore, if a user wants to edit all the cells then he/she should know the unprotect worksheet password. How can i disable a user from accessing any macro of the workbook unless the worksheet is unprotected. Any help is appreciated. Thanks....

Secondary SA user & password
I looked over previous sa user items in this group. Is it posible in 10.0 SP1 to create an additional sa user that doesn't have rights to security roles/user accounts? What I am trying to do is have 2 sa roles- one that can do all and one sa that can not modify user security or roles. If it is possible how would I accomplish that? Thanks for the response! Hi Ang Have a look at this white Paper, I think ti explains everything you are looking for: https://mbs.microsoft.com/customersource/documentation/whitepapers/msdygp10_securityplanwp.htm?printpage=false Cheers -- Fliehigh "An...

Money Plus not activated for just one Vista user
I posted a message over two hours ago and it never appeared. Sorry if this turns out to be a duplicate. I recently installed and activated Money Plus Deluxe after using the demo. The problem is that when I run it under my normal, restricted Vista account the program reports that it needs to be activated. I reactivate the program under that account and am told that it was successful, but the next time I start the program it reports that it needs to be activated again. The problem does not occur for other accounts on the computer. It only happens on the one Vista account that I actu...

Users Crm
Hi, I have a crm server, only one person seems to be able to view the web page, all the other get a box saying you are not authorised. Users have been created for all of these people. Also interestinly there are com+ errors for the executive and svc groups and dcom errors in the event logs. Are these anything to do with it? Thanks have all these user been assigned CRM Licenses and security roles? -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Matt Owen" <mowen@omniis.com> wrote in message news:bc4d4b18.0...

User changes last name
I have a network user with an associated mailbox. The user's name is jsmith and the Exchange alias is jsmith. This user has a new last name now, jjones.The user wants her email address to reflect the new name. She also wants to have any mail that goes to her old address forwarded to the new address. Can I just rename some things or do I have to create another userid and create the new mailbox and import all the email into the new mailbox and forward the old email to the new one. That senario would also cause the user to get a default profile on the desktop which I want to try to av...

Unable display inbox folder under the user's mailbox
We have a user of outlook'97 and we are using win Exchange server 5.5. But this user can not open his Inbox under the mailbox. But he can open the sent items, etc under the mailbox. What should We do ? Thanks and reagrds, Subyantoro@lfdis.com Sounds like it could be a corrupted view. Has the user/you tried starting Outlook with the /cleanviews command line switch? -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "Subyantoro" <Subyantoro@lfdis.com> wrote in message news:056f01c3d370$97715250$a401280a@phx....